Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

shivamanhar commented Oct 26, 2015

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

@howareyoucolin

This comment has been minimized.

Copy link

howareyoucolin commented Apr 10, 2016

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

This comment has been minimized.

Copy link

antcolag commented Mar 11, 2017

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

@almokhtarbr

This comment has been minimized.

Copy link

almokhtarbr commented Mar 10, 2018

thank you (👍

@isandipanmondal

This comment has been minimized.

Copy link

isandipanmondal commented Apr 12, 2019

Thank you very much

@Parviz-Turk

This comment has been minimized.

Copy link

Parviz-Turk 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

This comment has been minimized.

Copy link

juananruiz commented Dec 12, 2019

Great work! Thanks!

@bogere

This comment has been minimized.

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
You can’t perform that action at this time.