Skip to content

Instantly share code, notes, and snippets.

@benbalter
Created January 13, 2012 18:41
Show Gist options
  • Save benbalter/1607991 to your computer and use it in GitHub Desktop.
Save benbalter/1607991 to your computer and use it in GitHub Desktop.
WordPress DB Tutorial
<?php
//absolute path to wp-load.php, or relative to this script
//e.g., ../wp-core/wp-load.php
include( 'trunk/wp-load.php' );
//grab the WPDB database object, using WP's database
//more info: http://codex.wordpress.org/Class_Reference/wpdb
global $wpdb;
//make a new DB object using a different database
//$mydb = new wpdb('username','password','database','localhost');
//basic functionality
//run any query
$wpdb->query( "SELECT * FROM wp_posts" );
//run a query and get the results as an associative array
$wpdb->get_results( "SELECT * FROM wp_posts" );
//get a single variable
$wpdb->get_var( "SELECT post_title FROM wp_posts WHERE ID = 1" );
//get a row as an assoc. array
$wpdb->get_row( "SELECT * FROM wp_posts WHERE ID = 1" );
//get an entire column
$wpdb->get_col( "SELECT post_title FROM wp_posts" );
//insert data into a table… sql protection?
$wpdb->insert( 'wp_posts', array( 'post_title' => 'test', 'ID' => 5 ), array( '%s', '%d') );
//update an existing row
$wpdb->update( 'wp_posts', array( 'post_title' => 'test2'), array( 'ID' => 5 ), array( '%s' ) );
//escaping queries
$wpdb->query( $wpdb->prepare( "UPDATE INTO wp_posts set post_title = %s WHERE ID = %d", 'test2', 5 ) );
//two steps to insert a post…
//define the post… all field optional
$post = array(
'post_title' => 'test',
'post_type' => 'station',
'post_status' => 'publish',
'post_author' => 'greg',
);
//insert
$id = wp_insert_post( $post );
//store key/value pair
update_post_meta( $id, 'expiration', '201101010' );
//retrieve key/value pair
$meta = get_post_meta( $id, 'expiration', true );
//search for posts by key/value pair
$posts = get_posts( 'expiration=20110110' );
//associate taxonomy terms with a post
wp_set_post_terms( $id, array( 'red', 'blue', 'green'), 'colors' );
//query posts by taxonomy term
$posts = get_posts( 'color=red' );
//wizards for creating taxonomy / post types
//will output a plugin that you just drop into /wp-content/plugins and activate
//http://themergency.com/generators/wordpress-custom-taxonomy/
//http://themergency.com/generators/wordpress-custom-post-types/
//Cache
//store a value in cache
wp_cache_set( 'unique_key', $data );
//retrieve value from cache
$data = wp_cache_get( 'unqiue_key' );
/* additional things to do
1) Install W3 Total Cache to get DB and object caching
2) Use the front end / admin UI to browse / sort data
*/
@shivamanhar
Copy link

include_once('../../../wp-includes/wp-db.php');
include_once('../../../wp-load.php' );

@howareyoucolin
Copy link

thank you, it's very helpful.
I'm just wondering if update would still work if the row does not already exist. Would it insert a row automatically or it would throw an error or warning.

-- 纽约牙医

@antcolag
Copy link

thanks for the guide
and for get the last inserted id by $wpdb->insert()
$lastid = $wpdb->insert_id;
:)

@almokhtarbr
Copy link

thank you (:+1:

@isandipanmondal
Copy link

Thank you very much

@Parviz-Elite
Copy link

Parviz-Elite commented May 9, 2019

is there any way to get results by {array query} like update and delete? i think it's secure and easy to use.
example : $wpdb->get_results("products", ['order' => 1, 'user' => 2]);

@juananruiz
Copy link

Great work! Thanks!

@bogere
Copy link

bogere commented Jan 23, 2020

Thank you for the gist.
For the update part using prepare function, just update below.

escaping queries
$wpdb->query( $wpdb->prepare( "UPDATE INTO wp_posts set post_title = %s WHERE ID = %d", 'test2', 5 ) );

to this please..

 $wpdb->query( $wpdb->prepare( "UPDATE  wp_posts SET post_title = %s WHERE ID = %d", 'test2', 5 ) );

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment