Plugin Interaction with WordPress Database. Part 2

Continuing our series of articles on working with the database in WordPress. Today, we’ll talk about creating, updating, deleting, and retrieving data from the database.
Please note that we are not working with WordPress system tables, but with a custom one.
The table dump is shown below:

CREATE TABLE IF NOT EXISTS `wp_plance_text_shortcodes` (
  `sh_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sh_title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sh_code` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sh_description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `sh_is_lock` tinyint(1) unsigned NOT NULL,
  `sh_date_create` int(10) unsigned NOT NULL,
  PRIMARY KEY (`sh_id`)
) ENGINE=InnoDB;

This is the current structure of the table used in my plugin “My Text Shortcodes”.

Inserting Data

To insert data into a custom WordPress table, I usually use the following method:

$wpdb -> insert(
	$wpdb -> prefix.'plance_text_shortcodes',
	array(
		'sh_title'		 => $data_ar['sh_title'],
		'sh_code'		 => $data_ar['sh_code'],
		'sh_description' => $data_ar['sh_description'],
		'sh_is_lock'	 => $data_ar['sh_is_lock'],
		'sh_date_create' => time(),
	),
	array('%s', '%s', '%s', '%d', '%s')
);

Let’s break it down. The first thing you need to do (not shown in the example above) is declare the global `$wpdb` variable. Without it, you won’t be able to interact with the WP database. Add this line at the beginning of your function or method:

global $wpdb;

This makes the `$wpdb` database object available in the current scope.

The `insert` function has the following structure:

insert( $table, $data, $format = null )

Where:
$table — the name of the table to insert into
$data — an associative array: keys are column names, values are the data to insert
$format — an array of format specifiers to sanitize the values (`%s` for string, `%d` for number)

Back to the example:

$wpdb -> prefix.'plance_text_shortcodes',

Where:
`$wpdb->prefix` — the table prefix
`plance_text_shortcodes` — our custom table name

The second argument is a data array:

array(
	'sh_title'		 => $data_ar['sh_title'],
	'sh_code'		 => $data_ar['sh_code'],
	'sh_description' => $data_ar['sh_description'],
	'sh_is_lock'	 => $data_ar['sh_is_lock'],
	'sh_date_create' => time(),
),

And the last parameter is the format array:

array('%s', '%s', '%s', '%d', '%s')

Where:
- %s — string
- %d — number

Honestly, I don’t remember why I defined `time()` (a number) as a string. If I recall correctly, WP was trimming the value.

Updating Data

Believe it or not 🙂 it’s almost the same as inserting:

update( $table, $data, $where, $format = null, $where_format = null )

Where:
- $table — the table to update
- $data — array of fields and new values
- $where — array of WHERE conditions
- $format — format array for $data
- $where_format — format array for $where

Example:

$wpdb -> update(
	$wpdb -> prefix.'plance_text_shortcodes',
	array(
		'sh_title'		 => $data_ar['sh_title'],
		'sh_code'		 => $data_ar['sh_code'],
		'sh_description' => $data_ar['sh_description'],
		'sh_is_lock'	 => $data_ar['sh_is_lock'],
	),
	array('sh_id' => $sh_id),
	array('%s', '%s', '%s', '%d'),
	array('%d')
);

In this example, we update the record with a specific `sh_id`.

Deleting Data

To delete data, use:

delete( $table, $where, $where_format = null )

Where:
- $table — table name
- $where — WHERE conditions
- $where_format — format array for WHERE conditions

Example:

$wpdb -> delete(
	$wpdb -> prefix.'plance_text_shortcodes',
	array('sh_id' => $sh_id),
	array('%d')
);

Or using a raw query:

$wpdb -> query("DELETE FROM `{$wpdb -> prefix}plance_text_shortcodes` WHERE `sh_id` = ".intval($sh_id));

Retrieving Data

Use:

get_results( $query = null, $output = OBJECT )

Where:
- $query — SQL query
- $output — return format:
- OBJECT or OBJECT_K — as objects
- ARRAY_A — associative array
- ARRAY_N — numeric array

To retrieve a single value:

get_var( $query = null, $x = 0, $y = 0 )

To retrieve a single row:

get_row( $query = null, $output = OBJECT, $y = 0 )

To get values from a single column:

get_col( $query = null , $x = 0 )

Prepared Queries

Sometimes `insert` and `update` aren’t flexible enough.
In that case, you can combine `query` with `prepare`, which helps sanitize and bind values.

Example:

$wpdb->query( $wpdb->prepare(
	"INSERT INTO {$wpdb -> prefix}plance_text_shortcodes "
	. "(sh_title, sh_code, sh_description, sh_is_lock, sh_date_create) "
	. "VALUES "
	. "(%s, %s, %s, %d, %s )",
	array(
		$data_ar['sh_title'],
		$data_ar['sh_code'],
		$data_ar['sh_description'],
		$data_ar['sh_is_lock'],
		time()
	)
));

The `prepare` method looks like this:

prepare( $query, $args )

Where:
- $query — SQL query string
- $args — array of values to insert

Thank you all! That’s it for now 🙂