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.
CodePudding user response:
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);
?>