Warning: preg_replace(): Compilation failed: invalid range in character class at offset 4 in /home/customer/www/wpdb.boilingpotmedia.com/public_html/wp-content/plugins/crayon-syntax-highlighter/crayon_langs.class.php on line 340
WordPress - quickguide for all things global $wpdb;
Love &wpdb; ? wpdbtee Wear &wpdb; Click here, get gear.

Intro - About &WPDB

WordPress offers the $wpdb class for interacting with your website's database. The methods of this class can access native and custom tables.

The methods of $wpdb are accessed through the global object variable $wpdb.

Errors & Warnings - Common problems and simple solutions.

  • WordPress $wpdb Fatal error: Call to a member function on a non-object

    You may be seeing an error like this:

    [php]

    Fatal error: Call to a member function get_var() on a non-object…
    Fatal error: Call to a member function get_var() on a non-object…
    Fatal error: Call to a member function get_var() on a non-object…
    Fatal error: Call to a member function get_var() on a non-object…

    [/php]

    Super scary, huh? Well, it’s also super easy to solve!

    You probably just forgot to call the {php}$wpdb{/php} object into your PHP. Just add {php}global $wpdb;{/php} at the beginning of your function and you should be off to the races.

    [php]
    add_action(‘wp_footer’, ‘my_function’);
    function my_function() {
    global $wpdb;
    // the rest of the function
    }
    [/php]

  • WordPress $wpdb Warning – Missing argument 2 for wpdb::prepare()

    If you are seeing this error, then you are probably using {php}wpdb::prepare(){/php} like this:

    [php]$wpdb->prepare( “SELECT * FROM table WHERE id = $id” );[/php]

    You can not safely pass a PHP variable (in the example, {php}$id{/php}) directly into {php}wpdb::prepare(){/php} or your code will be vulnerable to SQL injection attacks. WordPress knows that if there is just one argument within the parentheses, that the argument contains a variable. In the example above, the argument is everything within the double quotes.

    To solve the problem, update your syntax to use more than one argument, and move the variable outside of the existing argument:

    [php]$wpdb->prepare( “SELECT * FROM table WHERE %d”, $id );[/php]

    If you have more than one variable, ensure that both are their own argument:

    [php]
    $id = 1432;
    $key = Jim;
    $wpdb->prepare( “SELECT * FROM table WHERE %d AND key = %s”, $id, $key);
    [/php]

    Learn more about this error here.

Delete - Remove data from the database.

  • WordPress $wpdb->delete

    Example code for deleting a single row in a WordPress table by targeting one data:

    [php]
    $wpdb->delete(
    ‘wp_table’, // table to delete from
    array(
    ‘column1’ => 123 // value in column to target for deletion
    ),
    array(
    ‘%d’ // format of value being targeted for deletion
    )
    );
    [/php]

    You probably want to do something more complicated than delete a row. In that case, you will have to use {php}$wpbd->query{/php} to pass a SQL function:

    [php]
    $wpdb->query(
    $wpdb->prepare(

    DELETE wp_table
    WHERE column1 = %d
    AND column2 = %s
    ‘,
    13, ‘jim’
    )
    );
    [/php]

Insert - Add new data to the database.

  • WordPress $wpdb->insert

    Example code for inserting a row into a WordPress table that has three columns:

    [php]
    $wpdb->insert(
    ‘wp_table’,
    array(
    ‘column1’ => ‘value1’,
    ‘column2’ => 123,
    ‘column3’ => $var,
    ),
    array(
    ‘%s’,
    ‘%d’,
    ‘%s’
    )
    );
    [/php]

Replace - Swap out data from the database.

  • WordPress $wpdb->replace

    What’s the difference between {php}$wpdb->replace{/php} and {php}$wpdb->update{/php} (or other methods)? {php}$wpdb->replace{/php} will perform a delete and an insert. But remember: if there are one or more rows in the table that share a value for the ‘unique_index’, all of the rows that have that value will be deleted and just one new row will be inserted.

    Example code to replace a row in a WordPress table that has three columns:

    [php]
    $wpdb->replace(
    ‘wp_table’,
    array(
    ‘unique_index’ => ‘value1’,
    ‘column2’ => 123,
    ‘column3’ => $var,
    ),
    array(
    ‘%s’,
    ‘%d’,
    ‘%s’
    )
    );
    [/php]

Select - Get data out of the database.

  • WordPress $wpdb Select Operations

    Example code to get a value from the database into a variable:

    [php]
    $variable = $wpdb->get_var(
    $wpdb->prepare(‘
    SELECT column3
    FROM wp_table
    WHERE column1 = %s
    AND column2 = %d
    ‘,
    $var1,
    123
    )
    );
    [/php]

    Example code to get an entire row from the database. Note – the output type may be specified:

    • OBJECT, or no specification, results in an object.
    • ARRAY_A results in an associative array.
    • ARRAY_N results in an indexed array.

    [php]
    $variable = $wpdb->get_row(
    $wpdb->prepare(‘
    SELECT column3
    FROM wp_table
    WHERE column1 = %s
    AND column2 = %d
    ‘,
    $var1,
    123
    ),
    ARRAY_A
    );
    [/php]

    Example code to get generic results from the database. Note – the output type may be specified (Defaults to OBJECT):

    • OBJECT – result will be output as a numerically indexed array of row objects.
    • OBJECT_K – result will be output as an associative array of row objects, using first column’s values as keys (duplicates will be discarded).
    • ARRAY_A – result will be output as a numerically indexed array of associative arrays, using column names as keys.
    • ARRAY_N – result will be output as a numerically indexed array of numerically indexed arrays.

    [php]
    $variable = $wpdb->get_results(
    $wpdb->prepare(‘
    SELECT column3
    FROM wp_table
    WHERE column1 = %s
    AND column2 = %d
    ‘,
    $var1,
    123
    ),
    OBJECT
    );
    [/php]

Update - Change existing data on the database.

  • WordPress $wpdb->update

    Example code to update a single cell in a WordPress table that has three columns:

    [php]
    $wpdb->update(
    wp_table,
    array(
    ‘column3’ => $variable, // the column to update
    ),
    array(
    ‘column1’ => 123, // the first WHERE argument
    ‘column2’ => ‘value2’, // additional WHERE argument!
    ),
    array(‘%s’), // the format of the update value
    array(
    ‘%d’, // the format of the first WHERE argument
    ‘%s’ // the format of the second WHERE argument
    )
    );
    [/php]