wpdb

Working with database in WordPress:

Get results

<?php

global $wpdb;

$results = $wpdb->get_results( "select * from $wpdb->posts where post_type='post' and post_status = 'publish' ORDER BY post_date DESC " ); // $wpdb->prepare()

foreach ($results as $result) {

	echo $result->post_title;

}

?>

Get row

<?php

global $wpdb;

$row = $wpdb->get_row("select * from $wpdb->links where link_id = 25");

echo $row->link_id; // prints "25"

?>

Insert row

<?php

global $wpdb;

$wpdb->insert(

	$wpdb->posts,

	array(

		'column1' => 'value1', // string

		'column2' => 123, // decimal

		'column3' => 12.5 // float

	),

	array( '%s', '%d', '%f' ) // format (optional) (string type by default)

);

$insert_id = $wpdb->insert_id; // the value of AUTO_INCREMENT column after insert

?>

Get var

<?php

global $wpdb;

$wpdb->get_var($sql);

?>

Get col

<?php

global $wpdb;

$wpdb->get_col($sql);

?>

Update row

<?php

global $wpdb;

$wpdb->update(

	$wpdb->posts,

	array(

		'column1' => 'value1', // string

		'column2' => 22 // decimal

	),

	array( 'ID' => 15 ), // where

	array( '%s', '%d' ), // format (optional)

	array( '%d' ) // where_format (optional)

);

?>

Run any query

<?php

global $wpdb;

$wpdb->query( $wpdb->prepare( "delete from tablename where post_id=%d and meta_key=%s", $number, $string ) );





// escape bad sql

$age = 14;  

$firstname = "Robert'; DROP TABLE Students;";  

$sql = $wpdb->prepare('SELECT * WHERE age=%d AND firstname = %s;',array($age,$firstname));  

$results = $wpdb->get_results($sql); 



// escape 'like' sql

$age=14;  

$firstname = "Robert'; DROP TABLE Students;";  

SELECT * WHERE age=$age AND (firstname LIKE '%$firstname%'); 

$query = $wpdb->prepare('SELECT * WHERE age=%d AND (firstname LIKE %s);', array($age, '%'.like_escape($firstname).'%') );



?>