Using $wpdb->get_results with Select and a comparing a column with a variable

  • Resolved admiralchip

    (@admiralchip)


    9 years, 8 months ago

    Hello,

    I’m developing a custom plugin and I’ve got to retrieve some numbers from a table where the value in the expiry_date column is greater than value that I’ve stored in a variable called $scheduleddate (That is, I need to retrieve numbers that haven’t expired as at the $scheduleddate). Here is what I’ve tried:

    $scheduled_date = strtotime($year."-".$month."-".$day);
$scheduleddate = date('Y-m-d',$scheduled_date);

  global $wpdb;
  $table_name = $wpdb->prefix . "subscribers";
  $result = $wpdb->get_results("SELECT number FROM " . $table_name .
            " WHERE list_id=" . $list . " AND expiry_date > " .  $scheduleddate);

    When I tested it, I still ended up with the numbers that had already expired (I echoed the numbers so that I could see whether they were the ones I needed). I need to retrieve the numbers that are NOT expired as at the time in the $scheduleddate. What’s the correct way to write the query? The expiry_date column is in the date format in the database table. (And if it’s important, the $year, $month, and $day variables are gotten from $_POST and I need them that way.)

    Am I meant to use $wpdb->prepare as well?

    Thanks in advance!

Viewing 7 replies - 1 through 7 (of 7 total)

  • Firstly since you are getting $year, $month, $day from $_POST you just MUST use prepare, if you don’t you are just begging for some smart vandal to hack your database and site.

    Have you tried an echo on:
    $scheduled_date and $scheduleddate ?
    I think you will be surprised.

    And also echo your query string. For this reason I always stricture my code along the lines of:

    global $wpdb;
  $table_name = $wpdb->prefix . 'subscribers';
  $query = 'SELECT number FROM ' . $table_name .
    ' WHERE list_id=' . $list . ' AND expiry_date > ' .  $scheduleddate;
// echo 'Fetch still live qry: ' . $query . "\n";
  $result = $wpdb->get_results( $query );
// print_r( $result );

    Note also that I only use double quotes when I want the text to be scanned. And I leave the debug code just commented out, there are all sorts of reasons why the may be needed again later.

    Thread Starter admiralchip

    (@admiralchip)

    Thanks for your reply!

    I’ve actually tried an echo on $scheduleddate and it echoed the date I entered.

    I’ll try your suggestions.

    Thread Starter admiralchip

    (@admiralchip)

    My code looks like this now:

    global $wpdb;
			$table_name = $wpdb->prefix . "subscribers";
			$result = $wpdb->get_results($wpdb->prepare('SELECT number FROM ' . $table_name . ' WHERE list_id= %d AND expiry_date > %s', $list_id, $scheduleddate));

    I still get the same problem.

    Also I’ve tried your code @rossmitchell and I echoed my query. The query string looks correct.

    The query string looks correct.

    Then copy the query and paste it into phpmyadmin as an SQL query, what happens ?

    Thread Starter admiralchip

    (@admiralchip)

    Then copy the query and paste it into phpmyadmin as an SQL query, what happens ?

    @rossmitchell I just tried it and it brought out the number that has already expired. I then put the value from $scheduleddate in single quotes and then it started working as expected. The query now looks like this:

    $table_name = $wpdb->prefix . 'nbsms_subscribers';
  $query = 'SELECT number FROM ' . $table_name .
    ' WHERE list_id=' . $list . ' AND expiry_date > ' .  "'".$scheduleddate."'";
// echo 'Fetch still live qry: ' . $query . "\n";
  $result = $wpdb->get_results( $query );
// print_r( $result );

    Thanks a lot!

    Is it now appropriate to mark this thread as resolved ?

    Thread Starter admiralchip

    (@admiralchip)

    Is it now appropriate to mark this thread as resolved ?

    Yep! Thanks! ??

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Using $wpdb->get_results with Select and a comparing a column with a variable’ is closed to new replies.