PHP not showing sql query result but no error codes to highlight why

Question

I'm trying to get data from a Microsoft SQL database using PHP which the PHP is based on a Raspberry PI running Apache2 and the database on a separate server. From the Raspberry PI I can connect to the server OK but when I try using PHP to get the data to display as a web page I get nothing and also no errors

SQL code for set up (image of existing table should be attached):

sql set up
CREATE TABLE [dbo].[statuslabel](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [status_label_value] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED

PHP code:

<!DOCTYPE html>
<html land="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE-edge">
    <meta name="viewport" content="width-device-width, initial-scale=1.0">
    <title>Test SQL page</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
</head>

<body>
<h1>Test page, SQL connection</h1>
<br>

<?php
/*set up connection*/
$serverName = "ipaddress:port number"; //serverName\instanceName, portNumber (1433 by default)
$connectionInfo = array( "Database"=>"databasename", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo) or die("Unable to Connect to database");

if ($conn === false){  
    echo "Could not connect.\n";  
    die( print_r( sqlsrv_errors(), true));  
}  

/* Set up and execute the query. */ 
$tsql = "SELECT [id],[status_label_value] FROM [dbo].[statuslabel]";
$stmt = sqlsrv_query($conn, $tsql) or die("SQL query error");

if( $stmt === false){
    echo "Error in query preparation/execution.\n";  
    die( print_r( sqlsrv_errors(), true));
}

while($obj = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    echo $obf['status_label_value'].'</br>';
}
 
sqlsrv_free_stmt( $stmt);  
sqlsrv_close( $conn); 
?>

</body>
</html>

sql table set up
result from php page when viewed

I have looked though various example on this site and tried the answers I understood and could adapt but not success. I have also tried mysqli code as well as sqlsrv and both seem to return blank tables. Connection to server tests OK based on IP address and port number and I have tried to add error code but nothing is pointing my in the right direction. If I try to connect to a MariaDB hosted locally on the Raspberry PI I can fetch the data so I am wondering if:

I cannot get data from a different SQL server or
The Raspberry PI OS running apache and PHP can't get the data from the Mircosoft server.

I have tried adding the Mircosoft driver to the Pi but I get msodbcsql18 unavailable (same for msodbcsql17) and unixodbc but still no luck.

Please do some research on [how to debug](https://stackoverflow.com/search?q=%5Bphp%5D+how+to+debug+php) and take a look at: [How do I get PHP errors to display?](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) — Luuk, Mar 14 '23 at 16:42
Do you think it is more a PHP issue than an SQL issue? The page loads just without data so my thinking was the PHP scrip has ran fully just I got no data from the database — user21397714, Mar 14 '23 at 18:02
I think your connection is failing (Because of: `$serverName = "ipaddress:port number"` ), or do you have a server which is reachable with the name `ipaddress`? I would expect you to see the message from: `die("Unable to Connect to database")`. (see: [How do I get PHP errors to display?](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display)) — Luuk, Mar 14 '23 at 18:28
I get same result regardless of using ipaddress:portnumber or serverName\\instance or serverName\\instance, port. Thanks to your links I think I have figured out how to get php error messages and am now getting: Fatal error: Uncaught Error: Call to undefined function sqlsrv_connect(). Any idea what this means? — user21397714, Mar 14 '23 at 19:10
Google is (or should be?) your friend in this: [Fatal error: Call to undefined function sqlsrv_connect()](https://stackoverflow.com/questions/22015179/fatal-error-call-to-undefined-function-sqlsrv-connect) — Luuk, Mar 14 '23 at 19:26

score 0 · Answer 1 · answered Mar 14 '23 at 19:04

I think the problem could be the while loop that are you using to display data. in the loop you are printing the $obf variable

while($obj = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    echo $obf['status_label_value'].'</br>';
}

but you called the variable $obj.

you clud try to use the following code:

while($obj = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    echo $obj['status_label_value'].'</br>';
}

you could also try to put these three line of cod on the top of your php script (in a php tag) to force php to report all the errors:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Many thanks to the replies so far, errors in code correct but still same error. From google it seems to be an issue with the microsoft sql driver needed on the Raspberry Pi to connect to the database. Various options suggest suing unixobdc but none of the steps I can find give me a working solution or one that I understand to be able to install the driver to use sqlsrv in php on a raspberry pi. Anyone found a solution to this to have the right driver? — user21397714, Mar 15 '23 at 08:27

PHP not showing sql query result but no error codes to highlight why

1 Answers1