WordPress DB Tutorial

wp-db.tutorial.php

<?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 ) );

/* additional things to do
1) install W3 Total Cache to get DB and object caching
2) wp_cache_set and wp_cache_get
*/

$post = array( 
		'post_title' => 'test',
		'post_type' => 'station',
		'post_status' => 'publish',
		'post_author' => 'greg',
);

$id = wp_insert_post( $post );

update_post_meta( $id, 'expiration', '201101010' );

get_post_meta( $id, 'expiration', true );

get_posts( 'expiration=20110110' );

wp_set_post_terms( $id, array( 'red', 'blue', 'green'), 'colors' );

get_posts( 'color=red' );

//http://themergency.com/generators/wordpress-custom-taxonomy/
//http://themergency.com/generators/wordpress-custom-post-types/