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';

}

issue & question. 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. — BENN1TH
– BENN1TH, Commented Nov 26, 2016 at 9:34
Your while loop (while($row = $query->fetchArray(SQLITE3_ASSOC))) should end just before the comment ' //build HTML table' — akrys
– akrys, Commented Nov 26, 2016 at 9:41

Harry Wood · Accepted Answer · 2018-07-26 13:33:04Z

4

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.

edited Jul 26, 2018 at 13:33

answered Jul 26, 2018 at 0:07

Harry Wood

2,3712 gold badges24 silver badges50 bronze badges

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

Comments

akrys · Accepted Answer · 2016-11-26 10:51:40Z

1

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>';

edited Nov 26, 2016 at 10:51

answered Nov 26, 2016 at 9:57

akrys

5631 gold badge3 silver badges9 bronze badges

4 Comments

BENN1TH Over a year ago

Essentially works but only shows one row even though I know I have multiple rows in the table. Also want to echo each column row value without using(assuming) I know the column name $dataRow['the column name'] ...

akrys Over a year ago

You'll need a second foreach: foreach($dataRow as $key => $value){echo '<td>'.$value.'</td>';}

BENN1TH Over a year ago

removed the $dataRows = array(); allowed me to get each row data but still need to get the row column value without knowing the column name in $dataRow['column1'];

akrys Over a year ago

hm, that doesn't make sense. Now you shoud get a notice, that $dataRows is not defined while trying to add the database rows. Did you place $dataRows[]=$row; in the beginning of your while loop?

BENN1TH · Accepted Answer · 2016-11-26 10:42:21Z

0

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>';
}

answered Nov 26, 2016 at 10:42

BENN1TH

2,0212 gold badges32 silver badges43 bronze badges

Collectives™ on Stack Overflow

Get SQLite Table Data into HTML Table

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related