从CSV填充的数据库表中填充多个HTML表

Question

I currently have a site that has a simple interface where the user logs in and either chooses to Upload or View. They can upload a CSV (for this purpose, the CSV template is around 300 fields) or go to a page to select or search for a particular record that's already been uploaded (sorted by certain keys).

In my folder is an HTML page for the file upload/submit section that utilizes an upload.php file, which is around 400 lines of code that handle the insertion of the CSV into the database. I've now created a display.html file that has about 300 other lines of code for 14 or 15 separate HTML tables on one page. I've created them in HTML first because they each have different styles, headers and formats that need to be kept.

My goal is this: When the user selects a record, I need to display the html page with all the different tables, and each table needs to be filled by certain fields in the SQL staging table that holds the 300 field CSV. So, the first ten fields will be in one table, the next 7 in one, and so on. I know I can't call PHP within an HTML page, but I've created the tables in HTML so I'm wondering if I can change it to php and add certain syntax to the existing table and how I would insert those fields into each table row by name. Here is an example of the upload PHP where I've declared variables for the columns:

PHP (For Uploads-Separate file)

if(isset($_POST['submit']))
{

$file = $_FILES["file"]["tmp_name"];
$handle = fopen($file, "r");
$filesop = fgetcsv($handle, 0, ",");

$coldata = array();

$coldata[ "orderNumber" ] = $filesop[0];
$coldata[ "place" ] = $filesop[1];
$coldata[ "workOrderNum" ] = $filesop[2];
$coldata["lowSideMIUNum"] = $filesop[3];
$coldata["highSideMIUNum"] = $filesop[4];
$coldata["accountNum"] = $filesop[5];
$coldata["custName"] = $filesop[6];
$coldata["address"] = $filesop[7];
$coldata["locID"] = $filesop[8];
$coldata["date"] = $filesop[9];
$coldata["utility"] = $filesop[10];
$coldata["serialNumber"] = $filesop[11];
$coldata["serviceName"] = $filesop[12];
$coldata["address2"] = $filesop[13];
$coldata["servicePreformed"] = $filesop[14];
$coldata["bypassSize"] = $filesop[15];
}

HTML for Existing Tables (Only one table. I left the PHP in the file from my attempt at using it as a PHP file):

<!--<?php

$server = "localhost";
$user = "root";
$pw = "root";
$db = "uwsTest";

$connect = mysqli_connect($server, $user, $pw, $db);

$query1 = "SELECT * FROM staging";
$result1 = mysqli_query($connect,$query1);

if ($connect->connect_error) {
die("Connection failed: " . $conn->connect_error);
}else{
    echo'success!';
}


?>


<?
while($row = mysql_fetch_array($result1)){
?>
<!--Qa Table-->
<table>
<tr>
<th colspan="2">Qa/Qc CheckList</th>
</tr>
<tr>
<td>Service Address Correct</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Service Loc Correct</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Meter Number Correct</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Meter Manufacturer Changed</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Meter Type Changed</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Meter Model Changed</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>Low Register Correct</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
<tr>
<td>High Register Correct</td>
<td><? echo $row['orderNumber'];?>&nbsp;</td>
</tr>
</table>
<br>
<br>

<?
}
?>

I left the PHP code in with the table to show what I attempted, but I don't think the naming convention on the table names worked with my php. On my web page I only get me message for a successful db connection but my tables don't show like they did when I ran it only as PHP. What is the best way to insert variables from my database into multiple tables on this page?

Answer 1

This solution would allow you to configure which columns appear in which table.

<?php

$tables = array();
$tables[0] = array('title' => 'Qa/Qc CheckList', 'cols' => array('Order #' => 'orderNumber','Place' => 'place','Work Order' => 'workOrderNum'));
$tables[1] = array('title' => 'Another CheckList', 'cols' => array('Low Side MIU' => 'lowSideMIUNum','Account #' => 'accountNum','Name' => 'custName'));
// ... add other table definitions ...

if ($connect->connect_error) {
die("Connection failed: " . $conn->connect_error);
} else {
    echo'success!';
}
$query1 = "SELECT * FROM staging";
$result1 = mysqli_query($connect,$query1);

$all_rows = mysqli_fetch_all($result1);

if($all_rows) {
    foreach($tables as $tableno => $tableinfo) {
        foreach($all_rows as $rowno => $row) {
?>
<!-- <?php echo $tableinfo['title']; ?> -->
<table>
<tr>
    <th colspan="2"><?php echo $tableinfo['title']; ?></th>
</tr>
<?php
            foreach($tableinfo as $col_label => $col_name) {
?>
<tr>
    <td><?php echo $col_label; ?></td>
    <td><?php echo $row[$col_name]; ?></td>
</tr>
<?php
            } // end of foreach $table_info
?>
</table>
<?php
        } // end of foreach $all_rows
?>
<?php
    } // end of foreach $tables

} else {
    echo "<p>No data found.</p>
";
}
?>