Use PHP to Display MySQL Results in HTML Table

Question

Update for CodingBiz:

I'm putting this in my code:

for($i=1;$i<=$numRows;$i++) {
    $output .= '<tr>';
    $row = $this->fetchAssoc($result);
    $colRow = $this->fetchAssoc($colResult);
    foreach($colRow as $colName) {
        $output .= "<td>".$row[$colName]."</td>";
    }
    $output .= '</tr>';
}

in place of

for($i=1;$i<=$numRows;$i++) {
    $output .= '<tr>';
    $row = $this->fetchAssoc($result);
    for($j=1;$j<=$colNumRows;$j++) {
        $colRow = $this->fetchAssoc($colResult);
        $output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";
    }
    $output .= '</tr>';
}

Is there anything wrong with this?

Original Post:

I'm writing a function in a PHP class to display the results of a query in a table. I'm not structuring any of the table myself, I want it everything to be done using PHP. Here is my code so far:

function allResults($table,$cols) {
    if(isset($cols)) {
        $query = "SELECT $cols FROM $table";
    }
    else {
        $query = "SELECT * FROM $table";
    }
    $result = $this->query($query);
    $numRows =  $this->numRows($result);
    $colQuery ="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='shareride'  AND TABLE_NAME='$table'";
    $colResult = $this->query($colQuery);
    $colNumRows = $this->numRows($colResult);

    $output = '<table class="allResults">';
    $output .= '<tr>';
    for($i=1;$i<=$colNumRows;$i++) {
        $colRow = $this->fetchAssoc($colResult);
        $output .= "<td>".$colRow["COLUMN_NAME"]."</td>";
    }
    $output .= '</tr>';
    for($i=1;$i<=$numRows;$i++) {
        $output .= '<tr>';
        $row = $this->fetchAssoc($result);
        for($j=1;$j<=$colNumRows;$j++) {
            $colRow = $this->fetchAssoc($colResult);
            $output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";
        }
        $output .= '</tr>';
    }
    $output .= '</table>';
    return $output;
}

In case it is unclear, query refers to mysqli_query, numRows refers to mysqli_num_rows, and fetchAssoc refers to mysqli_fetch_assoc. The database name is "shareride."

I know I am missing something in this line:

$output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";

but I just don't know what it is. Right now, I get all the table column titles displayed correctly, and I get the correct number of content rows, but I just can't populate those rows with the actual data from the database.

What am I missing? Any help would be GREATLY appreciated!

Answer 1

Get the data and column names from the same result set

  <?php
  $i = 0;
  $colNames = array();
  $data = array();
  while($row = ***_fetch_assoc($res)) //where $res is from the main query result not schema information
  {
     //get the column names into an array $colNames
     if($i == 0) //make sure this is done once
     {
        foreach($row as $colname => $val)
           $colNames[] = $colname;
     }

     //get the data into an array
     $data[] = $row;

     $i++;
  }

 ?>

UPDATE: Suggested by @YourCommonSense to replace the above code and it worked, simple and shorter - A WAY TO GET THE COLUMN NAMES/ARRAY KEYS WITHOUT LOOPING THROUGH LIKE I DID

  $data = array();
  while($row = mysql_fetch_assoc($res))
  {
     $data[] = $row;
  }

  $colNames = array_keys(reset($data))

Continued as before: Print the table

 <table border="1">
 <tr>
    <?php
       //print the header
       foreach($colNames as $colName)
       {
          echo "<th>$colName</th>";
       }
    ?>
 </tr>

    <?php
       //print the rows
       foreach($data as $row)
       {
          echo "<tr>";
          foreach($colNames as $colName)
          {
             echo "<td>".$row[$colName]."</td>";
          }
          echo "</tr>";
       }
    ?>
 </table>

Test Result

You can see how I separated the data retrieval from table generation. They are dependent of each other now and you can test your table generation without the database by populating the arrays with static data

You can also make them into separate functions.