Rataiczak24 Rataiczak24 - 2 months ago 16
PHP Question

Import Data from SQL Server Database Into HTML Table

I have many rows in my DB table so I am unable to hardcode anything in. I am assuming I need some sort of loop that loops through the rows in the DB then displays it into my HTML table. Here is my code so far...could you tell me what I need to do?

<html>
<head>
<title>Stage Rebate Master HTML Table</title>
</head>

<body>
<?php
$host="xxxxx";
$dbName="xxxxx";
$dbUser="xxxx";
$dbPass="xxxxxxxxx";
$dbh = new PDO( "sqlsrv:server=".$host."; Database=".$dbName, $dbUser, $dbPass); var_dump($dbh);

if (!$dbh) {
die("Connection failed: " . sqlsrv_connect_error());
}
echo "Connected successfully";

$results = sqlsrv_query("SELECT * FROM Stage_Rebate_Master ORDER BY MR_ID ASC");

$id = 'MR_ID';
$name = 'MR_Name';
$buyer = 'Buyer_ID';
$poc_name = 'MR_POC_N';
$poc_email = 'MR_POC_E';
$poc_tel = 'MR_POC_P';
?>

<table>
<thead>
<tr>
<td>MR_ID</td>
<td>MR_Name</td>
<td>Buyer_ID</td>
<td>MR_POC_N</td>
<td>MR_POC_E</td>
<td>MR_POC_P</td>
</tr>
</thead>
<tbody>
<?php
while($rows = sqlsrv_fetch_array($results)) {
?>
<tr>
<td><?php echo $rows['MR_ID']?></td>
<td><?php echo $rows['MR_Name']?></td>
<td><?php echo $rows['Buyer_ID']?></td>
<td><?php echo $rows['MR_POC_N']?></td>
<td><?php echo $rows['MR_POC_E']?></td>
<td><?php echo $rows['MR_POC_P']?></td>
</tr>

<?php
}
?>
</tbody>
</table>
</body>
</html>

Answer

You can try this code:

<?php
$host="xxxxxxxx"; 
$dbName="BI_Stage"; 
$dbUser="Domo"; 
$dbPass="xxxxxxxxx"; 

$dbh = new PDO( "sqlsrv:server=".$host."; Database=".$dbName, $dbUser, $dbPass); 
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 
$sql = "SELECT * FROM Stage_Rebate_Master ORDER BY MR_ID ASC"; 
?>

<table>
<thead>
    <tr>
    <td>MR_ID</td>
    <td>MR_Name</td>
    <td>Buyer_ID</td>
    <td>MR_POC_N</td>
    <td>MR_POC_E</td>
    <td>MR_POC_P</td>
    </tr>
</thead>
<tbody>

<?php
    foreach ($dbh->query($sql) as $rows){
    ?>
    <tr>
        <td><?php echo intval($rows['MR_ID'])?></td>
        <td><?php echo $rows['MR_Name']?></td>
        <td><?php echo $rows['Buyer_ID']?></td>
        <td><?php echo $rows['MR_POC_N']?></td>     
        <td><?php echo $rows['MR_POC_E']?></td>
        <td><?php echo $rows['MR_POC_P']?></td>
    </tr>
 <?php
  }
 ?>
 </tbody>
</table>