The WordPress database class is quite extensive and provides a range of methods to work effectively with the database and thereby use the WordPress standard.
You can find the class in /wp-includes/wp-db.php where the individual methods are documented.
I show the most important ones and give some small examples. It is important to work with these opportunities to ensure the safety of Plugins.
For the following four methods I created some syntax examples.
insert($table, $data, $format)— insert a row into a table via arrays.update($table, $data, $where, $format, $where_format)— update a row in a table via arrays.get_var($query, $x, $y)— retrieve a single variable from the database.query($query)— perform a MySQL database query with current connectionget_results($query, $output)— retrieve SQL result set from database… one or more rows.escape($data)— Escapes content for insertion into the database using addslashes(), for security
Also interesting are the methods below.
set_prefix($prefix)— used to set table prefix for WordPress tables, can be used to override prefix at any timeprepare($query)— safely prepares an SQL query for execution with sprintf()-like syntax.get_row($query, $output, $y)— retrieve a single row from the database.get_col($query, $x)— retrieve a single column from the database in array format.
/**
* insert
*/
$wpdb->insert( $wpdb->posts, array( 'post_title' => $mytitle ) );
$wpdb->insert( $wpdb->options, array(
'option_name',
'new_option_key',
'option_value' => 'New Option Value',
'autoload' => 'yes' )
);
/**
* update
*/
$wpdb->update( $wpdb->posts, array( 'post_title' => $mytitle ),
array( 'ID' => $myid )
);
$wpdb->update( $wpdb->options,
array( 'option_value' => 'New Option Value' ),
array( 'option_name' => 'new_option_value' )
);
/**
* get_var
*/
$post_id = $wpdb->get_var(
$wpdb->prepare( "SELECT post_id FROM
$wpdb->postmeta WHERE
post_id = %d AND
meta_key = 'enclosure' AND
meta_value LIKE (%s)", $post_ID, $url . '&' )
);
$content = $wpdb->get_var(
$wpdb->prepare("SELECT post_content FROM " .
"$wpdb->posts WHERE " .
"post_title = %s AND " .
"ID = %d", $title, $id )
);
/**
* query
*/
$wpdb->query( "DELETE FROM $wpdb->options WHERE option_name = '$name'" );
$wpdb->query( "UPDATE $wpdb->posts SET post_title = '$mytitle' WHERE ID = $myid" );
/**
* query and escape
*/
$mytitle = $wpdb->escape( $mytitle );
$myid = absint( $myid );
$wpdb->query( "UPDATE $wpdb->posts SET post_title = '$mytitle' WHERE ID = $myid" );
/**
* get_results
*/
$type = $wpdb->get_results( "SELECT post_type FROM " .
"$wpdb->posts WHERE ID=$id" );
Comments
13 responses to “WordPress Database Functions”
[…] can find a nice introduction and examples at WP Engineer. Here’s a sample of how you would go about changing the title of a post with a certain […]
Very good post. I’ve searched documents about $wpdb class, but there’s no “full” document. This post helps me very much. Thank you.
I second that, this is the best resource with examples that I could find online. More useful than the wordpress codex page. thanks!
Thanks for posting this. I’ve been hand-coding all of my plugin SQL.
Googling $wpdb brings up few resources relating to this rather useful set of wordpress functions. While the wordpress codex does provide some extensive coverage it isn’t laid out as simple as this for those out there who may be new to php or those who need a quick and simple reference list. Nice one.
Thanks for providing these functions. I am very interested in using these types of functions to add more functionality to my site. Where can I learn more information about how to put together such functions and where to use the syntax. For instance your use of get_var at the very beginning has an x and a x but it says get a single variable. What is the x and y for? And where can I learn about your use of absint,, prepare, escape and SET? Are these mysql query techniques or WordPress stuff? I sure would like to find a full fledged tutorial breaking all this stuff down so I can actually learn something other than how to copy and paste.
Just wanted to share something…
Let’s suppose you’ve created brand new table for the purpose of a plugin and want to insert some data. The following will not work:
$wpdb->insert( $wpdb->my_new_table, array( 'post_title' => $mytitle ) );
…you have to do it this way:
$wpdb->insert( $wpdb->prefix . 'my_new_table', array( 'post_title' => $mytitle ) );
Hopefully people will find it useful.
I insert a post with this function
$my_post_name = "test3";// check if title already exists in wpdb
global $wpdb;
if($wpdb->get_row("SELECT post_title FROM wp_posts WHERE post_title = '" . $my_post_name . "'", 'ARRAY_A')) {
echo $my_post_name.' | already exists';}
else
{
echo 'generating new post: '. $my_post_name.' ;';
// Create post object - in this case just containing one image
$my_post = array();
$my_post['post_title'] = $my_post_name;
$my_post['post_content'] = '';
$my_post['post_status'] = 'draft'; // or 'publish'
$my_post['post_author'] = 1;
$my_post['post_category'] = array(228);
// Insert the post into the database
wp_insert_post( $my_post );
sleep(1); //wait 1 second between posts
}
but my post is insert one time, and two time, nd three time …
what can i do ?
i suppose that this line is not avalable :
"SELECT post_title FROM wp_posts WHERE post_title = '" . $my_post_name . "'", 'ARRAY_A')what do you think about this ?
thx
I guess your dB query is not properly written coz it always returns false…
Try this:
if( $wpdb->get_row( "SELECT post_title FROM $wpdb->posts WHERE post_title = $my_post_name" ) ) { ... }
thx dameer ! but that doesn’t work…
dameer : in fact it ws a problem of syntax. It works if the syntax is :
if( $wpdb->get_row(“SELECT post_title FROM $wpdb->posts WHERE post_title = ‘$my_post_name’;” ) ) {
Hello,
I have WP 3.0 with MS enabled and I am pull the sites information from my subdomains via $wpdb.
global $wpdb;$blogs = $wpdb->get_results( $wpdb->prepare("SELECT blog_id, domain, path FROM $wpdb->blogs WHERE site_id = %d AND public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0' ORDER BY registered DESC", $wpdb->siteid), ARRAY_A );
This gives me an array, e.g.
Array ( [0] => Array ( [blog_id] => 10 [domain] => sample1.smithsaga.com [path] => / ) [1] ...)So, I want to add a key [logo] and store it permanently.
So later in my
foreach($blogs as $blog)where I have an input form for $blog[‘img’], and then I have$wpdb->insert( $wpdb->blogs, array( 'img' => $blog['img'] ), array( 'blog_id' => $blog['blog_id'] ) );but I don’t think this is right. I have also tried $wpdb->update. So what am I missing or doing wrong? For the full snipit, see http://pastebin.com/U61ME2AUHow can we add a database field where the search query will fetch the search result?
THanks a lot for sharing this.