CODEWITHSUNDEEP
mysqlwordpress
tatemz
tatemz

Reputation: 113

Wordpress database query ($wpdb) errors when using variables

I have a custom WordPress table on a WordPress MultiSite called "ihn_2_frm_item_metas" used for form entries.

Screenshot of database table is here: https://www.dropbox.com/s/ex54qxxohkexg5t/mysql.png

Right now, there are two entries. Each entry is given its own unique "item_id" (in this case, Bob Marley's entry has the id of 10 and Molly Dolly's entry has the id of 9). These entries also correspond to actual WordPress users (their username is their email address and their password(s) is "test"). The entry id is not the same number as their WordPress User Id.

I have logged in as "[email protected]" using the password "test."

On the home page of the blog, I am wanting to display all of items in the "meta_value" column ONLY for the currently logged in user.

I want to do this in two steps:

  1. Find the email address of the currently logged in user ("[email protected]").
  2. Find the "item_id" of that user's form entry (10) that is on the same row of the matching email address.

Here is the code I have come up with:

    global $wpdb;
    global $current_user;
    get_currentuserinfo();
    $email = $current_user->user_email;

    $id = $wpdb->get_var( " SELECT item_id FROM ihn_2_frm_item_metas WHERE meta_value = $email " );
    $client_meta = $wpdb->get_results(" SELECT * FROM ihn_2_frm_item_metas WHERE item_id = $id " );

    echo '<p> $email is a ' . gettype($email) . '</p>';
    echo '<p> $id is a ' . gettype($id) . '</p>';
    echo '<p>The current logged in user is ' . $email . '.</p>';
    echo '<p>The current form entry id is ' . $id . '.</p>';
    echo "<ul>";

    foreach ($client_meta as $value) {
        echo '<li>' . $value->meta_value . '</li>';
    }

    echo "</ul>";

I have two gettype() statements simply in order to confirm that both of my varables, $email and $id are strings.

Here is the output of my code:

$email is a string.

$id is a NULL.

The current logged in user is [email protected].

The current form entry id is .

Note that $id is NULL. What confuses me is that when I change the $id variable to this (replacing the variable $email with '[email protected]'):

$id = $wpdb->get_var( " SELECT item_id FROM ihn_2_frm_item_metas WHERE meta_value = '[email protected]' " );

My output is exactly what I need:

$email is a string.

$id is a string.

The current logged in user is [email protected].

The current form entry id is 10.

Where have I gone wrong?

Upvotes: 2

Views: 2996

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

You have omitted single quotes on $email:

$id = $wpdb->get_var( " SELECT item_id FROM ihn_2_frm_item_metas WHERE meta_value = '$email' " );
//---------------------------------------------------------------------------------^^^^^^^^

This results in a syntax error in the query. I am not familiar with how WordPress handles those errors, but it is returning NULL on the unsuccessful query.

If you don't need to actually know the value of item_id other than to use it in the subsequent query, I suppose you could combine these into a single statement using an IN() subquery:

$client_meta = $wpdb->get_results("
  SELECT * FROM ihn_2_frm_item_metas WHERE item_id IN (
    SELECT item_id FROM ihn_2_frm_item_metas WHERE meta_value = '$email'
  )
");

Upvotes: 2

Related Questions