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
– user3783243, Commented Feb 10, 2019 at 17:18
Are you sure the connection was established? Did you get any errors? Is $getResults empty? — abestrad
– abestrad, Commented Feb 10, 2019 at 17:19

Zhorov · Accepted Answer · 2019-02-10 18:42:51Z

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>

Marcell Toth · Accepted Answer · 2019-02-10 17:22:40Z

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, 2019 at 17:22

Marcell Toth

3,6981 gold badge24 silver badges37 bronze badges

2 Comments

Ivan Pudic Over a year ago

i'm getting an error on row 40 --> this line: foreach ($dbh->query($sql) as $rows){

Marcell Toth Over a year ago

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 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.

Collectives™ on Stack Overflow

PHP/MSSQL: query result to table

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related