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

1. Never mix your database handling code with HTML output code. These are 2 totally different matters. Make your allResults function only return array with data, and then you can make another function to print in fancy way (and not in the database handler class).
2. You don't need information schema to get column name - you already have it in the returned array keys.
3. You don't need numRows either - use while() and foreach()
4. NEVER insert anything into query directly like you do with $cols - eventually it will lead to errors and injections.
5. Such a function, without accepting some parameters for the query, makes absolutely no sense especially in the context of migrating from mysql to mysqli - you are going yo use it as an old school mysql query inserting variables, not placeholders. So, it makes migration totally useless.
6. To know "Is there anything wrong with code", one have to run it, not watch. Run and debug your code, outputting key variables and making sure you can see errors occurred.

Answer 2

i'd try replacing the data part with something like:

while($row = $this->fetchAssoc($colResult))
{
  echo "<tr>";
  foreach($row as $value)
  {
    echo sprintf("<td>%s</td>",$value)
  }
  echo "</tr>";
}

i know it's not a proper answer, but it's really hard to read that code imho

Answer 3

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.