Execute Custom Query in WordPress

  • Avinash
  • 2
  • Aug 17, 2011
  • Web Development

WordPress is a great platform to build the any types of sites in now a days. It has a great functionality and feasibility to perform our own stuffs. Because of this feasibilty we can create our own code and plug that code into wordpress.

For the default wordpress tables they have already created functions like get_page(), wp_nav_menu(), etc. But for some custom plugin we need to fetch data from our own custom tables.

WordPress has given a great flexibility for this. WordPress uses the ezSQL PHP class to interation with the database. WordPress has provided the $wpdb class object which used to call the database related function.

Note: Make sure you globalize $wpdb to use in any custom function.

To run any query on Database using $wpdb:

$wpdb->query("your query to custom table");
$wpdb->query("your query to custom table");

Above function will return the integer based inserted/affected rows or selected rows. If any error occurs then it will return FALSE.

Please make sure that you escape the data entered by the user by the escape function before passing to the query like below.

$wpdb->escape($user_entered_data_string)
$wpdb->escape($user_entered_data_string)

Select Data using $wpdb:

WordPress has four functions to select the data from the database.

1) get_var()

This function is used to fetch the single value from the database. Like total row count or sum of any column.

$wpdb->get_var("query",column_offset,row_offset);
$wpdb->get_var("query",column_offset,row_offset);

2) get_row()

This function is used to fetch the entire row from the table. We can get the result in diffrent format base on second parameter of the function call. It can be Object, Associative Array or numerical indexed array.

$wpdb->get_row("query",output_type,row_offset);
$wpdb->get_row("query",output_type,row_offset);

3) get_col()

This function used to fetch the single column from the table.

$wpdb->get_col("query",column_offset);
$wpdb->get_col("query",column_offset);

4) get_results()

This function is used to get the results in normal way like to fetch multiple rows from one query. Returned data can be of various types like OBJECT, OBJECT_K, ARRAY_A and ARRAY_N.

$wpdb->get_results("query","output type");
$wpdb->get_results("query","output type");

Insert Rows using $wpdb:

Here is the basic usage for inserting rows in wordpress database using $wpdb.

$wpdb->insert( $table, $data, $format );
$wpdb->insert( $table, $data, $format );

$table:
name of the table to insert data into.

$data:
Key value paired array which holds the values to be inserted.

$format:
This is the optional and which can be %d for decimal, %s for string and $f for float.

Note: After successful insertion of the row the last inserted ID can be accessed via $wpdb->insert_id.

Update rows using $wpdb:

This function returns FALSE if error, or number of affected rows upon success.

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );
$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

$table:
name of the table to update the data into.

$data:
Key value paired array which holds the updated values for the row.

$where:
A named array which holds the column name as a key and value as a actual value for condition.

$format:
This is optional which defines the format for the $data array.

$where_format:
This is optional which defines the format for the $where array.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: