I’m trying to compare the client side time format ("14:00")
to SQL Server time format ("14:00:00.0000000")
with SQL Query, technically is not equal because SQL Server time format has seconds and milliseconds, so I tried to concatenate the seconds and milliseconds to client side time format in PHP script like this 14:00.":00.0000000"
and yet is not equal to SQL Server time format.
My script in PHP:
JavaScript
x
// I assume that the $_POST['pAppointmentTime'] variable has a value of 14:00.
$pAppointTime = $_POST['pAppointmentTime'];
$pAppointTime = $pAppointTime.":00.0000000";
// SQL query
"SELECT * FROM appointment WHERE
pAppointment_Date = '".$pAppointSchedDate."' ".
"AND pAppointment_Time='".$pAppointTime."'".
" AND Department='".$selectedDept."';"
So how can I compare these two with SQL Query and PHP Script?
Advertisement
Answer
I think that you may consider the following:
- Do not concatenate strings to build a statement and use parameters in your statements to prevent possible SQL injection issues.
- Pass date and time values as text using unambiguous date and time formats –
yyyymmdd
andhh:mm:ss
. - For your specific case, you may try to compare the
date
andtime
values using explicit cast and adding only seconds for thetime
values.
An example, that is a possible solution to your problem:
JavaScript
<?php
// Connection info
$server = 'serverinstance';
$database = 'database';
$uid = 'uid';
$pwd = 'pwd';
// Connection
try {
$conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die( "Error connecting to SQL Server".$e->getMessage());
}
// Parameters
$pAppointSchedDate = "date in yyyymmdd format";
$pAppointTime = $_POST['pAppointmentTime'];
$pAppointTime = $pAppointTime.":00";
$selectedDept = "...";
// Statement
try {
$sql = "
SELECT *
FROM appointment
WHERE
CONVERT(date, pAppointment_Date) = CONVERT(date, ?) AND
CONVERT(time(0), pAppointment_Time) = CONVERT(time(0), ?) AND
Department = ?
";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $pAppointSchedDate, PDO::PARAM_STR);
$stmt->bindParam(2, $pAppointTime, PDO::PARAM_STR);
$stmt->bindParam(3, $selectedDept, PDO::PARAM_STR);
$stmt->execute();
while ($row = $stmt->fetch( PDO::FETCH_ASSOC) ){
echo print_r($row, true)."<br>";
}
} catch (PDOException $e) {
die ("Error executing query. ".$e->getMessage());
}
// End
$stmt = null;
$conn = null;
?>