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

Answer 1

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