Can't run database query

Question

I am writing a function to query some information from a custom table in the database. It seems that I am unable to query the database on the field I need.

function ch_details_from_id($id) {

    global $wpdb;

    $details = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix . "ch_guests WHERE key = '" . $id . "'", ARRAY_A);

    return $details;
}

$id is currently '001'. If I search on any other field in the table, I get the expected results, but on this field nothing is returned.

If I run exactly the same query in phpMyAdmin, it works fine.

Answer 1

Here issue with the column name. You are using column key in the query which is the default keyword/index in mysql.

For resolving this kind of issue just use the "Grave accent(`)' symbol in the query for the column name, No need to change the column name. So in your case the right query is

$details = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix . "ch_guests WHERE `key` = '" . $id . "'", ARRAY_A);

Run the above query, This is working for me, so i believe will work for you as well.

Answer 2

You should use $wpdb->prepare for queries, as it does all the sanitizing and escaping for you. Also, key is reserved as a field name in MySQL:

function ch_details_from_id($id) {

    global $wpdb;
    // or try using " . $wpdb->prefix . "ch_guests
    $query = "SELECT * FROM $wpdb->ch_guests WHERE guest = %d";
    $details = $wpdb->get_results( $wpdb->prepare( $query, $id ) );

    return $details;

}

To debug, you can output the last query $wpdb has done.

Answer 3

The field name is a reserved name. Changing from key to id has solved the issue.