WordPress is the leading blogging platform. In the last decade or so, it has been adopted as a great platform for building dynamic websites. The reason for its increased popularity can be attributed to its huge community and user-friendly interface. One thing that most people don’t know is that this platform is PHP based and uses MySQL database as the backend database.
MySQL is a database management system that is used by WordPress to store and retrieve all your blog information. MySQL is an open source relational database management system. It runs as a server and allows multiple users to manage and create numerous databases.
This article will provide some important guidelines when manipulation your MySQL database manually for your website or blog with wordpress
WordPress provides a global object variable, $wpdb, which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php. By default, $wpdb is instantiated to talk to the WordPress database. To access $wpdb in your WordPress PHP code, declare $wpdb as a global variable using the global keyword, or use the superglobal $GLOBALS in the following manner:
Example:
// 1st Method – Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object
global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );// 2nd Method – Utilizing the $GLOBALS superglobal. Does not require global keyword ( but may not be best practice )
$results = $GLOBALS['wpdb']->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );
Assuming that the table prefix is wp_:
$wpdb->posts will correspond to wp_posts table
$wpdb->postmeta will correspond to wp_postmeta table
$wpdb->users will correspond to wp_users table
INSERT ROWS
Insert a row into a table. This function returns false if the row could not be inserted. Otherwise, it returns the number of affected rows (which will always be 1).
$wpdb->insert( $table, $data, $format );
Parameters
table
(string) The name of the table to insert data into.
data
(array) Data to insert (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped).
format
(array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.
Examples
$wpdb->insert( 'table', array( 'column1' => 'value1', 'column2' => 123 ), array( '%s', '%d' ) );
UPDATE ROWS
Update a row in the table. Returns false if errors, or the number of rows affected if successful.
$wpdb->update( $table, $data, $where, $format = null, $where_format = null );
Parameters
table
(string) The name of the table to update.
data
(array) Data to update (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped). This means that if you are using GET or POST data you may need to use stripslashes() to avoid slashes ending up in the database.
where
(array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be “raw”.
format
(array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.
where_format
(array|string) (optional) An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where.
Possible format values: %s as string; %d as integer (whole number) and %f as float. (See below for more information.) If omitted, all values in $where will be treated as strings.
Examples
$wpdb->update( ‘table’, array(
‘column1’ => ‘value1’, // string
‘column2’ => ‘value2’ // integer (number)
),
array( ‘ID’ => 1 ),
array(
‘%s’, // value1
‘%d’ // value2
),
array( ‘%d’ )
);
DELETE ROWS
The delete function was added in WordPress 3.4.0, and can be used to delete rows from a table. It returns the number of rows updated, or false on error.
$wpdb->delete( $table, $where, $where_format = null );
Parameters
$table
(string) (required) Table name.
Default: None
$where
(array) (required) A named array of WHERE clauses (in column -> value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be ‘raw’.
Default: None
$where_format
(string/array) (optional) An array of formats to be mapped to each of the values in $where. If a string, that format will be used for all of the items in $where. A format is one of ‘%d’, ‘%f’, ‘%s’ (integer, float, string; see below for more information). If omitted, all values in $where will be treated as strings unless otherwise specified in wpdb::$field_types.
Default: null
Examples
// Default usage.
$wpdb->delete( 'table', array( 'ID' => 1 ) );
// Using where formatting.
$wpdb->delete( 'table', array( 'ID' => 1 ), array( '%d' ) );
And to find out more about $wpdb, you can visit the following link: WordPress Codex
Finally, we hope the above information will help you work better MySQL with WordPress
Too see more awesome entry from Cactusthemes, please visit our Blog!