Get SQLite Table Data into HTML Table

Question

I have an SQLite database and using PHP to serve up and interact the data with.

I am trying to build an HTML table with for each SQLite table columns as the HTML Table Columns and then for each row in the SQLite table render as a for each row in the HTML table.

Issue is that each sql row data is only rendered into one html table row (with current code) (should be for each row) question would be how I go about getting the sql row data into array for looping through to render html table rows.

Currently I have;

PHP get the SQL Columns into the HTML table & SQl Row data(foreach) into HTML Table Rows (foreach)

//get table count
$countTable = $dbConnect->querySingle("SELECT COUNT(*) as count FROM ".$table['name']."");

if($countTable){

//data exists, do work
//new query to get table data
$query = $dbConnect->query("SELECT * FROM ".$table['name']." ORDER BY id DESC");

//init arrays
$dataColumns = array();
$dataRows = array();

while($row = $query->fetchArray(SQLITE3_ASSOC))
{


   //add columns to array, checking if value exists
   foreach($row as $key => $value)
   {

       if(in_array(''.$key.'', $dataColumns)){

          //column already in array, dont add again.

       }else{

            //column not in array, add it.
            $dataColumns[]=array(
                'column'=>$key
            );  

        }

        //while in this foreach do I add the row values to an array or do it again outside this loop?

        //below does not work, only adds to the one array item and renders one HTML Table row with multiple SQL Table row values
        $dataRows[]=array(
            'row_item'=>$row[''.$row.'']
        );


    }

   }

   //build HTML table

   echo '<div class="table-responsive"><table class="table"><thead><tr>';

   //build columns from array... works
   foreach($dataColumns as $dataColumn){
        echo '<th>'.$dataColumn['column'].'</th>';
    }

    //close table column headers 7 start HTML table body...
    echo '</tr></thead><tbody>';

    //Issue is here, how do I get the each row (value is either null or not null) to

    echo '<tr>';
    foreach($dataRows as $dataRow){
        echo '<td>'.$dataRow['row_item'].'</td>';
    }
    echo '</tr>';


    //close table body & table...
    echo '</tbody></table></div>';


}else{

//table has no data
echo 'no data in the selected table';

}

Answer 1

I rewrote this to do it all in one loop like this.

$firstRow = true;
echo '<div class="table-responsive"><table class="table">';
while ($row = $query->fetchArray(SQLITE3_ASSOC)) {
    if ($firstRow) {
        echo '<thead><tr>';
        foreach ($row as $key => $value) {
            echo '<th>'.$key.'</th>';
        }
        echo '</tr></thead>';
        echo '<tbody>';
        $firstRow = false;
    }

    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>'.$value.'</td>';
    }
    echo '</tr>';
}
echo '</tbody>';
echo '</table></div>';

You might find it clearer to read? It also avoids building an array of all the dataRows in memory.

Answer 2

Try to replace

$dataRows[]=array(
    'row_item'=>$row[''.$row.'']
);

with

$dataRows[]=$row;

Put this line at first line inside your while loop (or general outside the foreach loop over your columns), because adding a row is not connected analyzing your columns.

Then, in your output foreach, you should find the rows with all columns selected from your database query inside $dataRow (here symbolized with column1, column2, …):

echo '<tr>';
foreach($dataRows as $dataRow){
    echo '<td>'.$dataRow['column1'].'</td>';
    echo '<td>'.$dataRow['column2'].'</td>';
    echo '<td>'.$dataRow['column3'].'</td>';
    echo '<td>'.$dataRow['column4'].'</td>';
    echo '<td>'.$dataRow['column5'].'</td>';
    echo '<td>'.$dataRow['column6'].'</td>';
    echo '<td>'.$dataRow['column7'].'</td>';
    // etc.
}
echo '</tr>';

After all your code should look like this (a bit simplified):

$query = $dbConnect->query("SELECT * FROM ".$table['name']." ORDER BY id DESC");

$dataColumns = array();
$dataRows = array();

while ($row = $query->fetchArray(SQLITE3_ASSOC)) {
    $dataRows[] = $row;

    foreach ($row as $key => $value) {
        //Bilding $dataColumns, see Question
    }
}

echo '<div class="table-responsive"><table class="table"><thead><tr>';

foreach ($dataColumns as $dataColumn) {
    echo '<th>'.$dataColumn['column'].'</th>';
}

echo '</tr></thead><tbody>';

echo '<tr>';
foreach ($dataRows as $dataRow) {
    foreach ($dataRow as $columnName => $columnValue) {
        echo '<td>'.$columnValue.'</td>';
    }
}
echo '</tr>';

echo '</tbody></table></div>';

Answer 3

Thanks to akrys

in the while loop, but outside the foreach column loop;

 //$dataRows = array();
 $dataRows[]=$row;

and in the building of the HTML Table;

 foreach($dataRows as $dataRow){
    echo '<tr>';
    foreach($dataRow as $key => $value){echo '<td>'.$value.'</td>';}
    echo '</tr>';
}