使用 PHP 在 HTML 表格中显示 MySQL 结果

2021-12-25 00:00:00 php mysqli

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

代替

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

这有什么问题吗?

原帖:

我正在 PHP 类中编写一个函数来在表中显示查询结果.我没有自己构建任何表格，我希望它使用 PHP 完成所有工作.到目前为止，这是我的代码:

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

如果不清楚，query指的是mysqli_query，numRows指的是mysqli_num_rows，>fetchAssoc 指的是 mysqli_fetch_assoc.数据库名称是shareride".

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!

推荐答案

从同一个结果集中获取数据和列名

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

 ?>

更新:@YourCommonSense 建议替换上面的代码，它有效，简单且更短 - 一种无需像我那样循环即可获取列名/数组键的方法

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

  $colNames = array_keys(reset($data))

继续:打印表格

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

测试结果

您可以看到我如何将数据检索与表生成分离.它们现在相互依赖，您可以通过使用静态数据填充数组来测试没有数据库的表生成

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.

相关文章