Display any given MySQL SELECT query as a HTML table using PHP

Question

Sample Query 1 -

SELECT ID,NAME FROM USERS

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

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

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

Pass an array of keys to your function and loop to print the headings? Or you could loop through your query and use regex to extract the key for each item before an operator — Halfpint
– Halfpint, Commented May 2, 2015 at 11:28
Yes it is possible, but actually I'm letting users to run their own SQL queries, so it may not be the best solution here. — M_R_K
– M_R_K, Commented May 2, 2015 at 11:33

hynner · Accepted Answer · 2015-05-02 12:21:50Z

4

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

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.

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

edited May 2, 2015 at 12:21

answered May 2, 2015 at 11:57

hynner

1,3521 gold badge11 silver badges21 bronze badges

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

3 Comments

M_R_K Over a year ago

Yes it actually returns headings, but looks like it ignores AS statements in a SQL query!

Ofir Baruch Over a year ago

According to the manual it does consider the alias. (Check out the difference between name and orgname under Object properties in the manual)

hynner Over a year ago

@cyberboy: I tried it and it returns alias in name field and original column name in orgname field.

M_R_K · Accepted Answer · 2015-05-03 06:10:28Z

0

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

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.

edited May 3, 2015 at 6:10

answered May 3, 2015 at 1:06

M_R_K

6,3922 gold badges44 silver badges44 bronze badges

1 Comment

mickmackusa Over a year ago

This answer is outdated / not modern.

Collectives™ on Stack Overflow

Display any given MySQL SELECT query as a HTML table using PHP

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related