PDO query does not return data when inserting date as variable

Question

Im trying to get a hold of OOP and PDO. Did some tutorials. In the tutorial i got the query method (so thats not mine...)

but im having troubles with a pdo query

I want to select orders from the database matching a date..... de date comes from a datepicker and returns 2012-12-16 for example therefor

$dateInputQuery = date("Y-m-d", strtotime(Input::get('datepick')));
$data = $order->getAllOrders('order', 'WHERE DATE(orderdate) = DATE({$dateInputQuery})', false, false);

the strange thing is that when i replace the WHERE clause to WHERE DATE(orderdate) = \'2013-12-16\' it returns all the data but when inserting my date like above it does not....

in the db class the method looks like this

public function getAll($table, $where = NULL, $orderSort = NULL, $limit = NULL) {
    $this->query("SELECT * FROM {$table} {$where} {$orderSort} {$limit}")->error();
    return $this;
}

and query method in db class

public function query($sql, $params = array()) {
    //reset error
    $this->_error = false;
    if ($this->_query = $this->_pdo->prepare($sql)) {

        $x = 1;
        if (count($params)) {
            foreach ($params as $param) {
                $this->_query->bindValue($x,$param);
                $x++;
            }
        }

        if ($this->_query->execute()) {
            $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
            $this->_count = $this->_query->rowCount();
        } else {
            $this->_error = true;
        }
    }

    return $this;
}

why is this ?

Double check that $dateInputQuery is actually in the format you expect. — Robbert, Dec 17 '13 at 05:39

score 0 · Accepted Answer · edited May 23 '17 at 11:58

Your immediate problem is caused the fact that $dateInputQuery is unquoted. Date is a string literal and should be quoted. And even though you can easily add quotes around it you really shouldn't do this. See next point.

order is a reserved word in MySQL, therefore the table name should be put in backticks

$data = $order->getAllOrders('`order`', "WHERE DATE(orderdate) = DATE('$dateInputQuery')", false, false);
                              ^     ^                                 ^               ^

You're not leveraging parameter binding in query() function. Instead on top of it you're using query string interpolation leaving your code vulnerable to sql injections and diminishing the usage of prepared statements. When you use parameter binding you no longer need to quote parameter values.
Your sql query is not index-friendly. You shouldn't apply any functions (in your case DATE()) to the column you're searching on (orderdate). Instead you can rewrite your condition to apply necessary transformations/calculations to the arguments which are constants.
You should avoid using SELECT *. Read Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc and Why is using '*' to build a view bad?

That being said your query should look something like

$sql = "SELECT order_id, orderdate, ...
          FROM `order` 
         WHERE orderdate >= ? 
           AND orderdate <  ? + INTERVAL 1 DAY";

And you should execute it

$this->query($sql, array($dateInputQuery, $dateInputQuery));

Instead of passing whole clauses (e.g. WHERE) you should pass values

even if I add quotes it wont work (just for checking), thanks for the rest,, ill try it! I only dont understand the 2 times $sql 1 as parameter in array and 1 in query (that one is mine...) but why double ? — Reza, Dec 17 '13 at 08:18
@rZaaaa If you quote it correctly it should work. Also see updated answer. You should put the table name in back ticks because `order` is a reserved word in MySQL. Now you absolutely right there is no need to pass `$sql` second time it was just a typo. Corrected it in the answer. — peterm, Dec 17 '13 at 08:56
I made work of all your notes and its working now. I guess the parameter binding was the (main) problem... I really did put proper quotes around the date var,, but de PDO just would not take it. Only as param.. but its for the better i think,, but THANK YOU! — Reza, Dec 18 '13 at 06:59

PDO query does not return data when inserting date as variable

1 Answers1