0

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.

  • try running "SELECT * FROM " . $wpdb->prefix . "ch_guests WHERE key = '" . $id . "'" query in your phpmyadmin, if possible, that will give you clear idea about the problem. – Prafulla Kumar Sahu May 10 '16 at 12:22
  • Yeah, it works fine in phpMyAdmin. – Badger May 10 '16 at 12:23
  • how you did it, by using echo $details ?? – Prafulla Kumar Sahu May 10 '16 at 12:26
  • and enable the debug that will give you clear message about error. – Prafulla Kumar Sahu May 10 '16 at 12:28
  • I'm just var_dumping the details. – Badger May 10 '16 at 12:31
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.

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.

  • Do not you think "key" is reserved word and we should use something else ?? – Prafulla Kumar Sahu May 10 '16 at 12:53
  • Thanks, the issue was the choice of field name, but I have used details = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM " . $wpdb->prefix . "ch_guests WHERE guest = %s", $id ), ARRAY_A); now. – Badger May 10 '16 at 12:54
  • Yes, that is definitely the issue as I had already tried $wpd->prepare before. – Badger May 10 '16 at 12:55
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.