Hi all!
I have been having an issue with selecting rows of data between certain date ranges. I am building an application around an already existing database so changes to the database aren't practical.
To complicate things even further I have started using PDO to create more secure SQL queries (or so my friend tells me) I am not entirely familiar with how PDO works although I have a reasonable idea at this stage.
The database in question is an MS SQL 2005 Standard Edition DB running on a Windows Server 2003 machine.
Now, hopefully you are all still with me so far! Put your concentrating hats on because this is where it becomes confusing.
Originally I begun writing this application without using PDO. Now, the code in question selects rows of data based on a unique code (MonCode) between date ranges that can be set by the user on a form. My original code looked like this:
$sqlSelectHistory = mssql_query("SELECT MonCode, DtTm, Event, OperatorName, Comment
FROM tblHistory
WHERE MonCode LIKE '{$searchMon}'
AND DtTm between '{$fromDate['mday']}/{$fromDate['mon']}/{$fromDate['year']}'
AND '{$toDate['mday']}/{$toDate['mon']}/{$toDate['year']}'
ORDER BY DtTm ".$_POST['listSort']."");
At the end of the page I have a while loop which outputs all the data into a table, this code works fine.
The issue arose when I had the query rewritten using PDO, the code now looks like this:
$sortorder = (isset($_POST['listSort']) && $_POST['listSort'] == 'ASC') ? 'ASC' : 'DESC';
$sqlSelectHistory = $db_hist->prepare('SELECT MonCode, DtTm, Event, OperatorName, Comment
FROM tblHistory
WHERE MonCode LIKE :search
AND DtTm BETWEEN :fromDate AND :toDate
ORDER BY DtTm ' . $sortorder . '');
$fromDate = $fromDate['mday'] . '/' . $fromDate['mon'] . '/' . $fromDate['year'];
$toDate = $toDate['mday'] . '/' . $toDate['mon'] . '/' . $toDate['year'];
$sqlSelectHistory->bindValue(':search', $searchMon . '%');
$sqlSelectHistory->bindValue(':fromDate', $fromDate);
$sqlSelectHistory->bindValue(':toDate', $toDate);
if(!$sqlSelectHistory->execute());
$entries = $sqlSelectHistory->fetchAll(PDO::FETCH_ASSOC);
When this code is run it returns 0 rows. I have tried changing the date format around to d/m/y, y/m/d, m/d/y etc.
And to top it all off I have a copy of the database running on a different machine at home and when run on that machine the code works aswell!
Any help would be greatly appreciated, this is driving me insane.
Thanks in advance
- Ben
