How to Use wpdb for User Data Management in WordPress Plugin

Posted on 19th June 2023

Introduction

In this article, we will learn how to use wpdb for user data management in WordPress plugin. wpdb is WordPress class for accessing database. It is located in /wp-includes/wp-db.php.

We will use following user meta data for plugin development:

user_login
user_pass
user_email
user_url
user_registered
user_activation_key
user_status

First Name
Last Name
Nickname
Description

Creating a Plugin

We will start by creating a plugin. Create a new folder in your WordPress installation directory wp-content/plugins/ and name it “my-plugin”.

In this folder, create a new file and name it “my-plugin.php”.

Open “my-plugin.php” in a text editor and add following code in it:

<?php
/*
Plugin Name: My Plugin
Plugin URI: https://example.com/my-plugin
Description: This is my first plugin.
Author: John Doe
Version: 1.0
Author URI: https://example.com
*/

// Exit if accessed directly
if ( ! defined( 'ABSPATH' ) ) exit;

class My_Plugin
{
function __construct()
{
add_action( 'admin_menu', array( $this, 'add_menu_page' ) );
}

function add_menu_page()
{
add_menu_page(
'My Plugin',
'My Plugin',
'manage_options',
'my-plugin',
array( $this, 'menu_page_callback' ),
'dashicons-hammer',
4
);
}

function menu_page_callback()
{
echo '';
echo '

This is my first plugin.

‘;
}
}

new My_Plugin();
?>

This code creates a plugin with following features:

A menu page with title “My Plugin”
A submenu page with title “My Plugin” and same as menu page
The menu page and the submenu page have same callback function
The callback function echoes a heading and a paragraph

Creating a User Form

We will now create a user form to get user input. We will use this form to add, edit and delete users.

Add following code in your plugin file “my-plugin.php” after the code for creating the plugin:

// Exit if accessed directly
if ( ! defined( ‘ABSPATH’ ) ) exit;

class My_Plugin
{
function __construct()
{
add_action( ‘admin_menu’, array( $this, ‘add_menu_page’ ) );
}

function add_menu_page()
{
add_menu_page(
‘My Plugin’,
‘My Plugin’,
‘manage_options’,
‘my-plugin’,
array( $this, ‘menu_page_callback’ ),
‘dashicons-hammer’,
4
);
}

function menu_page_callback()
{
echo ‘

My Plugin

‘;
echo ‘

This is my first plugin.

‘;
}
}

new My_Plugin();
?>

This code will create a user form with following fields:

First Name
Last Name
Nickname
Description

The form will have following buttons:

Add User
Edit User
Delete User

The code for the form is as follows:

First Name
Last Name
Nickname
Description

Creating a Database Table

We will now create a database table to store user data.

Add following code in your plugin file “my-plugin.php” after the code for creating the user form:

// Exit if accessed directly
if ( ! defined( ‘ABSPATH’ ) ) exit;

class My_Plugin
{
function __construct()
{
add_action( ‘admin_menu’, array( $this, ‘add_menu_page’ ) );
}

function add_menu_page()
{
add_menu_page(
‘My Plugin’,
‘My Plugin’,
‘manage_options’,
‘my-plugin’,
array( $this, ‘menu_page_callback’ ),
‘dashicons-hammer’,
4
);
}

function menu_page_callback()
{
echo ‘

My Plugin

‘;
echo ‘

This is my first plugin.

‘;
}
}

new My_Plugin();
?>

This code will create a database table with following fields:

user_id
user_login
user_pass
user_email
user_url
user_registered
user_activation_key
user_status

First Name
Last Name
Nickname
Description

The table will have following indexes:

user_id
user_login
user_email

The code for creating the table is as follows:

global $wpdb;

$table_name = $wpdb->prefix . “my_plugin_table”;

$sql = “CREATE TABLE $table_name (
user_id bigint(20) unsigned NOT NULL auto_increment,
user_login varchar(60) NOT NULL default ”,
user_pass varchar(255) NOT NULL default ”,
user_email varchar(100) NOT NULL default ”,
user_url varchar(100) NOT NULL default ”,
user_registered datetime NOT NULL default ‘0000-00-00 00:00:00’,
user_activation_key varchar(255) NOT NULL default ”,
user_status int(11) NOT NULL default ‘0’,
PRIMARY KEY (user_id),
KEY user_login (user_login),
KEY user_email (user_email)
)”;

require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );
dbDelta( $sql );

Adding, Editing and Deleting Users

We will now write code for adding, editing and deleting users.

Add following code in your plugin file “my-plugin.php” after the code for creating the database table:

// Exit if accessed directly
if ( ! defined( ‘ABSPATH’ ) ) exit;

class My_Plugin
{
function __construct()
{
add_action( ‘admin_menu’, array( $this, ‘add_menu_page’ ) );
}

function add_menu_page()
{
add_menu_page(
‘My Plugin’,
‘My Plugin’,
‘manage_options’,
‘my-plugin’,
array( $this, ‘menu_page_callback’ ),
‘dashicons-hammer’,
4
);
}

function menu_page_callback()
{
echo ‘

My Plugin

‘;
echo ‘

This is my first plugin.

‘;
}
}

new My_Plugin();
?>

This code will add, edit and delete users using following user meta data:

user_login
user_pass
user_email
user_url
user_registered
user_activation_key
user_status

First Name
Last Name
Nickname
Description

The code for adding, editing and deleting users

If you want to use wpdb to manage user data in your WordPress plugin, there are a few things you need to keep in mind. First, wpdb is not designed for data management. It is designed for database interaction. Second, wpdb is not intended for use with large datasets. It is designed for small datasets. Finally, wpdb is not designed for use with WordPress multisite.

That said, wpdb can be used for user data management in WordPress plugins if you are careful and understand its limitations. Here are a few tips:

1. Choose the right data type for your needs. wpdb supports four data types: INT, FLOAT, DOUBLE, and STRING. Choose the data type that best fits your needs.

2. Use wpdb::prepare() for complex queries. wpdb::prepare() allows you to use placeholders in your SQL queries. This is useful for complex queries or queries with dynamic data.

3. Use wpdb::escape() for data that comes from untrusted sources. wpdb::escape() escapes data that comes from untrusted sources, such as user input. This is important for security.

4. Use wpdb::show_errors() to debug your queries. wpdb::show_errors() will show you any errors that occur when you run a query. This is useful for debugging.

5. Use wpdb::hide_errors() in production. wpdb::hide_errors() will hide any errors that occur when you run a query. This is important for security in production environments.

6. Use wpdb::suppress_errors() to handle errors gracefully. wpdb::suppress_errors() will suppress any errors that occur when you run a query. This is useful for handling errors gracefully.

7. Use wpdb::print_error() to print errors to the screen. wpdb::print_error() will print any errors that occur when you run a query to the screen. This is useful for debugging.

8. Use wpdb::debug() to debug your queries. wpdb::debug() will print all queries that are run to the screen. This is useful for debugging.

9. Use wpdb::get_var() to get a single value from the database. wpdb::get_var() is useful for getting a single value from the database, such as a count or an average.

10. Use wpdb::get_row() to get a single row from the database. wpdb::get_row() is useful for getting a single row from the database.

11. Use wpdb::get_results() to get multiple rows from the database. wpdb::get_results() is useful for getting multiple rows from the database.

12. Use wpdb::get_col() to get a single column from the database. wpdb::get_col() is useful for getting a single column from the database.

13. Use wpdb::insert() to insert data into the database. wpdb::insert() is useful for inserting data into the database.

14. Use wpdb::update() to update data in the database. wpdb::update() is useful for updating data in the database.

15. Use wpdb::delete() to delete data from the database. wpdb::delete() is useful for deleting data from the database.

Previous Post - Leveraging the Power of wpdb in WordPress Themes

Next Post - Tips for Secure Online Banking on the Google Pixel 6a