Direct database queries (wpdb) in Wordpress - Mini Project: Build & Apply

Direct Database Queries with wpdb in WordPress

📖 Scenario: You are building a WordPress plugin that needs to fetch and display user information directly from the WordPress database using the wpdb class.This is useful when you want to run custom queries beyond the standard WordPress functions.

🎯 Goal: Learn how to safely run direct database queries using wpdb to get user data and display it.

📋 What You'll Learn

Use the global $wpdb object to access the database

Write a query to select user data from the $wpdb->users table

Use a variable to set a minimum user ID threshold

Fetch the results as an array of objects

Display the user login names in a loop

💡 Why This Matters

🌍 Real World

Direct database queries with wpdb are useful when you need custom data retrieval beyond WordPress built-in functions, such as custom reports or plugin features.

💼 Career

Knowing how to safely query the WordPress database directly is a valuable skill for WordPress developers building custom plugins or themes.

1

Set up the global wpdb object and write the base query

Write the line to declare the global $wpdb variable and create a string variable called $query with the value "SELECT * FROM $wpdb->users".

Wordpress

# Declare global $wpdb and create base query string
# Your code here

Hint

Remember to use global $wpdb; to access the database object inside your function or file.

2

Add a variable to filter users by minimum ID

Create a variable called $min_user_id and set it to 5. Then update the $query string to select users with ID greater than or equal to $min_user_id using a WHERE clause.

Wordpress

users}";

// Add minimum user ID filter
# Your code here

Hint

Use a WHERE clause in your SQL query to filter by ID.

3

Run the query and get results as objects

Use the $wpdb->get_results() method with the $query variable to fetch the results. Store the results in a variable called $users.

Wordpress

users} WHERE ID >= $min_user_id";

// Fetch users from database
# Your code here

Hint

Use $wpdb->get_results() to get an array of objects from your SQL query.

4

Loop through results and display user login names

Write a foreach loop to go through the $users array. Inside the loop, echo the user_login property of each user object inside a paragraph tag.

Wordpress

users} WHERE ID >= $min_user_id";
// Fetch users from database
$users = $wpdb->get_results($query);

// Loop and display user login names
# Your code here

Hint

Use foreach to loop through the array and access each user's user_login property.

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