0

I have a working query put together in PHP using sqlsrv functions. I'd like to check whether the query contains any results. If it doesn't, I'd like to echo, "No results found". If there are results, I'd like it to proceed and display them.

I've been trying to use sqlsrv_num_rows to check whether the $result variable contains any rows of data but it echo's "No results found" for everything, even for valid searches. For example, if I type in "EOG" in the form and hit "search", the resulting page says, "No results found". The expected result be 20 rows of "EOG" variants. I'm fairly knew to PHP and sqlsrv and have scoured Stackoverflow but haven't come across something that has worked yet. Any help would be appreciated.

Here's the forms section where the user enters his/her search:

<form action="searchResults.php" method="POST" autocomplete="off">
    <input type="text" class="form-control" name="opname" placeholder="Search Text">
    <button type="submit" name="submit-search">Search</button>
</form>

Here's the PHP where the form input data is received:

<?php
        if (empty($_POST['opname'])) {
            echo "Nothing was entered in the input field";
        } else {
            $params = array("%$_POST[opname]%");

            $query = "SELECT TOP 20 EntityName
                      FROM Well
                      LEFT JOIN Entity ON Entity.PKey = Well.Operator
                      WHERE EntityName LIKE ?;";

            $result = sqlsrv_query($conn, $query, $params);

            if (sqlsrv_num_rows($result) == 0) {
                echo "No results found";
            } else {
                while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC)) {
                    echo $row[0]."<br />";
                }
            }
            sqlsrv_free_stmt($result);
        }
    ?>
Hiebs915
  • 444
  • 4
  • 16
  • Do a `var_dump(sqlsrv_num_rows($result))` and check if it actually returns `0` or if it, for some reason fails and returns `false` instead. – M. Eriksson Oct 26 '21 at 22:32
  • @MagnusEriksson This is what I get from the var_dump you mentioned: bool(false) No results found. – Hiebs915 Oct 26 '21 at 22:34
  • 1
    That means that `sqlsrv_num_rows($result)` fails, not that it didn't return any rows. In [the manual](https://www.php.net/manual/en/function.sqlsrv-num-rows) for `sqlsrv_num_rows()`, there's a comment saying that you need to add `array('Scrollable' => 'buffered')` as a fourth argument for `sqlsrv_query()` in order for `sqlsrv_num_rows()` to work properly. Try that and see if it works for you. – M. Eriksson Oct 26 '21 at 22:36
  • @MagnusEriksson good catch on the comment! I used `array('Scrollable' => 'buffered') as the fourth argument in `$result = sqlsrv_query($conn, $query, $params, array('Scrollable' => 'buffered'));` and it worked! Feel free to make your suggestion an official answer and I'll accept it. – Hiebs915 Oct 26 '21 at 22:44
  • I found a duplicate of the issue that explains it very well, so I've marked it as a duplicate of that instead. – M. Eriksson Oct 26 '21 at 22:48
  • @MagnusEriksson Yes, it would have helped me, although I doubt I would have caught the comment in the PHP manual. I spent too much time looking up things related to echoing the right message when I should have searched "sqlsrv_num_rows count" or something like it. – Hiebs915 Oct 26 '21 at 22:52
  • 1
    @Hiebs915, if you simply need to check if the result set has one or more rows, [sqlsrv_has_rows()](https://docs.microsoft.com/en-us/sql/connect/php/sqlsrv-has-rows?view=sql-server-ver15) is more appropriate, because you do not need to change the cursor type. – Zhorov Oct 27 '21 at 05:43
  • @Zhorov I like this. Using this method allows you to remove the fourth argument in `sqlsrv_query()`. Instead of making another variable I used, `if ((sqlsrv_has_rows($result)) === true) ` to check whether any rows existed. – Hiebs915 Oct 27 '21 at 13:25

0 Answers0