Valid SQL query not returning results

Question

I am trying to retrieve some basic information from the Wordpress database for a theme. However, when the query runs it returns no data. If I run the query on Phpmyadmin, it works fine. Note, wp_places_locator is a table used by a third party plugin.

function get_info($postid) {

global $wpdb;

$info = $wpdb->get_results($wpdb->prepare("SELECT post_id, address, phone, email FROM $wpdb->places_locator WHERE post_id = '$postid'"));

print_r($info);

}

Thanks!

Answer 1

Here you've did something wrong with $wpdb->table_name. It will only return the default WordPress table names. Not custom table names. And also when you call $wpdb->prepare then pass the parameter as array. So your function will be like below-

function get_info($postid) {

    global $wpdb;
    // Here I assumed that your table name is '{$wpdb->prefix}places_locator'
    $sql = $wpdb->prepare("SELECT post_id, address, phone, email 
                            FROM {$wpdb->prefix}places_locator 
                            WHERE post_id = '%d'", 
                            array( $postid )
                        );

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

    return $info;

}

Now if you do print_r on get_info() with parameter post ID then it will return you value.

Use like below-

print_r(get_info(1)); // Here I assumed your post ID is 1

Answer 2

You shouldn't place variables directly on your SQL statement, as it is not safe

To use WPDB::prepare you need to put unquoted placeholders (like %s for strings or %d for integers) on your SQL and then add the variables in the right order (to match the placeholders) after the first parameter (the SQL statement).

Here are the examples from the WordPress developer reference I linked above:

$wpdb->prepare( "SELECT * FROM `table` WHERE `column` = %s AND `field` = %d", 'foo', 1337 );
$wpdb->prepare( "SELECT DATE_FORMAT(`field`, '%%c') FROM `table` WHERE `column` = %s", 'foo' );

To fix your code something like this should work:

$info = $wpdb->get_results($wpdb->prepare("SELECT post_id, address, phone, email FROM $wpdb->places_locator WHERE post_id = %d", $postid));