Home | About | View All Posts

1 Jan 2015

Database interaction with $wpdb in wordpress plugin development

Wordpress uses inbuilt class "wpdb" which resides in '/wp-includes/wp-db.php' There are multipurpose functions or methods available in Wpdb class to interact with wordpress database tables or custom plugin tables. These methods is called by using the global $wpdb object at script page.


Database interaction can be divided under following points:
  1. Calling $wpdb object
  2. Using $wpdb
    1. Executing SQL query using function - query('query_string')
    2. Getting single variable using function - get_var('query', column_offset, row_offset)
    3. Retrieving an entire row using function - get_row('query', output_type, row_offset)
    4. Getting a column using function - get_col('query', column_offset)
    5. Pulling multiple rows result using function - get_results('query', output_type)
    6. Inserting a row into a table using function - insert($table, $data, $format)
    7. Replacing a row in a table using function - replace($table, $data, $format)
    8. Updating a row in the table using function - update($table, $data, $where, $format = null, $where_format = null)
    9. Retrieving columns information using function - get_col_info('type', offset)
    10. Deleting a row in the table using function - delete($table, $where, $where_format = null)
    11. Clearing the SQL result cache using function - flush()
    12. Retrieving information using class variables



  1. Calling $wpdb object
    There are two methods to call or use the $wpdb object.
    Method 1 -
    We declare $wpdb as global variable to execute an SQL query statement. For example -

    global $wpdb;
    $results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

    Method 2 -
    We use the $GLOBALS superglobal variable to execute an SQL query statement. For example -

    $results = $GLOBALS['wpdb']->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

  2. Using $wpdb
    1. Executing SQL query using function - query('query_string'):
      It returns an integer value which shows the number of rows affected/selected. For CREATE, ALTER, TRUNCATE and DROP SQL statements, this function returns TRUE on success. If a MySQL error is encountered, the function will return FALSE.

      Syntax -
      <?php $wpdb->query('query'); ?>

      Example 1 -
      Delete the 'FeatureImage' meta key and value from Post 12. (the method 'prepare' is used to avoid SQL injection or an illegal operation or any illegal characters. It uses query with placeholders and additional arguments to replace that placeholders):
      $wpdb->query(
      $wpdb->prepare("DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s", 12, 'FeatureImage')
      );
      

      Example 2 -
      Update the post parent value which have post id 15 and status as stattic.
      $wpdb->query("UPDATE $wpdb->posts SET post_parent = 7 WHERE ID = 15 AND post_status = 'static'");

      or using prepare() method
      $wpdb->query("UPDATE $wpdb->posts SET post_parent = 7 WHERE ID = %d AND post_status = %s", 7, 'static');
    2. Getting single variable using function - get_var('query', column_offset, row_offset) :
    3. Retrieving information using class variables
      $wpdb->prefix shows database table prefix.

      For example -
      $q_for_mlm_user = $wpdb->prepare( "UPDATE $wpdb->prefix".mlm_users." SET username = %s WHERE username = %s", $new_username, $current_username );
NOTE:
You can use $wpdb object to get data from non wordpress tables(like custom plugin tables) as example below. This script selects data from a custom table named as 'custom_table_name'.

$custom_rows = $wpdb->get_results( "SELECT id, name FROM custom_table_name" );
Tags : , , ,

1 comments: