WordPress Custom CRUD Operations Using `$wpdb`

In WordPress, $wpdb is the built-in database abstraction class that allow you to interact with the WordPress database securely and efficiently.

In this post I to try to walk you thought how to implement custom CRUD (Create, Read, Update, and Delete) operations using $wpdb in a WordPress plugin or theme.

We’ll cover:

How to create a custom table
Implementing the Create, Read, Update, and Delete operations

Step 1: Setting up the Custom Table

Before performing CRUD operations, we need a table to work with, For demonstration, we’ll create a custom table that stores a user’s basic information, like name and email.

Example Table Structure

id: Primary key (auto-incremented)
name: User’s name
email: User’s email address
date_created: Date and time the record was created

Here’s how you can create the table using $wpdb:

// Function to create a custom table on plugin/theme activation
function create_custom_user_table() {
    global $wpdb;

    //Define the table name (prefix + custom_table)
    $table_name = $wpdb->prefix . 'custom_users';

    //Set the charset and collation
    $charset_collate = $wpdb->get_charset_collate();

    //SQL query to create the table
    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        email varchar(255) NOT NULL,
        date_created datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
    ) $charset_collate;
    ";

    //Include the upgrade function and run the query
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta($sql);
}

Step 2: Create CRUD Operations

Now that we have the table. let’s look at each CRUD operation.

2.1 Create: Inserting Data into the Table

To insert new records into the custom table, we can use $wpdb->insert(). This function provides an easy and secure way to add data to a table.


//Function to insert a new user into the custom_users table
function insert_user($name, $email) {
    global $wpdb;

    //Table name
    $table_name = $wpdb->prefix . "custom_users";

    //Insert the new user
    $wpdb->insert(
        $table_name,
        array(
            'name' => $name,
            'email' = $email,
            'date_created' => current_time('mysql');
        )
    );
}

insert_user("Chethan S Poojary", "chethanspoojary1@gmail.com");

In this example, we use the $wpdb->insert() method to add a new record into the custom_users table. The method accepts the table name and an associative array of column names and values.

2.2 Read: Retrieving Data from the Table

To retrieve data from the custom table, we can use $wpdb->get_results() for multiple rows or $wpdb->get_row() for a single row.

Example 1: Get All Users


//Function to get all users from the custom_users table
function get_all_users() {
    global $wpdb;

    //Table name
    $table_name = $wpdb->prefix . 'custom_users';

    //Query to retrieve all users
    $results = $wpdb->get_results("SELECT * FROM $table_name");

    return $results;
}

// Example of using the function to get all users
$users = get_all_users();
foreach ($users as $user) {
    echo 'Name: ' . $user->name . ' | Email: ' . $user->email . '<br>';
}

In this example, we use $wpdb->get_results() to retrieve all rows from the custom_users table. The function returns an array of objects where each object represents a row in the table.

Example 2: Get a Single User by ID


function get_user_by_id($user_id) {
    global $wpdb;

    //Table name
    $table_name = $wpdb->prefix . "custom_users";

    //Query to get a specific user
    $result = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $user_id));

    return $result;
}

// Example of using the function to get a user by ID
$user = get_user_by_id(1);
echo 'Name: ' . $user->name . ' | Email: ' . $user->email;

In this example, we use $wpdb->prepare() to ensure the query is safe from SQL injection. We retrieve a single row by using $wpdb->get_row(), which returns a single object for that record.

2.3 Update: Updating Data in the Table

To update an existing record, you can use $wpdb->update(). This function works similarly to $wpdb->insert(), but it updates records instead.

// Function to update a user's details
function update_users($user_id, $name, $email) {
    global $wpdb;

    //Table name
    $table_name = $wpdb->prefix . "custom_users";

    //Data to update
    $data = array(
        'name' => $name,
        "email" => $email
    );

    //Where condition
    $where = array('id' => $user_id);

    //Udpate the user
    $wpdb->update($table_name, $data, $where);

}

// Example of using the function to update a user's information
update_user(1, 'Chethan Updated', 'chethan.updated@example.com');

Here, $wpdb->update() takes three arguments:

The table name.
An array of data to update.
A WHERE condition to target the correct row(s).

2.4 Delete: Deleting Data from the Table

To delete a record, we can use $wpdb->delete(). This method securely deletes records based on the specified condition.

//Function to delete user by ID
function delete_user($user_id) {
    global $wpdb;

    //Table name
    $table_name = $wpdb->prefix . "custom-users";

    //Delete the user by ID
    $wpdb->delete($table_name, array("id" => $user_id));
}

// Example of using the function to delete a user by ID
delete_user(1);

In this example, $wpdb->delete() takes two arguments:

The table name.
An associative array of the condition to match the row(s) you want to delete (e.g., id = 1).

Conclusion

With these simple examples, you can easily implement Create, Read, Update, and Delete (CRUD) operations in WordPress using $wpdb. The key points to remember are:

Use $wpdb->insert() for creating records.
Use $wpdb->get_results() or $wpdb->get_row() to read data.
Use $wpdb->update() for modifying existing data.
Use $wpdb->delete() for removing records.

These methods are safe from SQL injection, as long as you use $wpdb->prepare() when building your queries. By using $wpdb, you ensure that your custom queries are compatible with the WordPress database and work seamlessly across different environments.

This approach can be used for building custom database interactions in WordPress plugins or themes, providing an efficient and secure way to manage custom data.