SQL queries in WordPress are extremely simple. With pre-built functions, you just need to call them correctly — unlike other source codes that are often convoluted. Let’s take a look at how to query and add data in WordPress!
About WordPress WPDB Class
WordPress’s WPDB class uses the EzSQL library by Justin Vincent and is further developed to suit WordPress while still retaining the EzSQL style.
* Note: For security reasons, do not call class::WPDB directly. Instead, use the global $wpdb object.
* Another note: To maintain security, you must escape all data before inserting it into the database. Use the following escape functions:
- esc_html: escapes all HTML code, e.g., < becomes <
- esc_attr: escapes all form values!
- esc_sql: escapes SQL text. However, it’s better to use $wpdb->prepare instead!
- esc_textarea: escapes HTML tags!
Default Database Tables
Default tables:
$posts $postmeta $comments $commentmeta $terms $term_taxonomy $term_relationships $users $usermeta $links $options
Tables in WordPress Multisite:
$blogs $signups $site $sitemeta $sitecategories $registration_log $blog_versions
How to Call the Database
You should not call it directly. Instead, use the global variable like this:
global $wpdb;
Everything Should Be Secure
According to importance, Jam will put the critical functions at the top for easy reference. To avoid SQL Injection attacks, always use the prepare function, like the example below:
global $wpdb;
$wpdb->prepare('select * from %s where user_id = %d', $wbdb->users, 5);
This function works similarly to PHP’s sprintf() function!
%f: Float – e.g., 1.234
%d: Decimal – e.g., 1220
%s: String – e.g., “Hello everyone, I am a string”
If the input doesn’t match the first condition, it will be typecasted. For example:
select * from %s <!-- string --> where user_id = %d <!-- integer -->, %s <!-- string -->, %d <!-- typecasted to int -->
When using prepare, all variables and user inputs are filtered for security. Note: if no user input is included in the query, you don’t need prepare. Otherwise, an error might occur, like:
$wpdb->prepare("select * from {$wbdb->users}");
If the query is completely safe, just use $wpdb->query directly instead of prepare!
* Important: A famous saying in programming is “trust no one, not even yourself.” If you’re writing plugins or themes, prioritize security. If unsure, don’t do it!
General Query
You can run any basic SQL statement with $wpdb->query, such as SELECT, INSERT, DELETE, UPDATE, like this:
$wpdb->query(
$wpdb->prepare(
"
DELETE FROM $wpdb->postmeta
WHERE post_id = %d
AND meta_key = %s
",
13, 'gargle'
)
);
Or like this:
$wpdb->query( " UPDATE $wpdb->posts SET post_parent = 7 WHERE ID = 15 AND post_status = 'static' " );
* Tip: For system-run queries with no user input, use direct query. These typically return true/false or affected row ID.
* Example:
Count users on the site:
global $wpdb;
echo inval( $wpdb->query("select count(*) from $wpdb->users") );
Get a Single Value from a Table
To get a single value or count, use the get_var function in WordPress. Example:
<?php $wpdb->get_var( 'query', column_offset, row_offset ); ?>
Example to count users:
$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users" );
Or sum the post meta values, e.g., price:
$meta_key = '_price'; $allmiles = $wpdb->get_var( $wpdb->prepare( " SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %s ", $meta_key ) );
Get a Single Row
To get all values of a row, use get_row. Example:
<?php $wpdb->get_row('query'); ?>
This returns an object by default. Example:
$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );
Get all values for a Post ID:
$postdata = $wpdb->get_row( "SELECT * FROM $wpdb->posts WHERE post_id = 10" );
And get the Post_content:
echo $content = $postdata->post_content;
Get Multiple Rows
Use get_var or get_row for single results, but use get_results for multiple rows. Example:
$wpdb->get_results( 'query');
This returns an array of objects. To get all posts from User 1:
$user_id = 1;
$data = $wpdb->get_results( $wpdb->prepare(
'select * from %s where user_id = %d LIMIT 99',
$wpdb->posts, $user_id
));
foreach ( $data as $k ) {
echo $k->post_title;
}
In most cases, use get_var or get_results. get_results is common in WordPress loops!
Insert Values into Table
To insert data, use the insert function. Avoid using $wpdb->query for inserts unless it’s very safe. Example:
$wpdb->insert( $table, $data, $format );
To insert into the posts table:
$wpdb->insert( $wpdb->posts,
array('ID' => $wpdb->insert_id, 'post_content' => $content),
array('%d', '%s')
);
Returns 1 on success, False or 0 on failure.
Replace a Field in the Table
To replace a field, use replace like insert:
$wpdb->replace( $table, $data, $format )
Example:
$wpdb->replace(
'table',
array(
'indexed_id' => 1,
'column1' => 'value1',
'column2' => 123
),
array(
'%d',
'%s',
'%d'
)
);
Returns true or false depending on success.
Update Data
To update a row, use the update function:
$wpdb->update( $table, $data, $where, $format = null, $where_format = null );Example: Update the Post_title for Post ID 10:
$wpdb->update( $wpdb->posts,
array('post_title' => 'Hello everyone'),
array('ID' => 10),
);
Delete Data
To delete a row or field, use the delete function:
$wpdb->delete( $table, $where, $where_format = null );
Example: Delete a post with ID = 2:
$wpdb->delete( $wpdb->posts, array('ID' => 2));
Returns the deleted ID or false;
Prevent Database Hacking
You should use sprintf or prepare to reduce SQL injection risk. You can also use WordPress’s built-in functions. For example:
Using sprintf:
$wpdb->query( sprinf('select * from %s where user_id = %d ', $wpdb->users, 12) );
Using built-in functions:
- wp_delete_post( postID );
- wp_delete_user( $userID );
- delete_option( $name );
- wp_trash_post( $postID );
Etc. You can refer to the Codex on WordPress.org to find the correct functions and avoid writing long, unsafe code!
Good luck!