Issue
I'd like to start displaying some data from a sql server database I work with using PHP. I believe my connection to the database is working but I can't get any data from the Facility table to display in the browser. I've been using an Apache server through XAMPP to run PHP(PHP version 8.0). The SQL server(version 2012) is on another machine in the building. So far I have:
- Downloaded the sqlsrv extension files called, "php_sqlsrv_80ts.dll", and "php_sqlsrv_80_ts.dll". Both are in my XAMPP php.ini file as new extensions (see below)
- Restarted my Apache and MySQL servers after adding the two new extensions.
- Tested my connection and tried displaying some results using the code below:
<!DOCTYPE html>
<html lang="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>MSSQL Connection Test</title>
</head>
<body>
<?php
$serverName = "###"; //serverName\instanceName
$connectionInfo = array( "Database"=>"UTRBDMSNET", "UID"=>"###", "PWD"=>"###");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
$query = "SELECT * FROM Facility;";
$result = sqlsrv_query($conn, $query);
$resultCheck = sqlsrv_num_rows($result);
if ($resultCheck > 0) {
while ($row = sqlsrv_fetch_assoc($result)) {
echo $row['Operator'] . "<br>";
}
}
?>
</body>
</html>When I go to the file in my browser I get this message, "Connection established". I don't see anything in the console. It's hard to tell what's going wrong without any error messages. Ideally, I would like to display something from any of the tables in my database to see if things are working.
Solution
After looking at the two links @cottton posted, I was able to query some data from the database. I found a couple of issues with my code:
- It doesn't seem to like when I use
*in my SQL query. If I specify what column I want to see and tell it to echo the first row in the table, like this,SELECT Operator FROM Facility, it works fine. When I saySELECT * FROM Facilityand specify the column in the echo statement it gives me this error:
"Warning: Undefined array key "Operator" in C:\Users###\Documents\GitHub_Repos\Utah_OG_Website\MSSQLQueryTest.php on line 58".
- I needed to use
sqlsrv_fetch_arrayandSQLSRV_FETCH_NUMERICin a while statement. NOTE - It only accepts upper-caseSQLSRV_FETCH_NUMERICvssqlsrv_fetch_numeric. - If I use
if ($resultCheck > 0) {}it won't show me any results. I'm assuming it's something related to the type of data being returned, not completely sure yet.
Here's the working piece of code for comparison:
<!DOCTYPE html>
<html lang="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>MSSQL Connection Test</title>
</head>
<body>
<?php
$serverName = "###"; //serverName\instanceName
$connectionInfo = array( "Database"=>"###", "UID"=>"###", "PWD"=>"###");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
$query = "SELECT Operator FROM Facility;";
$result = sqlsrv_query($conn, $query);
$resultCheck = sqlsrv_num_rows($result);
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC)) {
echo $row[0] . "<br>";
}
?>
</body>
</html>Answered By - Hiebs915 Answer Checked By - Gilberto Lyons (PHPFixing Admin)
Posts
