Posts Tagged ‘mySQL’

Querying the wordpress database with $wpdb from PHP

September 19th, 2012

I have been working with WordPress a lot lately and I really like it. I want to have 10,000 of it’s babies. I recently needed to access the database using the $wpdb class that is built in to wordpress, but my PHP file was not part of the wordpress core, so $wpdb wasn’t automatically available. It’s really useful for accessing info like the root URL of your site (stored in the wp_options table). If you are creating a plugin or custom feature and need access to the database, it can be hard to figure how to enable the $wpdb class in your PHP files. Luckily, the code is actually not that complicated. The first thing you have to do is include the necessary files at the top of your custom php file:

include_once '/wp-load.php';
//$wpdb is now accessible!

If you uncomment the var_dump line in the code above and run this php file, it should spit out a lot of information. If it doesn’t, then you aren’t including the wp-load.php file correctly. You must have WordPress 3.0 or higher installed and your PHP file must be installed somewhere in the same directory as WordPress. You can also use a relative path if you need to:

include_once '../../../wp-load.php';
//$wpdb is now accessible!

Just make sure you have your relative path correct. It may be different, depending on where your PHP file is located in your wordpress installation.

Now that you have the $wpdb class enabled, you can query the database. Going back to my example above, if you need to retrieve your site URL, you could add the following lines to the PHP file we just created:

$table_name = $wpdb->prefix . "options";
$sql = "SELECT option_value FROM ".$table_name." WHERE option_name = 'siteurl';";
$root_url = $wpdb->get_var($sql);

This uses the get_var() function of $wpdb to return a single item (your site url) from the wp_options table. Notice that on the first line, I also used the $wpdb->prefix property, which in almost all cases, will be “wp_”.

What if you wanted to run a mySQL query on a table? That’s simple too! just use the query() method of $wpdb. For example, we can insert values into a custom table that we created for a plugin:

$table_name = $wpdb->prefix . "my_custom_table";

$sql = "INSERT INTO ".$table_name." (
              ) VALUES (
                'some value',
                'some other value'
$result = $wpdb->query($sql) or die(mysql_error());
//do something with the result here

You can even do more complex things, like create the tables you need for a plugin. The following function runs a mySQL query to create a table for a plugin:

function create_my_custom_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . "my_custom_table";
    global $my_custom_db_table_version;
    $installed_ver = get_option( "my_custom_db_table_version" );
    //Check if the table already exists and if the table is up to date, if not create it
    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name || $installed_ver != $my_custom_db_table_version ) {
        $sql = "CREATE TABLE " . $table_name . " (
              id mediumint(9) NOT NULL AUTO_INCREMENT,
              field_1 varchar(100),
              field_2 varchar(100),
              UNIQUE KEY id (id)

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        update_option( "my_custom_db_table_version", $my_custom_db_table_version );
    //Add database table versions to options
    add_option("my_custom_db_table_version", $my_custom_db_table_version);

The code above is actually a function that will check the wp_options table for a field called “my_custom_db_table_version.” If it exists, it compares the value to a global variable $my_custom_db_table_version. If there is a newer version of the table or if the version can’t be found in the database, it creates/updates the custom table.

As with most things, wordpress makes it easier once you know the basic code. Good Luck!

How to fix Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource

June 1st, 2010

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in… blah blah blah.

If you need to useĀ  mysql_free_result(), you may have received this error. What causes it and how can you fix it?

First, what is mysql_free_result()? When you make mySQL database queries that return large sets of data, it can cause your server to bog down because those results are hogging up a lot of memory. On a high-traffic site, it can actually crash your server as more and more of those results build up. So, mysql_free_result() is a built-in PHP function that releases the results of your query from memory. Again, this is usually not a big deal unless you are returning large data sets from your queries, but it is always good practice to clear your results and free up the memory on your server. Here is an example of what the PHP code might look like:


$query = "SELECT * FROM employees WHERE age >= 30";
if ($result = mysql_query($query)) {
	while ($row = mysql_fetch_array($result)) {		
	mysql_free_result($result); //free the query results from memory


But, if you have strict PHP warnings enabled on your server, you may start getting the warning above for some (but not all) of your queries. I had a really hard time tracking this problem down, but I finally found it here. You only use mysql_free_result() for the following query types: SELECT, SHOW, EXPLAIN, and DESCRIBE. So, if you perform an UPDATE query, it doesn’t return a valid resource and throws the warning when you call mysql_free_result(). Duh.