输入（datetime_local）字段中的MYSQL查询

Question

I've got a problem with mysql query:

$req=mysql_query("
  SELECT Device_Type, Device_Number, sum(Ok) as Ok, sum(Rejected) as Rejected, sum(QC_Rejected) as QC_Rejected
  FROM `Iteration_TotalStats`
  where Line_Id = $line and Device_Number IN ($dev) and time > '$datest' and time < '$daten'
");

variables into "for" loop:

• $datest=$_POST['date_start'];
• $daten=$_POST['date_end '];

all works fine except time parameters with variable ($dates and $datee). Inputs here:

<input type="datetime-local" name="date_start" value="2016-04-19T09:00"></input>
<input type="datetime-local" name="date_end" value="2016-04-19T21:00"></input>

but when i type this date and time format direct in the mysql query like this:

time > '2016-04-19T09:00' and time < '2016-04-19T21:00'

it works fine. I have no idea why it doesn't work with variable. Please help.

Answer 1

Just to put you on the richt track, and to show that PDO isn't that hard once you get used to it, I went ahead and converted your code to PDO:

$qry = $db->prepare(
    'SELECT '
        . 'Device_Type, '
        . 'Device_Number, '
        . 'sum(Ok) as Ok, '
        . 'sum(Rejected) as Rejected, '
        . 'sum(QC_Rejected) as QC_Rejected '
    . 'FROM `Iteration_TotalStats` '
    . 'WHERE Line_Id = :line '
        . 'AND Device_Number IN ($dev) '
        . 'AND time > :start '
        . 'AND time < :end'
);

$qry->bindParam(':line', $line);
$qry->bindParam(':start', $_POST['date_start']);
$qry->bindParam(':end', $_POST['date_end']);

$result = $qry->execute();

You'll have to instantiate that $db variable first obviously, but that is just a PDO instance, which should be clear from the manual: http://php.net/manual/en/pdo.construct.php

Let me know if this solves your problem. And do feel free to ask if anything is unclear.