wpdb

WordPress logo

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

Leave a Comment