Passing PHP variables in $wpdb query

1 Views

I’ve a table in my database named wp_q33uds_campaign. I have a query in my application as follows: 

$usr = $wpdb->get_var('SELECT user_id FROM wp_q33uds_campaign WHERE date1 = 1451546940');

Now, this query returns a user_id from the table which has the date1 column set as 1451546940.

Read More

Now, somewhere in my application, I have a variable named $dd where $dd is set as 1451546940. However, when I modify my above query as follows: 

$usr = $wpdb->get_var('SELECT user_id FROM wp_q33uds_campaign WHERE date1 = $dd);

it returns a NULL value. What seems to be wrong here? Is there no way to pass in PHP variables inside $wpdb query?

Post Views: 1

Related posts

4 comments

  1. You should use the $wpdb->prepare() method to properly escape variables to prevent syntax errors and most importantly to protect against SQL injection attacks – see Running General Queries in the WordPress documentation. You should also use the $wpdb->prefix property to ensure the proper table prefix in your queries.

    The value you see for your date is actually a Unix timestamp, you will need to convert this to a string using date();

    // convert from timestamp to date string
$dd = date( 'Y-m-d H:i:s', 1451546940 );
// your SQL statement with db table prefix and %s placeholder
$sql = "SELECT user_id FROM {$wpdb->prefix}campaign WHERE date1 = %s";
// populate vars and run query
$usr = $wpdb->get_var( $wpdb->prepare( $sql, $dd ) );

  2. Use either

    $usr = $wpdb->get_var("SELECT user_id FROM wp_q33uds_campaign WHERE date1 = '$dd'");

    or

    $usr = $wpdb->get_var('SELECT user_id FROM wp_q33uds_campaign WHERE date1 = ''.$dd.''');

  3. First, you are missing the close quote.

    Second, when you use single quotes PHP take the string inside literal, so your $dd is not used as variable is used as string.

    There are a lot of ways to fix this.

    $usr = $wpdb->get_var("SELECT user_id FROM wp_q33uds_campaign WHERE date1 = $dd");

    Or

    $usr = $wpdb->get_var('SELECT user_id FROM wp_q33uds_campaign WHERE date1 = ' . $dd);

    The best way is using the sprintf http://php.net/manual/en/function.sprintf.php

    $sql = sprintf( 'SELECT user_id FROM wp_q33uds_campaign WHERE date1=%s', $dd);
$usr = $wpdb->get_var($sql);

  4. If you want your PHP var to be evaluated, you need to use doublequotes. Otherwise you need to concat your var with your string like this

    $usr = $wpdb->get_var('SELECT user_id FROM wp_q33uds_campaign WHERE date1 = ' . $dd);

Comments are closed.