Querying DATE in MySQL using PHP PDO

Question

I am updating my code to use PHP PDO... I came across an sql query

 $sql = "SELECT COUNT(id) AS number_of_item FROM ".$db_table_prefix."item_table 
WHERE id > 0 
    AND date_visited BETWEEN 
    CAST('$start_date' AS DATE ) 
    AND CAST('$end_date' AS DATE )";

which i replace with this

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN CAST(:start_date AS DATE )
                      AND CAST(:end_date AS DATE )";
        $this->prepare($this->sql);
        $this->bind(':id', 0);
        $this->bind(':start_date', $date_start);
        $this->bind(':end_date', $date_end);

        $this->execute();

        sizeof($this->multiple_fetch()) > 0 ? $this->result_set = $this->multiple_fetch() : $this->result_set = 404;

        return $this->result_set;

I have entries in my database that meet all the criteria but my new code is giving this as a result

array (size=1)   0 => 
    array (size=1)
      'number_of_items' => int 0

My questions, is there something i am doing wrong? Is there a better way or different way of using the

CAST(... AS DATE)

in PDO?

UPDATE

Here is what my bind method look like

function bind($placeholder, $value, $type = null){
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = \PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = \PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = \PDO::PARAM_NULL;
                    break;
                default:
                    $type = \PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($placeholder, $value, $type);
    }

My $date_start and date_end values are 2016-11-01 and 2016-11-30 respectively. I have about 101 dummy entries in my db (item_table) with date_visited as 2016-11-18

What is exactly inside $start_date and $end_date ? Also in you bind() method, are you passing them as string ? — EhsanT
– EhsanT, Commented Nov 20, 2016 at 0:13
are $start_date and $end_date DateTime objects or string? Have you tried $start_date = date('Y-m-d', strtotime($start_date))(and same for $end_date) and then pass them to the query without CAST? — EhsanT
– EhsanT, Commented Nov 20, 2016 at 6:04
@EhsanT Yes, I have done the date('Y-m-d', strtotime($start_date)) and i am still getting same result... The $start_date and $end_date are both string... — Ukor
– Ukor, Commented Nov 20, 2016 at 7:59
can you create a simple sqlFiddle ? When you query directly in mysql do you fetch records? — EhsanT
– EhsanT, Commented Nov 20, 2016 at 8:01
@EhsanT You've been really helpful... I finally figured it out, using you suggestion above. Before, i was doing the date('Y-m-d', strtotime($start_date)) thing before binding, i don't know how that affect it? but when i did this $this->bind(':start_date', date("Y-m-d", strtotime($date_start))); $this->bind(':end_date', date("Y-m-d", strtotime($date_end))); it worked. and i also remove the CAST (), so my query look like this AND date_visited BETWEEN :start_date AND :end_date please give that as an answer. Thanks alot — Ukor
– Ukor, Commented Nov 20, 2016 at 8:38

EhsanT · Accepted Answer · 2016-11-20 09:10:05Z

As I suggested in my comment you can use this code:

$date_start = date('Y-m-d', strtotime($start_date));
$date_end = date('Y-m-d', strtotime($date_end));

To convert your variable contents to a format which MySQL needs

Now when you say that you have done it before and it was not working, I assume somewhere between this conversion and binding them to the sql statement you where changing them in your code, so either you can convert them immediately before binding them or as you suggested just convert them in the bind like this:

$this->bind(':start_date', date('Y-m-d', strtotime($start_date)));

And then your query will look like this

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";

So to sum everything up, you can use one of these two ways. either of them should work, but you can use any of them you are more comfortable with:

1. Convert the variables before binding them

$date_start = date('Y-m-d', strtotime($start_date));
$date_end = date('Y-m-d', strtotime($date_end));

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";
$this->prepare($this->sql);
$this->bind(':id', 0);
$this->bind(':start_date', $date_start);
$this->bind(':end_date', $date_end);
// rest of your code

2. Convert the variables during binding

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";
$this->prepare($this->sql);
$this->bind(':id', 0);
$this->bind(':start_date', date('Y-m-d', strtotime($start_date)));
$this->bind(':end_date', date('Y-m-d', strtotime($date_end)););
// rest of your code

Collectives™ on Stack Overflow

Querying DATE in MySQL using PHP PDO

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related