Working with database in WordPress:
Get results
1 2 3 4 5 6 7 8 9 10 11 12 13 | <?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
1 2 3 4 5 6 7 8 9 | <?php global $wpdb ; $row = $wpdb ->get_row( "select * from $wpdb->links where link_id = 25" ); echo $row ->link_id; // prints "25" ?> |
Insert row
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <?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
1 2 3 4 5 6 7 | <?php global $wpdb ; $wpdb ->get_var( $sql ); ?> |
Get col
1 2 3 4 5 6 7 | <?php global $wpdb ; $wpdb ->get_col( $sql ); ?> |
Update row
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <?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 ). '%' ) ); ?> |