wpdb

WordPress logo

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).'%') );
 
?>

Leave a Reply

Your email address will not be published. Required fields are marked *