Skip to content
Advertisement

PHP/MSSQL: query result to table

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>

Advertisement

Answer

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>
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement