As a plugin developer or WordPress hacker, accessing the database used by a WordPress install is vital. This can be accomplished through a few different means, but the best is by using the WPDB class that is provided. The only requirement for using this class is that your code exists within the WordPress install (plugins, themes, etc).
WPDB Queries
Let’s say that you would like to run a simple query that returns all of the rows in the “posts” table. With the WPDB class, all you need to do is execute:
1 | $rows = $wpdb->get_results( "SELECT * FROM $wpdb->posts" ); |
When this code is executed, it returns the entire table “posts” ($wpdb->posts) as an array of objects into the $rows variable. From there, it’s easy enough to iterate over the array using a foreach loop.
WPDB Insert
Inserting data into a table is easy using the WPDB class. All you need to know are the column name(s), the table name, and data you want to store. I’ll lead with an example:
1 | $wpdb->insert( 'links', array( 'link_url' => 're-cycledair.wploadtest.xyz', 'visit' => 12 ), array( '%s', '%d' ) ); |
This example of $wpdb->insert, inserts “re-cycledair.wploadtest.xyz” and “12” into the link_url and visit columns of the “links” table respectively. The third argument in this function is one that tells the WPDB what type these values are. The first value is a string, so we use “%s”, and the second is an integer, so we use “%d”.
If you would like to know the auto-incremented id of this insert, simply call:
1 | $wpdb->insert_id |
WPDB Update
Updating rows in a table is also easy with the WPDB class. Here is an example of an update.
1 | $wpdb->update( 'links', array( 'link_url' => 'wordpress.org'), array( 'ID' => 15), array( '%s'), array( '%d' ) ) |
As you can see, this works a lot like $wpdb->insert. The first argument is the table name. The second argument is an array of column-value pairs. The third argument is the where condition (if ID is equal to 15). The fourth argument tells the WPDB class that you are updating a string, and the fifth argument says the WHERE condition is an integer.
WPDB Prepare: Protect Against SQL Injection
One thing every WordPress developer needs to know about is SQL injection. SQL injection is when someone is able to modify your SQL query to execute their own. To prevent this kind of malicious attack, the WPDB class has a method called “prepare”. “Prepare” will take your input data an sanitize it, so that it cannot be used in a SQL injection attack. An example is as follows:
1 2 3 4 5 | $wpdb->query( $wpdb->prepare( " INSERT INTO $wpdb->posts ( post_id, post_content ) VALUES ( %d, %s)", 15, "this is un'safe" ) ); |
As with previous examples, the “%d” and “%s” function as placeholders for the sanitized data.
With those functions and a little bit of work, you should be writing WordPress database queries with the WPDB class in no time!