Import Data from SQL Server Database Into HTML Table

Question

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>

You already have a loop on your code...also what is the question here? — Hackerman
– Hackerman, Commented Sep 27, 2016 at 15:29
I need to be able to get the data for the DB into the html table...the only thing i have displaying currently are the table headers — Rataiczak24
– Rataiczak24, Commented Sep 27, 2016 at 15:30
I pasted that code right before my while loop and i got bool(false) — Rataiczak24
– Rataiczak24, Commented Sep 27, 2016 at 15:35
Yeah, I was thinking the same....you are mixing PDO with sqlsrv_query...also the first parameter for sqlsrv_query in the connection...you either use one of those...take a look at the documentation: php.net/manual/en/function.sqlsrv-query.php — Hackerman
– Hackerman, Commented Sep 27, 2016 at 15:41

Hackerman · Accepted Answer · 2016-09-27 17:37:07Z

1

You can try this code:

<?php
$host="xxxxxxxx"; 
$dbName="xxxxxxx"; 
$dbUser="xxxxx"; 
$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>

edited Sep 27, 2016 at 17:37

answered Sep 27, 2016 at 17:28

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rataiczak24 Over a year ago

Awesome works great...my only question is that the ID goes 1.0, 2.0, 3.0 etc...is there anyway to get rid of the .0?

Rataiczak24 Over a year ago

Thank you very much!! Appreciate it!!

Lewis Browne · Accepted Answer · 2016-09-27 15:31:42Z

0

You are correct in saying that a loop is required.

Replace your while loop with the following: while ($rows = $results->fetch_assoc()){

I would personally also add in some logic which will check if any data is returned, if not then display a message.

i.e. if($results->num_rows > 0)

answered Sep 27, 2016 at 15:31

Lewis Browne

9307 silver badges25 bronze badges

3 Comments

Rataiczak24 Over a year ago

I used that new while loop and nothing happened

Hackerman Over a year ago

You are not identifying the underlying issues...I am not going to downvote your code, but, try a little bit harder

Lewis Browne Over a year ago

In the case of this not working, i would suggest rewriting your code to follow a more object orientated MySQLi approach. Doing this will make error diagnosis a little easier. For an example of this please see the following: w3schools.com/php/php_mysql_select.asp EDIT: Thanks for the feedback @Hackerman

Collectives™ on Stack Overflow

Import Data from SQL Server Database Into HTML Table

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related