Direct database queries (wpdb) in Wordpress - Step-by-Step Execution

Concept Flow - Direct database queries (wpdb)

Start Query

↓

Prepare SQL with placeholders

↓

Call $wpdb->prepare()

↓

Execute query with $wpdb->get_results() or similar

↓

Fetch results

↓

Use results in code

↓

End

This flow shows how to safely prepare and run a direct database query using $wpdb in WordPress.

Execution Sample

Wordpress

$sql = $wpdb->prepare("SELECT * FROM wp_users WHERE ID = %d", 5);
$results = $wpdb->get_results($sql);
foreach ($results as $user) {
  echo $user->user_login;
}

This code prepares a query to get user with ID 5, runs it, and prints the username.

Execution Table

Step	Action	SQL Query	Result	Notes
1	Call $wpdb->prepare() with SQL and ID=5	SELECT * FROM wp_users WHERE ID = 5	Prepared SQL string	Placeholder %d replaced by 5 safely
2	Call $wpdb->get_results() with prepared SQL	SELECT * FROM wp_users WHERE ID = 5	Array of user objects	Query executed, results fetched
3	Loop over results	-	Access $user->user_login	User login printed for each result
4	End loop	-	-	All results processed

💡 All matching users fetched and displayed; query execution complete.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
$sql	null	SELECT * FROM wp_users WHERE ID = 5	SELECT * FROM wp_users WHERE ID = 5	SELECT * FROM wp_users WHERE ID = 5	SELECT * FROM wp_users WHERE ID = 5
$results	null	null	[user object with ID=5]	[user object with ID=5]	[user object with ID=5]
$user	null	null	null	user object with ID=5	user object with ID=5

Key Moments - 2 Insights

Why do we use $wpdb->prepare() instead of putting the ID directly in the SQL?

Using $wpdb->prepare() safely inserts variables into SQL, preventing errors and security risks like SQL injection, as shown in step 1 of the execution_table.

What type of data does $wpdb->get_results() return?

$wpdb->get_results() returns an array of objects representing rows from the database, as seen in step 2 and variable_tracker for $results.

Visual Quiz - 3 Questions

Look at the execution_table step 1. What does $wpdb->prepare() do with the placeholder %d?

A Replaces %d with the number 5 safely

B Ignores %d and runs the query as is

C Deletes %d from the query

D Converts %d to a string 'd'

💡 Hint

Check the SQL Query column in step 1 of execution_table to see how %d is replaced.

At which step in the execution_table do we get the actual user data from the database?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Result column to see when the array of user objects is returned.

If we changed the ID from 5 to 10 in the prepare call, what changes in the execution_table?

A The Result in step 2 becomes empty

B The loop in step 3 is skipped

C The SQL Query in step 1 changes to ID = 10

D No changes happen

💡 Hint

Focus on how $wpdb->prepare() replaces placeholders in step 1.

Concept Snapshot

Direct database queries with $wpdb:
- Use $wpdb->prepare() to safely insert variables.
- Run queries with $wpdb->get_results() or similar.
- Results come as arrays of objects.
- Always prepare queries to avoid SQL injection.
- Loop through results to use data.

Full Transcript

This lesson shows how to run direct database queries in WordPress using the $wpdb object. First, you prepare your SQL query with placeholders using $wpdb->prepare(), which safely inserts your variables. Then you execute the query with $wpdb->get_results() to get the data as an array of objects. Finally, you loop through the results to use the data, like printing usernames. This method prevents security issues and ensures your queries run correctly.

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