When I started developing plugins for WordPress I found it quite difficult to understand how to do database queries the right way. I had read about $wpdb, the $wpdb->prepare() method as well as different other methods like get_var(), get_results(), query()…
In the end I was pretty confused as to when and how to use which method and how to use prepared statements. So for anyone struggling about this as well I created this quick tip to show you best practice when creating MySQL queries in WordPress.
Selecting rows
If you want to select multiple rows as an array use the get_results() method. If you would like to add parameters into the query combine it with prepare().
$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM `".$wpdb->prefix."tablename` WHERE `foo` = %s ORDER BY `time` DESC LIMIT 50", $foo));
If you would like to select only a single row replace the get_results() with get_row().
Selecting the number of rows
If you would like to find out how many rows exist in your table then you should use get_var() like so:
$amount = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `".$wpdb->prefix."tablename` WHERE `foo` = %s", $foo));
Inserting new rows
If you would like to insert new data into your table use the insert() method and don’t combine it with prepare(). The $wpdb class will escape the string automatically.
$wpdb->insert('tablename', array('foo' => 'fooVal', 'bar' => 15), array('%s', '%u'));
As an alternative you can of course also use query() combined with prepare() to write your own MySQL insert code like so:
$wpdb->query($wpdb->prepare("INSERT INTO `".$wpdb->prefix."tablename` (`foo`, `bar`) VALUES (%s, %u)", $foo, 15));
However the preferred way is to use the built-in function insert() to insert new rows.
Deleting rows
If you would like to delete certain rows from your table please use the delete() method and don’t combine it with prepare(). The $wpdb class will again escape the string automatically.
$wpdb->delete('tablename', array('foo' => $foo, 'bar' => 15), array('%s', '%u'));
Just as before you could also manually write your SQL code and then use the query() method to execute it. However the recommended way is to use the built-in WordPress method delete().
Updating rows
If you would like to update a certain row in your table use the update() method.
$wpdb->update('tablename', array('foo' => $foo, 'bar' => $bar), array('foobar' => $foobar), array('%s', '%u'), array('%s'));
Just as before you could also manually write your SQL code and then use the query() method to execute it. However the recommended way is to use the built-in WordPress method update().
I hope this little quick tip can help someone when they struggle with prepared statements in WordPress. Please also take a look at this blog post of Cloudways if you want to find out more about how to improve MySQL performance in WordPress.
