How do I display SQL Server table data using PHP?

Question

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.

Check for errors on sqlsrv_query() using sqlsrv_errors(). See php.net/manual/de/… And i think sqlsrv_fetch_assoc does not exist. Should be sqlsrv_fetch_array. Example see: php.net/manual/de/… — cottton
– cottton, Commented Oct 21, 2021 at 1:17
@cottton I think sqlsrv_fetch_assoc exists but I was using it incorrectly. — Matt
– Matt, Commented Oct 21, 2021 at 14:53

Matt · Accepted Answer · 2021-10-26 12:38:21Z

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 say SELECT * FROM Facility and 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_array and SQLSRV_FETCH_NUMERIC in a while statement. NOTE - It only accepts upper-case SQLSRV_FETCH_NUMERIC vs sqlsrv_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>

Collectives™ on Stack Overflow

How do I display SQL Server table data using PHP?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related