How do I return multiple results from my Stored Procedure function if it returns only one result?

Question

I have an application right now that uses SQL Server 2017. When I am executing a stored procedure on SSMS, it shows all of the data. But then, on PHP, when I am executing it, it only shows the first data that comes up.

This is the command:

EXEC dbo.SP_Get_Books_Archive;

The stored procedure I am using:

CREATE PROCEDURE SP_Get_Books_Archive
AS
BEGIN
SELECT 
   Book_ISBN as ISBN, 
   Book_Name, Book_Author, 
   Category_Name, 
   Book_Status, 
   Book_Copies_Current, 
   Book_Copies_Actual 
FROM Book 
LEFT JOIN Book_Category ON (Book.Book_Category_ID = Book_Category.Category_ID)
END

PHP Code I am using:

<?php
$connection = sqlsrv_connect($server, $connectionInfo);
$query = "EXEC SP_Get_Books_Archive";
$statement = sqlsrv_prepare($connection, $query);
$result = sqlsrv_execute($statement);
$row = sqlsrv_fetch_array($statement);
$rowCount = 1;

var_dump($row);
if (count($row) > 0) {
    foreach ($row as $key => $value) {
        if ($key == 'ISBN') {
            echo "<tr>";
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Name') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Author') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Category_Name') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Status') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Copies_Current') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Copies_Actual') {
            echo "<td>" . $value . "</td>";
        }
        if ($rowCount == count($row)) {
            echo "</tr>";
        }
        $rowCount++;
    }
}

(note: this is enclosed on a <?php include(get_books.php) ?> code located on another php file. tried not separating it but no avail)

To which the result only says:

I also tried executing the full SELECT... script on the $query but same results. Is there something wrong with my PHP code or is it a SQL Server error I have missed?

This is somewhat similar to this post but I don't think the answer there is my issue.

Answer 1

You need to fetch all data:

while ($row = sqlsrv_fetch_array($statement, SQLSRV_FETCH_ASSOC)) {
   ...
}

As an additional note, you need to check the result from each sqlsrv_ function call. An example, based on your code:

<?php
// Connection
$connection = sqlsrv_connect($server, $connectionInfo);
if ($connection === false ) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Statement
$query = "EXEC SP_Get_Books_Archive";
$statement = sqlsrv_prepare($connection, $query);
if ($statement === false) {
    echo "Error (sqlsrv_prepare): ".print_r(sqlsrv_errors(), true);
    exit;
}   

// Execution
$result = sqlsrv_execute($statement);
if ($result === false) {
    echo "Error (sqlsrv_execute): ".print_r(sqlsrv_errors(), true);
    exit;
}   

// Fetch data
$rowCount = 0;
while ($row = sqlsrv_fetch_array($statement, SQLSRV_FETCH_ASSOC)) {
    $rowCount++;
    echo "<tr>";
    foreach ($row as $key => $value) {
        if ($key == 'ISBN') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Name') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Author') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Category_Name') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Status') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Copies_Current') {
            echo "<td>" . $value . "</td>";
        } else if ($key == 'Book_Copies_Actual') {
            echo "<td>" . $value . "</td>";
        }
    }
    echo "</tr>";
}

// End
sqlsrv_free_stmt($statement);
sqlsrv_close($connection);
?>