CODEWITHSUNDEEP
phpsqlsql-serversqlsrv
Eugene Chefranov
Eugene Chefranov

Reputation: 171

Can't get data from col which has datetime format

I am trying to get column data filtered by current time from MS SQL Server. Cells have datetime format. But I get empty response. Where is a mistake?

enter image description here

enter image description here

<?php
$serverName = ''; // server name
$cinfo      = array(
    "Database" => "", // database
    "UID" => "", // user
    "PWD" => "" // pass
);
$conn       = sqlsrv_connect($serverName, $cinfo);

if ($conn) {
    echo "connected" . "<br/>";
    $datetimenow = current_time("d.m.Y H:i"); // wordpress function
    echo $datetimenow . "<br/>"; // 30.05.2020 14:10
    if (($result = sqlsrv_query($conn, "SELECT * FROM dbo.GorElectroTrans WHERE GPS_datetime=$datetimenow")) !== false) {
        while ($obj = sqlsrv_fetch_object($result)) {
            echo $obj->Longitude . '<br />';
        }
    }
} else {
    die(print_r(sqlsrv_errors(), true));
}
?>

Upvotes: 1

Views: 353

Answers (1)

Zhorov
Zhorov

Reputation: 30003

You need to change the statement and use parameterized query to prevent possible SQL injection issues. As is mentioned in the documentation, the sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply. This function provides a streamlined method to execute a query with a minimum amount of code. The sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

When you use a parameter for datetime values, you have two options:

  • Pass datetime value as text, using unambiguous datetime format for the value of $datetimenow variable (in your case function current_time returns current time as text from formatted PHP datetime object).
  • Pass datetime value as PHP datetime object using extended parameter syntax.

PHP:

<?php
$serverName = '';     // server name
$cinfo = array(
    "Database" => "", // database
    "UID" => "",      // user
    "PWD" => ""       // pass
);
$conn = sqlsrv_connect($serverName, $cinfo);

if ($conn) {
    echo "connected" . "<br/>";

    // Datetime value in `yyyy-mm-ddThh:mi:ss` format
    $datetimenow = current_time("Y-m-d\TH:i:00");
    // or if you want a specific datetime value
    //$datetimenow = '2020-05-30T14:10:00';

    // Pass datetime value as text
    $sql = "SELECT * FROM dbo.GorElectroTrans WHERE GPS_datetime = ?";
    $params = array($datetimenow);
    if (($result = sqlsrv_query($conn, $sql, $params)) !== false) {
        while ($obj = sqlsrv_fetch_object($result)) {
            echo $obj->Longitude . '<br />';
        }
    }

    // Pass datetime value as PHP datetime object
    $sql = "SELECT * FROM dbo.GorElectroTrans WHERE GPS_datetime = ?";
    $params = array(
        array(
           date_create_from_format('Y-m-d\TH:i:s', $datetimenow),
           SQLSRV_PARAM_IN, 
           SQLSRV_PHPTYPE_DATETIME, 
           SQLSRV_SQLTYPE_DATETIME
        )
    );
    if (($result = sqlsrv_query($conn, $sql, $params)) !== false) {
        while ($obj = sqlsrv_fetch_object($result)) {
            echo $obj->Longitude . '<br />';
        }
    }

} else {
    die(print_r(sqlsrv_errors(), true));
}
?>

Additional notes:

  • When you retrieve data from SQL Server, the datetime values are returned as PHP datetime objects by default, so you need to use DateTime->format() or to set 'ReturnDatesAsStrings' option in the connection string or at the statement level.
  • When you send bind values to a decimal or numeric column (also mentioned in the documentation), it is recommended to use strings as inputs to ensure precision and accuracy as PHP has limited precision for floating point numbers. The same applies to bigint columns, especially when the values are outside the range of an integer.

Upvotes: 1

Related Questions