Running database queries in WordPress

WordPress has a built-in object class for dealing with database queries. It’s called wpdb and it’s found in the includes/wp-db.php file. When you’re running queries you should always use this class to execute them. To use this class you first need to define $wpdb as a global variable before you can use it. Just place this line of code before every $wpdb function:

global $wpdb

 Within your wpdb functions you should use the prepare() function. This secures your query, preventing SQL injections.

Functions for running database queries

$wpdb->query – any query function

The $wpdb->query method is used mainly for SELECT and DELETE statements but can actually be used for any sql statement

$wpdb->query($wpdb->prepare ("DELETE FROM $wpdb->custom_table WHERE ID = 1" ));

 As you can see, we use the wp->custom_table to reference a custom table in our query. 

$wpdb->get_var – return single variable

The get_var() function is used for retrieving a single variable from the database

$count_posts = $wpdb->get_var( $wpdb->prepare(" SELECT COUNT(*) FROM $wpdb->wp_posts" ));
echo "$count_posts";

 This will return the number of posts.

$wpdb->get_row – return single row

To retrieve an entire row you’ll use the get_row() function. By default the row is returned as an object, but you can add the ARRAY_A or ARRAY_N parameter at the end of your function. The ARRAY_A parameter will return an associative array and the ARRAY_N parameter will return a numerically index array

$wpdb->get_row( $wpdb->prepare( "SELECT * from $wpdb->posts WHERE ID=1"), ARRAY_A );

$wpdb->get_results – return multiple rows

To retrieve multiple rows from a table you’ll use the $wpdb->get_results() function.

$wpdb->get_results( $wpdb->get_results( $wpdb->prepare( " SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' "));

 This query will retun all the published posts as an object.

$wpdb->insert – Insert query

The are specific wordpress wpdb functions for an insert or update sql. The basic usage of the insert() function

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

 An example of an insert sql query:

$firstname = "My first name";
$lastname = "My last name";

$wpdb->insert( $wpdb->custom_table, array("firstname" => $firstname, "lastname" => $lastname ));

 You first need to set your table and then pass your field values in an array. No need to use the prepare function here. I’ll explain why in the update statement query.

$wpdb->update – update query

We do a update query in a similar fashion. We first set our table, then pass our field values in an array and then set our where clause variable.

$wpdb->update( $table, $data, $where );

 The where variable is an array of field names and values for the where clause.  In this example we’ll use a unique ID in our where clause.

$wpdb->update( $wpdb->posts, array( 'post_title' => "my new title", 'post_content' => "my new content"), array( "ID", 1));

Both the insert() and update() do not need to use the prepare() function because both these functions use the prepare function internally.  

 

Useful database functions

$wpdb->num_rows – number of rows returned

We use the num_rows() function to return the number of rows returned by a sql query.

var_dump( $wpdb->num_rows);

 $wpdb->print_error() – display sql errors

 When dealing with custom functions it can be hard to debug what’s wrong with your sql. The print_error() function will display the errors of your sql to the page

$wpdb->show_errors();
$wpdb->get_row( $wpdb->prepare(" SELECT * FROM $wpdb->posts WHERE ID = 3000"); // unexisting ID
$wpdb->print_error();

 The show_errors() function must be called before executing a sql query. The print_error() function directly after running the query.

$wpdb->last_query – print last query executed

The last_query() function will print the last sql query executed. This can be useful to test why a query isn’t working as expected.

var_dump( $wpdb->last_query );

7 thoughts on “Running database queries in WordPress

  1. i want know how to insert multiple value like
    insert table(id,name)values(1,a)(2,b)

  2. @satyendra as mentioned you just insert an array of values

    $wpdb->insert( $wpdb->custom_table, array(“firstname” => $firstname, “lastname” => $lastname ));

  3. Dear , thanks for the information.

     

    I am struggling to find out , how to put the code in page. Code isnot getting executed. I have installed php-exec plugin also but no help !!

     

    Kindly help to suggest where shoudl i make changes to execute above code in pages. Idea is to display data fetched from tables on pages.

  4. hi Steven Dobbelaere,
    nice article …. i need little help with that insert query is working fine but i am trying to delete a database row as well as update my data which is fetching from database
    here is my code

    prefix . "tropix_dmin";
    $query = "
    SELECT post_name1,id
    FROM $post_name_table";

    $post_name_results = $wpdb->get_results($query);

    function delete_row($id){
    global $wpdb;
    $table_name = $wpdb->prefix . 'tropix_dmin';
    $wpdb->query(
    $wpdb->prepare(
    "
    DELETE FROM $table_name
    WHERE id = %d",
    $id
    )
    );
    }

    ?>

    Street Address
    Apartment No
    Detail
    Delete

    post_name1);

    if($this_data != ""){

    ?>

    View Detail

    delete

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s