Large HTML from SQL Server Using PHP

Question

I've got an MSSQL server running with several large tables. I'm trying to place them into an HTML table so that I can display all of the data in a nice CSS modified webpage. I'm using PHP to ferry the information from SQL to my HTML script, but the only way I've found so far to generate such a table is by hardcoding all of the SQL column names into my PHP-SQL query.

As you can see below, this is the setup required for just one such table. Is there any more concise way to get the information from SQL into a formatted HTML table? I've looked around for a number of hours perhaps for some sort of PHP scripted loop that can iterate through all of the SQL columns, but I haven't found anything. I greatly appreciate any input!

<table id="capacitors">
<thead>
    <tr>
    <td>Part Number</td>
    <td>Capacitance</td>
    <td>Capacitance Tolerance</td>
    <td>Case Package</td>
    <td>Case Package SI</td>
    <td>Dielectric Char.</td>
    <td>Dielectric Mat.</td>
    <td>Halogen Free Stat.</td>
    <td>Insulation Resistance</td>
    <td>Lead Free Stat.</td>
    <td>Lifecycle Stat.</td>
    <td>Mounting Style</td>
    <td>Operating Temp.</td>
    <td>Packaging</td>
    <td>Pin Count</td>
    <td>Reach SVHC Comp.</td>
    <td>Rohs Stat.</td>
    <td>Size Height</td>
    <td>Size Length</td>
    <td>Size Thickness</td>
    <td>Size Width</td>
    <td>Temp. Coefficient</td>
    <td>Voltage Rating DC</td>
    </tr>
</thead>
<tbody>
<?php
    foreach ($db->query($sql) as $rows){
    ?>
    <tr>
        <td><?php echo $rows['part_number']?></td>
        <td><?php echo $rows['capacitance']?></td>
        <td><?php echo $rows['capacitance_tolerance']?></td>
        <td><?php echo $rows['case_package']?></td>     
        <td><?php echo $rows['case_package_si']?></td>
        <td><?php echo $rows['dielectric_characteristic']?></td>
        <td><?php echo $rows['dielectric_material']?></td>
        <td><?php echo $rows['halogen_free_status']?></td>
        <td><?php echo $rows['insulation_resistance']?></td>
        <td><?php echo $rows['lead_free_status']?></td>
        <td><?php echo $rows['lifecycle_status']?></td>
        <td><?php echo $rows['mounting_style']?></td>
        <td><?php echo $rows['operating_temperature']?></td>
        <td><?php echo $rows['packaging']?></td>
        <td><?php echo $rows['pin_count']?></td>
        <td><?php echo $rows['reach_svhc_compliance']?></td>
        <td><?php echo $rows['rohs_status']?></td>
        <td><?php echo $rows['size_height']?></td>
        <td><?php echo $rows['size_length']?></td>
        <td><?php echo $rows['size_thickness']?></td>
        <td><?php echo $rows['size_width']?></td>
        <td><?php echo $rows['temperature_coefficient']?></td>
        <td><?php echo $rows['voltage_rating_dc']?></td>
    </tr>
 <?php
  }
 ?>
</tbody>
</table>

You should be able to query for the column names (In a seperate query), and output them as the table numbers. — Blue
– Blue, Commented Jul 31, 2018 at 20:54
If PHP is ferrying the data as an associative array, you should be able to get an array of column headings from the first (or any) row (for example by calling array_keys() ). — billrichards
– billrichards, Commented Jul 31, 2018 at 21:03
Apologies for being somewhat ignorant here, but how would I tell what format PHP is carrying the information in? — Jonny1998
– Jonny1998, Commented Jul 31, 2018 at 21:28

Zhorov · Accepted Answer · 2018-08-01 06:46:54Z

1

This is basic example using PHP Driver for SQL Server:

<?php
# Settings
$server    = 'server\instance,port';
$database  = 'database';
$user      = 'user';
$password  = 'password';
$tablename = 'tablename';

# Connection
$cinfo = array(
    "Database" => $database,
    "ReturnDatesAsStrings" => true,
    "UID" => $user,
    "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if($conn === false)
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# SQL statement
$sql = "SELECT * FROM [".$tablename."]";
$stmt = sqlsrv_prepare($conn, $sql);
if($stmt === false) {
    echo "Error (sqlsrv_prepare): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Columns names
echo '<table id="'.$tablename.'">';
echo "<thead>";
echo "<tr>";
$metadata = sqlsrv_field_metadata($stmt);
foreach($metadata as $field) {
    echo "<td>".$field['Name']."</td>";
}
echo "</tr>";
echo "</thead>";

# Table rows
echo "<tbody>";
if (!sqlsrv_execute($stmt)) {
    echo "Error (sqlsrv_execute): ".print_r(sqlsrv_errors(), true);
    exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo "<tr>"; 
    foreach($row as $value) {
        echo "<td>".$value."</td>";
    };
    echo "</tr>";
}
echo "</tbody>";
echo "</table>";

# End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

edited Aug 1, 2018 at 6:46

answered Aug 1, 2018 at 6:41

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

4 Comments

Jonny1998 Over a year ago

Thank you, this is exactly what I've been looking for. Now I can simply adjust the $tablename variable to switch between tables. Two questions if you don't mind, a lot of the special characters that I have to load like μ and ° are appearing as �. I assume this is something to do with the utf-8 encoding. Any suggestions? And why is it necessary to surround the <table>, <thead>, <tr> and so forth with quotes and echo them?

Jonny1998 Over a year ago

Ignore the character encoding portion. I realized I needed to place a "CharacterSet" => "UTF-8" entry into the $cinfo variable otherwise php wouldn't correctly (correct as in the format I needed) interpret the incoming characters

Zhorov Over a year ago

@Jonny1998 I'm glad to help you! You are right about "CharacterSet" => "UTF-8". About the second question - sometimes I output HTML with echo, sometimes I output it outside the <?php ?> - so it is not necessary to surround the HTML with quotes and then echo.

Jonny1998 Over a year ago

Thanks for the explanation! I was wondering if it really was a syntactically necessary step or personal preference :P

Mike K · Accepted Answer · 2018-07-31 20:57:16Z

0

Maybe use SHOW COLUMNS or DESCRIBE

$columns = "SHOW COLUMNS FROM <table>";
$output = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($result)){
    echo $row['Field']."<br>";
}

answered Jul 31, 2018 at 20:57

Mike K

6,62517 gold badges79 silver badges150 bronze badges

1 Comment

Jonny1998 Over a year ago

I think that would be a good suggestion for MySQL, unfortunately, I'm using Microsoft SQL Server which has a different command structure

Collectives™ on Stack Overflow

Large HTML from SQL Server Using PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related