Migz Migz - 6 months ago 18
MySQL Question

PHP Mysql datetime search with PDO

Struggling to return results on a DateTime column. I just get null.
Been trying different ways but end up with same result.
I think the problem comes in when there is a space ?!?

Code:

$myStart=$_POST['jQueryDateStart'];
$myEnd=$_POST['jQueryDateEnd'];
$myStart = $myStart." 00:00:00";
$myEnd = $myEnd." 23:59:59";

if ($myStart>$myEnd ){
echo "<script>";
echo " alert('Start Date must less than End Date');";
echo " window.location.href='location.php';";
echo "</script>";
exit();
}
$mytable = $_SESSION["SESS_myuserid"];

$stmt = $db->prepare("SELECT * FROM ".$mytable." WHERE PTime BETWEEN ':SearchS' AND ':SearchE'");
$stmt->bindParam(':SearchS', $myStart, PDO::PARAM_STR);
$stmt->bindParam(':SearchE', $myEnd, PDO::PARAM_STR);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo $result[0]['PLong'];
echo $myStart;
echo $myEnd;
echo $mytable;
exit();


Start date data : 2016-05-22 00:00:00
End date Data : 2016-05-27 23:59:59

Example of Time in column : 2016-05-26 13:29:24

I've tried with Single ' ' around the variables as shown in statement code and without, same result .

Answer

Wrap off quotes from placeholder otherwise it create your query as WHERE PTime BETWEEN ''2016-05-22 00:00:00'' AND ''2016-05-27 23:59:59''

Just use

$stmt = $db->prepare("SELECT * FROM ".$mytable." WHERE PTime BETWEEN :SearchS AND :SearchE");
Comments