Query with data from user in MSSQL with PHP

Question

(I'm forced to work on microsoft sql server in my internship). I don't understand why my query doesn't work in PHP (it returns no data), but it works when I put it directly in Microsoft SQL Server Management Studio (it returns the datas).

Here is my code :

<?php 
  require('conn.php');

    if(isset($_POST['submit-search'])){
      $search = $_POST['search'];
      $sql = "SELECT codepo, codepsa, controle, FORMAT(date, 'dd-MM-yyyy hh:mm:ss') as date FROM dbo.codebarre where datediff(day, date, '$search') = 0";
      var_dump($sql);
      $result = sqlsrv_query($conn2, $sql);
      $queryResult = sqlsrv_num_rows($result);

      ?>
    (...)
      <?php
      if($queryResult > 0){
        while($donnees = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
          ?>
          <tbody>
          <tr>
            <th style="font-weight: normal;"><?php echo htmlspecialchars($donnees['codepo']); ?></th>
            <td><?php echo htmlspecialchars($donnees['codepsa']); ?></td>
            <td <?php if ($donnees['controle'] === 'NOK') {
                  echo 'style="color: red; font-weight: bold"';
                } ?>><?php echo htmlspecialchars($donnees['controle']);  ?></td>
            <td><?php echo $donnees['date'] ?></td>
          </tr>
        </tbody>
        <?php
        }
      } else {
        echo "No data";
      }
    }

The var_dump($sql) returns me this :

string(138) "SELECT codepo, codepsa, controle, FORMAT(date, 'dd-MM-yyyy hh:mm:ss') as date FROM dbo.codebarre where datediff(day, date, '20210107') = 0"

As I told you when I paste it in Management studio it works, so I don't understand why it doesn't here.

Does this answer your question? [sqlsrv\_num\_rows Not Returning Any Value](https://stackoverflow.com/questions/23741534/sqlsrv-num-rows-not-returning-any-value) . See also https://www.php.net/manual/en/function.sqlsrv-num-rows.php — ADyson, Jan 07 '21 at 11:40

Zhorov · Accepted Answer · 2021-01-07T12:05:13.840

You have two options:

Execute sqlsrv_query() with the appropriate cursor type, if you want to get the exact number of the returned rows.
Use sqlsrv_has_rows() if you want to check if there are rows returned.

PHP code using sqlsrv_num_rows():

<?php
    
   ...
   $result = sqlsrv_query($conn2, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
   $queryResult = sqlsrv_num_rows($result);
   if ($queryResult > 0) {
       // Fetch data
   }

   ...
?>

PHP code using sqlsrv_has_rows():

<?php
    
   ...
   $result = sqlsrv_query($conn2, $sql);
   $queryResult = sqlsrv_has_rows($result);
   if ($queryResult) {
       // Fetch data
   }

   ...
?>

As an additional note, always use parameterized statements to prevent possible SQL injection issues. As is mentioned in the documentation, function sqlsrv_query() does both statement preparation and statement execution and can be used to execute parameterized queries.

<?php

    ...
    $sql = "
        SELECT codepo, codepsa, controle, FORMAT(date, 'dd-MM-yyyy hh:mm:ss') AS date 
        FROM dbo.codebarre 
        WHERE datediff(day, date, ?) = 0
    ";
    $params = array($search);
    $options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
    $result = sqlsrv_query($conn2, $sql, $params, $options);

...
?>

Query with data from user in MSSQL with PHP

1 Answers1