WordPress database functions

Table creation in WordPress

For plugins or themes often database connections are needed to store settings for APIs or other things. Below you will find the code for creating a new table in the database. We use a plugin for this. The connection is made via $wpdb with the WordPress database credentials:

function create_plugin_database_tables( ) {
    global $wpdb, $table_prefix;
    require_once( ABSPATH . '/wp-admin/includes/upgrade.php' );
    $new_table = $table_prefix . "plugin_table_name";
    if( $wpdb->get_var( "show tables like '$new_table'" ) != $new_table )
    {
        $sql = "CREATE TABLE `" . $new_table . "` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `account_name` varchar(255) NOT NULL,
          `account_type` int(11) NOT NULL,
          `transfer_state` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
        dbDelta($sql);
    }
}
register_activation_hook( __FILE__, 'create_plugin_database_tables' );

function create_plugin_database_tables( ) {

    global $wpdb, $table_prefix;

    require_once( ABSPATH . '/wp-admin/includes/upgrade.php' );

    $new_table = $table_prefix . "plugin_table_name";

    if( $wpdb->get_var( "show tables like '$new_table'" ) != $new_table )

    {

        $sql = "CREATE TABLE `" . $new_table . "` (

          `id` int(11) NOT NULL AUTO_INCREMENT,

          `account_name` varchar(255) NOT NULL,

          `account_type` int(11) NOT NULL,

          `transfer_state` varchar(255) DEFAULT NULL,

          PRIMARY KEY (`id`)

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ";

        dbDelta($sql);

    }

}

register_activation_hook( __FILE__, 'create_plugin_database_tables' );

The plugin creates a 4 column table when activated. This then looks like this:

Name `wp_plugin_table_name`

`id` int(11)

`account_name` varchar(255)

`account_type` int(11)

`transfer_state` varchar(255)

In the following examples, this table is used as a starting point.

Options with WordPress DB

WordPress DB (short $wpdb) offers the same structure as normal MySQL Script. These are also used via $wpdb in the same complexity. Standard functions like SELECT, INSERT, UPDATE and DELETE are covered here in more detail.

WordPress database: SELECT

The output shows all account names which have the fictitious account_name = 123aaa and account_type = 1.

global $wpdb;
$session_id = "123aaa";
$dbquery = $wpdb->prepare( "SELECT * FROM ".$wpdb->prefix."plugin_table_name WHERE account_name = %s AND account_type = %d", $session_id, 1 );
$db_entries = $wpdb->get_results( $dbquery );
if(count( $db_entries ) != 0 ) {
	foreach( $db_entries as $src_entry ) {
    	$account_name = $src_entry->account_name;
      	return $account_name;
    }
}

global $wpdb;

$session_id = "123aaa";

$dbquery = $wpdb->prepare( "SELECT * FROM ".$wpdb->prefix."plugin_table_name WHERE account_name = %s AND account_type = %d", $session_id, 1 );

$db_entries = $wpdb->get_results( $dbquery );

if(count( $db_entries ) != 0 ) {

    foreach( $db_entries as $src_entry ) {

        $account_name = $src_entry->account_name;

        return $account_name;

    }

}

Strictly speaking, all the magic happens on line 3.

In this query, % variables are used. %s stands for string, a type assignment as a placeholder. %d stands for decimal number, also a placeholder. The values for the query are appended as parameters at the end of the prepare function.

WordPress database: INSERT

global $wpdb;
$wpdb->insert( $wpdb->prefix . "plugin_table_name", 
    array(
		"account_name" => "123aaa",
		"account_type" => 1,
		"transfer_state" => "transfered"
	)
);

global $wpdb;

$wpdb->insert( $wpdb->prefix . "plugin_table_name", 

    array(

        "account_name" => "123aaa",

        "account_type" => 1,

        "transfer_state" => "transfered"

    )

);

The first column id in our table example has a PRIMARY key and the option auto_increment. This ensures that the number of records is incremented automatically.

WordPress database: UPDATE

$wpdb->update( $wpdb->prefix . 'plugin_table_name',
	array(
		'account_name' => "124abb",
		'account_type' => 2,
		'transfer_state' => "finished",
	),
	array('account_type' => 1, 'transfer_state' => "tranfered"),
	array('%s', '%d', '%s'),
	array('%d', '%s')
);

$wpdb->update( $wpdb->prefix . 'plugin_table_name',

    array(

        'account_name' => "124abb",

        'account_type' => 2,

        'transfer_state' => "finished",

    ),

    array('account_type' => 1, 'transfer_state' => "tranfered"),

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

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

);

The last two arrays contain type definitions. First of the last two with three elements specifies which data in the table is updated, in this example %s -> string, %d -> decimal number, %s -> string. The last one checks the search data (usually WHERE) for type of account_type and transfer_state.

WordPress database: DELETE

global $wpdb;
$wpdb->delete($wpdb->prefix . 'plugin_table_name', array('account_typ' => 2));

global $wpdb;

$wpdb->delete($wpdb->prefix . 'plugin_table_name', array('account_typ' => 2));

All records which have account_type = 2 will be deleted irrevocably.

Source: https://codex.wordpress.org/Database_Description