使用PHP将任何给定的MySQL SELECT查询显示为HTML表

Sample Query 1 -

查询样例1 -

SELECT ID,NAME FROM USERS

Sample Query 2 -

查询样例2 -

SELECT Orders.OrderID as ID, Customers.CustomerName as Name, Orders.OrderDate as Date
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

How can I load title headings to an array in PHP? I mean ID,NAME in Query 1 and ID,Name,Date in Query 2

如何在PHP中将标题加载到数组中?我的意思是ID，查询1中的名字和ID，查询2中的名字，日期

This is what I'm trying to do :

这就是我想做的:

I'm creating a PHP function to make HTML table automatically from any given MySQL-Select query

我正在创建一个PHP函数，以便从任何给定的MySQL-Select查询中自动生成HTML表

This where I'm up to now

这就是我现在所做的

function createTable($query) {
    $sql_link = Connect_MySQLi_DB();// Database Connection
    $sql_link->set_charset("utf8");
    $result = $sql_link->query($query);
    $headings = array('ID','Name','Date');//I need this array to create automatically
    echo '<table>';
    echo '<tr>';
    for ($x = 0; $x <= (count($headings) - 1); $x++) {
        echo '<th>'.$headings[$x].'</th>';
    }
    echo '<tr>';
    while ($row = $result->fetch_object()) {
        echo '<tr>';
        for ($x = 0; $x <= (count($headings) - 1); $x++) {
            echo '<td>' . $row->$headings[$x] . '</td>';
        }
        echo '<tr>';
    }
    echo '</table>';
}

So I need to create that $heading array automatically. If I can do that, this function can display any MySQL-Select query as a HTML table

我需要自动创建$heading数组。如果我可以这样做，这个函数可以将任何MySQL-Select查询显示为HTML表

2 个解决方案

#1

If you are using mysqli then fetch_fields seems to do the job.

如果您正在使用mysqli，那么fetch_fields似乎可以完成这项工作。

EDIT: Example:

编辑:例如:

$res = $db->query('SELECT A.id,A.a,A.id + B.id AS idd FROM A natural join B');
var_dump($res->fetch_fields());

Returns information about columns A.id, A.a and about the idd column (both tables have other columns). I omitted some fields from the output in order to make it shorter.

返回关于列A的信息。id,一个。a和关于idd列(两个表都有其他列)。为了使输出更短，我省略了输出中的一些字段。

array(3) {
  [0]=>
  object(stdClass)#3 (13) {
    ["name"]=>
    string(2) "id"
    ["orgname"]=>
    string(2) "id"
    ["table"]=>
    string(1) "A"
    ["orgtable"]=>
    string(1) "A"
    // more fields here
  }
  [1]=>
  object(stdClass)#4 (13) {
    ["name"]=>
    string(1) "a"
    ["orgname"]=>
    string(1) "a"
    ["table"]=>
    string(1) "A"
    ["orgtable"]=>
    string(1) "A"
    // more fields here
  }
  [2]=>
  object(stdClass)#5 (13) {
    ["name"]=>
    string(3) "idd"
    ["orgname"]=>
    string(0) ""
    ["table"]=>
    string(0) ""
    ["orgtable"]=>
    string(0) ""
    // more fields here
  }
}

#2

Finally this is how I made this function to work (Thanks Hynner for your excellent suggestion),

最后，我是如何让这个功能发挥作用的(谢谢Hynner的建议)，

function createTable_from_sql_select_query($query) {
    $sql_link = Connect_MySQLi_DB();// Database connection
    $sql_link->set_charset("utf8");
    $result = $sql_link->query($query);

    // Adding Missing array_column Function for Old PHP Versions (<5.5)
    if (!function_exists('array_column')) {

        function array_column($array, $column) {
            $ret = array();
            foreach ($array as $row)
                $ret[] = $row[$column];
            return $ret;
        }

    }

    $headings = json_decode(json_encode($result->fetch_fields()), true);
    $headings = array_column($headings, 'name');

      $return = '<table>';
      $return .= '<thead><tr>';
      for ($x = 0; $x <= (count($headings) - 1); $x++) {
      $return .= '<th>' . ucwords(str_replace('_', ' ', (strtolower($headings[$x])))) . '</th>';
      }
      $return .= '</tr></thead><tbody>';
      while ($row = $result->fetch_object()) {
      $return .= '<tr>';
      for ($x = 0; $x <= (count($headings) - 1); $x++) {
      $return .= '<td>' . $row->$headings[$x] . '</td>';
      }
      $return .= '</tr>';
      }
      $return .= '</tbody></table>';

      return $return;
}

Now anyone can convert any SQL select query to a HTML table.

现在任何人都可以将任何SQL select查询转换为HTML表。

#1