MySQLi to PDO issue with prepared statements

Question

Trying to move to PDO and having a helluva time. Here's my code, which isn't working quite right:

$db = new PDO('mysql:host=localhost;dbname=pp_production;charset=utf8', 'mr', 'jones');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$report_date = "DATE(time_track.punch_in) = CURRENT_DATE ORDER BY time_track.punch_in DESC";

$query_clock_history = $db->prepare("SELECT time_track.id, time_track.job_id, first_name, activity, time_track.comments, time_track.punch_in, time_track.punch_out, TIMESTAMPDIFF(MINUTE, time_track.punch_in, time_track.punch_out) AS time_spent, customers.customer_name, typesetting.description, typesetting.job_ticket FROM typesetting 
JOIN time_track ON typesetting.id = time_track.job_id 
JOIN employees ON employees.id = time_track.employee_id 
JOIN activities ON activities.id = time_track.activity_id 
JOIN customers ON customers.id = typesetting.customers_id 
WHERE :report_date");
$query_clock_history->bindValue(':report_date', $report_date, PDO::PARAM_STR);
$query_clock_history->execute();

So, that returns no results. If I replace :report_date with the actual contents of the $report_date variable, it works. I've been banging my head against this for a while now and just can't get it to work. Any help greatly appreciated. This is running on MAMP, if that makes a difference.

Tried some things that didn't work

$report_date = "CURRENT_DATE";
...
WHERE DATE(time_track.punch_in) = :report_date ORDER BY time_track.punch_in DESC");

Still nothing. It seems like a simple example but I don't know why it won't work.

[PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858) — Your Common Sense, May 16 '13 at 06:02
I have a switch statement to change out the WHERE part of the query depending on what has been submitted by the user. — Matt, May 16 '13 at 06:23
Well, if there are no variables involved just concatenate this variable part to your query — Your Common Sense, May 16 '13 at 06:25

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You can't bind an arbitrary query part but scalar value only (i.e. string and number), neither in PDO or mysqli

Nevertheless, you don't need no binding for this query at all

$sql = "SELECT time_track.id, time_track.job_id, first_name, activity, time_track.comments, time_track.punch_in, time_track.punch_out, TIMESTAMPDIFF(MINUTE, time_track.punch_in, time_track.punch_out) AS time_spent, customers.customer_name, typesetting.description, typesetting.job_ticket FROM typesetting 
JOIN time_track ON typesetting.id = time_track.job_id 
JOIN employees ON employees.id = time_track.employee_id 
JOIN activities ON activities.id = time_track.activity_id 
JOIN customers ON customers.id = typesetting.customers_id 
WHERE DATE(time_track.punch_in) = CURRENT_DATE ORDER BY time_track.punch_in DESC"

$stmt = $db->prepare($sql);
$stmt->execute();

that's all

Since there's no binding, there's also no need for prepare and execute; just query. — Ja͢ck, May 16 '13 at 06:36

score 0 · Answer 2 · answered May 16 '13 at 06:11

0

Your where must have a condition, i.e

WHERE date = :report_date

from php website

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

Therefore, your :report_date param needs to have a condition against a colomn in from your table in your query statement

answered May 16 '13 at 06:11

Billy2mates

49
5

I just tried doing what you suggested but that doesn't seem to work either. Maybe I'm misunderstanding: $report_date = "CURRENT_DATE ORDER BY time_track.punch_in DESC"; ... WHERE DATE(time_track.punch_in) = :report_date"); – Matt May 16 '13 at 06:18
What is the type of time_track.punch_in, does it match :report_date, may be the formats/type are different – Billy2mates May 16 '13 at 06:32

MySQLi to PDO issue with prepared statements

2 Answers2

You can't bind an arbitrary query part but scalar value only (i.e. string and number), neither in PDO or mysqli