PHP/MSSQL: query result to table

Question

I'm trying to return results from a MS SQL query, into a table. But somehow I only get a blanc page with the headers. I don't know what i'm doing wrong or where to search for an answer. Could someone please direct me in the right way.

Help is appreciated

<?php
    $serverName = "192.168.8.8";
    $connectionOptions = array(
        "Database" => "GeoDynamics",
        "Uid" => "User",
        "PWD" => "Password"
    );
    //Establishes the connection
    $dbh = sqlsrv_connect($serverName, $connectionOptions);
$sql = "SELECT * FROM Aanbest WHERE ReceiptLimitDate = '2018-12-03'  order by ImportDatum desc"; 
$getResults= sqlsrv_query($dbh, $sql);
?>
<style type="text/css">
.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #729ea5;border-collapse: collapse;}
.tftable th {font-size:12px;background-color:#acc8cc;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;text-align:left;}
.tftable tr {background-color:#d4e3e5;}
.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;}
.tftable tr:hover {background-color:#ffffff;}
</style>
<table class="tftable" border="1">
<thead>
    <tr>
    <th>ID</th>
    <th>Aanbestedingsdatum</th>
    <th>Klasse</th>
    <th>Omschrijving</th>
    <th>Postcode</th>
    <th>Stad</th>
    <th>Bestuur</th>
    <th>LikedBy?</th>
    <th>Like</th>
    </tr>
</thead>
<tbody>

<?php
    foreach ($dbh->query($sql) as $rows){
    ?>
    <tr>
        <td><?php echo $rows['ID']?></td>
        <td><?php echo $rows['ReceiptLimitDate']?></td>
        <td><?php echo $rows['Classes1']?></td>
        <td><?php echo $rows['Title']?></td>
        <td><?php echo $rows['AdministrationZip']?></td>
        <td><?php echo $rows['AdministrationCity']?></td>
        <td><?php echo $rows['AdministrationName']?></td>
        <td><?php echo $rows['LikedBy']?></td>
        <td><button type="button" id="like_btn">Like</button></td>
    </tr>
 <?php
  }
 ?>
 </tbody>
</table>

Check the result of the query first. You also are executing it twice. `$getResults` is never being used. — user3783243, Feb 10 '19 at 17:18
Are you sure the connection was established? Did you get any errors? Is $getResults empty? — abestrad, Feb 10 '19 at 17:19

score 1 · Accepted Answer · answered Feb 10 '19 at 18:42

What I think is that $dbh->query($sql) is the reason for your error. Variable $dbh holds the result from sqlsrv_connect(), but you use it as a PDO class variable.

Change your code like this (including error checks):

<?php
    $serverName = "192.168.8.8";
    $connectionOptions = array(
        "Database" => "GeoDynamics",
        "Uid" => "User",
        "PWD" => "Password"
    );
    //Establishes the connection
    $dbh = sqlsrv_connect($serverName, $connectionOptions);
    if ($dbh === false) {
        echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
        exit;
    }
    $sql = "SELECT * FROM Aanbest WHERE ReceiptLimitDate = '2018-12-03'  order by ImportDatum desc"; 
    $getResults = sqlsrv_query($dbh, $sql);
    if ($getResults === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }
?>
<style type="text/css">
.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #729ea5;border-collapse: collapse;}
.tftable th {font-size:12px;background-color:#acc8cc;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;text-align:left;}
.tftable tr {background-color:#d4e3e5;}
.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;}
.tftable tr:hover {background-color:#ffffff;}
</style>
<table class="tftable" border="1">
<thead>
    <tr>
    <th>ID</th>
    <th>Aanbestedingsdatum</th>
    <th>Klasse</th>
    <th>Omschrijving</th>
    <th>Postcode</th>
    <th>Stad</th>
    <th>Bestuur</th>
    <th>LikedBy?</th>
    <th>Like</th>
    </tr>
</thead>
<tbody>
<?php
    while ($rows = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
?>
    <tr>
        <td><?php echo $rows['ID']?></td>
        <td><?php echo $rows['ReceiptLimitDate']?></td>
        <td><?php echo $rows['Classes1']?></td>
        <td><?php echo $rows['Title']?></td>
        <td><?php echo $rows['AdministrationZip']?></td>
        <td><?php echo $rows['AdministrationCity']?></td>
        <td><?php echo $rows['AdministrationName']?></td>
        <td><?php echo $rows['LikedBy']?></td>
        <td><button type="button" id="like_btn">Like</button></td>
    </tr>
<?php
  }
?>
</tbody>
</table>

score 0 · Answer 2 · answered Feb 10 '19 at 17:22

0

You are probably seeing what is called the "white screen of death". Basically your script runs into some error while executing your code, but error reporting is turned off, so it fails silently.

Try appending this to the beginning of the script and see if you can see the actual issue:

ini_set('display_errors', 'On');
ini_set('html_errors', 0);
error_reporting(E_ALL);

If that doesn't work check the more comprehensive answer here, or consult with your hosting provider about their php.ini settings.

answered Feb 10 '19 at 17:22

Marcell Toth

3,433
1
21
36

i'm getting an error on row 40 --> this line: foreach ($dbh->query($sql) as $rows){ – Ivan Pudic Feb 10 '19 at 17:29
1

You need to replace that line with something like `while($obj = sqlsrv_fetch_object($getResults))`. But this is starting to get beyond the scope of your original question, check this thread https://stackoverflow.com/q/23131514/10614791 and you should be able to figure it out I think. If you keep having issues, feel free to come back with another question, detailing the exact error you receive. – Marcell Toth Feb 10 '19 at 17:33

PHP/MSSQL: query result to table

2 Answers2