Direct database queries (wpdb) in Wordpress - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is wpdb in WordPress?

<p><code>wpdb</code> is a WordPress class that helps you safely run database queries. It connects to the WordPress database and lets you get, insert, update, or delete data.</p>

beginner

How do you safely prepare a SQL query using wpdb?

You use the $wpdb->prepare() method. It replaces placeholders with safe values to avoid SQL injection.

Example: $wpdb->prepare('SELECT * FROM wp_posts WHERE ID = %d', $post_id);

beginner

Which wpdb method would you use to get a single row from the database?

You use $wpdb->get_row(). It returns one row as an object, array, or associative array.

intermediate

What is the difference between get_results() and get_var() in wpdb?

get_results() returns multiple rows as an array of objects.

get_var() returns a single value from the database, like one cell.

beginner

Why should you avoid directly inserting variables into SQL queries without prepare()?

Directly inserting variables can cause SQL injection, a security risk where attackers can run harmful commands. prepare() safely escapes variables to prevent this.

Which wpdb method is used to safely insert data into the database?

A <code>query()</code>

B <code>insert()</code>

C <code>prepare()</code>

D <code>get_row()</code>

insert() is used to add new rows safely. prepare() helps build queries but does not insert data by itself.

What placeholder should you use in prepare() for a string value?

A %s

B %d

C %f

D %a

%s is for strings, %d is for integers, and %f is for floats.

Which wpdb method returns a single value from the database?

A <code>get_results()</code>

B <code>get_row()</code>

C <code>insert()</code>

D <code>get_var()</code>

get_var() returns one value, like a single cell from a table.

What does $wpdb->query() return after running an UPDATE statement?

A Single row object

B Array of results

C Number of rows affected

D Boolean true or false

query() returns the number of rows changed by the statement.

Why is it important to use wpdb->prepare() before running a query?

A To prevent SQL injection

B To format the output

C To speed up the query

D To connect to the database

prepare() escapes variables to keep the database safe from malicious input.

Explain how to safely run a SELECT query using wpdb and retrieve multiple rows.

Think about how to avoid SQL injection and get many rows.

Describe the difference between get_row(), get_results(), and get_var() in wpdb.

Focus on how many rows or values each method returns.

Practice

1. What is the main purpose of the $wpdb object in WordPress?

easy

A. To style WordPress themes with CSS

B. To create new WordPress posts automatically

C. To manage WordPress user sessions

D. To run direct SQL queries on the WordPress database safely

Solution

1. Step 1: Understand the role of $wpdb

   $wpdb is a WordPress class that allows running SQL queries directly on the database.

2. Step 2: Identify the correct purpose

   It is used to safely execute SQL queries, not for posts, sessions, or styling.

3. Final Answer:

   To run direct SQL queries on the WordPress database safely -> Option D

4. Quick Check:

   $wpdb = direct database queries [OK]

Hint: Remember: $wpdb is for database queries only [OK]

Common Mistakes:

• Confusing $wpdb with theme or user management
• Thinking $wpdb handles styling or sessions

2. Which of the following is the correct way to safely prepare a SQL query using $wpdb?

easy

A. $wpdb->prepare("SELECT * FROM wp_posts WHERE ID = %d", $id);

B. $wpdb->query("SELECT * FROM wp_posts WHERE ID = $id");

C. $wpdb->get_results("SELECT * FROM wp_posts WHERE ID = $id");

D. $wpdb->execute("SELECT * FROM wp_posts WHERE ID = %d", $id);

Solution

1. Step 1: Identify the safe query method

   $wpdb->prepare() safely inserts variables into SQL queries using placeholders like %d.

2. Step 2: Check each option

   $wpdb->prepare("SELECT * FROM wp_posts WHERE ID = %d", $id); uses prepare correctly with %d and variable $id. Others either lack preparation or use wrong methods.

3. Final Answer:

   $wpdb->prepare("SELECT * FROM wp_posts WHERE ID = %d", $id); -> Option A

4. Quick Check:

   Safe query uses prepare with placeholders [OK]

Hint: Use prepare with %d, %s for safe queries [OK]

Common Mistakes:

• Inserting variables directly without prepare
• Using wrong method names like execute
• Not using placeholders for variables

3. What will the following code return if the wp_users table has 3 users?

$count = $wpdb->get_var("SELECT COUNT(*) FROM wp_users");

medium

A. An array of user data

B. The number 3

C. A boolean true

D. An error message

Solution

1. Step 1: Understand get_var usage

   get_var returns a single value from the database, here the count of users.

2. Step 2: Interpret the SQL query

   The query counts rows in wp_users, which is 3 users.

3. Final Answer:

   The number 3 -> Option B

4. Quick Check:

   get_var returns single value count [OK]

Hint: get_var returns one value, not arrays [OK]

Common Mistakes:

• Expecting an array instead of a single value
• Confusing get_var with get_results
• Thinking it returns true/false

4. Identify the error in this code snippet:

$results = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_status = 'publish'");
foreach($results as $post) {
  echo $post->post_title;
}

medium

A. Loop variable should be $results not $post

B. Incorrect method; should use query instead of get_results

C. No error; code works correctly

D. Missing $wpdb->prepare() for safe query

Solution

1. Step 1: Check query safety

   The query uses a fixed string 'publish' so no variable injection risk, so prepare is not required here.

2. Step 2: Analyze method and loop

   get_results returns an array of objects; looping with $post is correct. No syntax error here.

3. Final Answer:

   No error; code works correctly -> Option C

4. Quick Check:

   Fixed string query is safe without prepare [OK]

Hint: Use prepare when variables are in query [OK]

Common Mistakes:

• Ignoring query preparation for dynamic inputs
• Confusing get_results with query
• Misusing loop variables

5. You want to update the post title of a post with ID stored in $post_id safely using $wpdb. Which code snippet correctly does this?

hard

A. $prepared = $wpdb->prepare("UPDATE wp_posts SET post_title = %s WHERE ID = %d", $new_title, $post_id); $wpdb->query($prepared);

B. $wpdb->prepare("UPDATE wp_posts SET post_title = %s WHERE ID = %d", $new_title, $post_id); $wpdb->query($prepared);

C. $wpdb->query("UPDATE wp_posts SET post_title = '$new_title' WHERE ID = $post_id");

D. $wpdb->update('wp_posts', ['post_title' => $new_title], ['ID' => $post_id]);

Solution

1. Step 1: Understand safe update with $wpdb

   Use prepare to safely insert variables, then run query with prepared SQL.

2. Step 2: Check each option

   $prepared = $wpdb->prepare("UPDATE wp_posts SET post_title = %s WHERE ID = %d", $new_title, $post_id); $wpdb->query($prepared); correctly prepares the query and then runs it. $wpdb->prepare("UPDATE wp_posts SET post_title = %s WHERE ID = %d", $new_title, $post_id); $wpdb->query($prepared); calls prepare but does not assign or run the result properly. $wpdb->query("UPDATE wp_posts SET post_title = '$new_title' WHERE ID = $post_id"); is unsafe. $wpdb->update('wp_posts', ['post_title' => $new_title], ['ID' => $post_id]); uses update method but misses table prefix and is not asked here.

3. Final Answer:

   $prepared = $wpdb->prepare("UPDATE wp_posts SET post_title = %s WHERE ID = %d", $new_title, $post_id); $wpdb->query($prepared); -> Option A

4. Quick Check:

   Prepare query, assign, then query [OK]

Hint: Assign prepare result before running query [OK]

Common Mistakes:

• Running prepare without assigning result
• Inserting variables directly into SQL
• Confusing update method usage