Display data from mysql columns as rows in PHP

Question

Im trying to create a website to display a booking system's table. I have a table in mysql database which containing the data like this:

The second column is the post_id. The booking details are provided with the same post_id.

I would like to create an html table which contains the same booking details in one row like this:

<html>
<head>
  <title>Booking</title>
  <style>
    table {
      border-collapse: collapse;
      width: 100%;
      color: #588c7e;
      font-family: monospace;
      font-size: 25px;
      text-align: left;
    } 
    th {
      background-color: #588c7e;
      color: white;
    }
    tr:nth-child(even) {background-color: #f2f2f2}
  </style>
</head>
<body>
  <table>
    <tr>
      <th>Field14</th> 
      <th>Field15</th> 
      <th>Field16</th>
    </tr>
    <?php
      $conn = mysqli_connect("localhost", "admin", "", "test");
      // Check connection
      if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
      } 
      $sql = "SELECT _field_14, _field_15, _field_16 FROM booking";
      $result = $conn->query($sql);

    ?>
  </table>
</body>
</html>

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Igor Ilic, Jul 13 '18 at 07:09
I know but is it not possible to do without run an sql query? — Bence, Jul 13 '18 at 07:14

mickmackusa · Accepted Answer · 2018-07-19T11:48:47.600

0

Don't be scared off by the pivot query technique. It's as simple as GROUPing and then calling MAX() on simple case statements.

Query:

SELECT 
    `post_id`,
    MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field 14`,
    MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field 15`,
    MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field 16`
FROM `booking`
GROUP BY `post_id`
HAVING field14 IS NOT NULL AND field15 IS NOT NULL AND field16 IS NOT NULL
ORDER BY `post_id`;

*edit: The HAVING clause omits generated rows that are fully comprised of NULL values -- as requested by the OP. Then just process the resultset rows as you would normally.

Resultset:

post_id |   field 14  | field 15  |  field 16
--------|-------------|-----------|-----------
  490   |     IND     |   LHSM    | 2018-07-07
  491   |     ERK     |   LHKE    | 2018-07-08

Here is a code snippet, packed with error checkpoints to show you how to process the resultset:

echo '<body>';
if (!$conn = new mysqli('localhost', 'admin', '', 'test')) {
    echo 'Connection Error'; // $conn->connect_error;  // never show the exact error message to the public
} else {
    $pivot = "SELECT 
                `post_id`,
                MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field14`,
                MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field15`,
                MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field16`
            FROM `booking`
            GROUP BY `post_id`
            ORDER BY `post_id`";
    if (!$result = $conn->query($pivot)) {
        echo 'Syntax Error'; // $conn->error;  // never show the exact error message to the public
    } else {
        if (!$result->num_rows) {
            echo 'No Rows Returned From Pivot Query';
        } else {
            echo '<table>';
                echo '<tr><th>Field14</th><th>Field15</th><th>Field16</th></tr>';
                while ($row = $result->fetch_assoc()) {
                    echo "<tr><td>{$row['field14']}</td><td>{$row['field15']}</td><td>{$row['field16']}</td></tr>";
                }
            echo '</table>';
        }
        // $result->free();  // just a consideration
    }
    // $conn->close();  // just a consideration
}
echo '</body>';

edited Jul 19 '18 at 11:48

answered Jul 13 '18 at 08:37

mickmackusa

43,625
12
83
136

Thank you, yeah I know that I can do it with sql query but the values of the database are constantly changing. Is it possible to run and display the querys result in html table? – Bence Jul 13 '18 at 08:40
I spent a good 15 minutes trying to find the perfect duplicate to close with, but all of the others were more complicated. This question is about as basic as it comes. – mickmackusa Jul 13 '18 at 08:40
You have a dynamic/infinite number of `metar_key` values? You should clarify your question. Making an html table with an unknown number of columns is trickier. – mickmackusa Jul 13 '18 at 08:41
The possible values of the metar_key is _field_14,15,16,17,18,21, _form_id and _seq_num but in the html table I need only the _field_14-18 – Bence Jul 13 '18 at 08:45
@Bence fixed: https://www.db-fiddle.com/f/jz4z6GJ4ixaKkXaqEz95jK/0 You don't even need a WHERE clause, just write the additional CASEs. See how `_field_21` is not included in the resultset? – mickmackusa Jul 13 '18 at 08:49
No. It isnt included. – Bence Jul 13 '18 at 08:54
My original question was how can I do this table without sql query, only in php. But as I see I have to do it with query. – Bence Jul 13 '18 at 08:58
Correct, you can't "pull database data" without a query. – mickmackusa Jul 13 '18 at 08:59
1

@Bence If you are using Brian's solution, when you have missing values from one of the nominated fields, you will generate warnings. In addition to that poor practice, Brian's answer is making iterated query calls and that is not best practice. Is there something that you don't understand about my answer? I am showing you best practice. – mickmackusa Jul 16 '18 at 00:49
First, thank you for your solution. I ran it in phpmyadmin and it worked but I dont know how can I "display" it in html table. So thats the reason why Im using Brian's one. @mickmackusa – Bence Jul 16 '18 at 15:16
I've tested and I have a little bit problem. In the html table I have a lots of empty row. Here it is an image where you can this: [link](https://i.snag.gy/AOpjDY.jpg) @mickmackusa – Bence Jul 19 '18 at 11:27
@Bence how would you like these rows to be treated? Ignore them entirely? – mickmackusa Jul 19 '18 at 11:28
1

or maybe you could test my best blind-guess: (after the GROUP BY clause and before the ORDER BY clause) `HAVING field14 IS NOT NULL AND field15 IS NOT NULL AND field16 IS NOT NULL AND field17 IS NOT NULL AND field18 IS NOT NULL`. I would much rather provide a smart query-based solution rather than a php condition -- using php wouldn't be as clever/clean/direct. – mickmackusa Jul 19 '18 at 11:42
Thank you very very much for your help! Its working! :) – Bence Jul 19 '18 at 11:45

Brian biggs · Answer 2 · 2018-07-13T09:39:25.567

Try this...

<html>
<head>
  <title>Booking</title>
  <style>
    table {
      border-collapse: collapse;
      width: 100%;
      color: #588c7e;
      font-family: monospace;
      font-size: 25px;
      text-align: left;
    } 
    th {
      background-color: #588c7e;
      color: white;
    }
    tr:nth-child(even) {background-color: #f2f2f2}
  </style>
</head>
<body>
  <table>
    <tr>
      <th>Post_id</th>
      <th>Field14</th>
      <th>Field15</th> 
      <th>Field16</th>
    </tr>
    <?php
      $conn = mysqli_connect("10.0.1.1", "user", "pwd", "mydb");
      // Check connection
      if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error); 
      }

      $sql = "SELECT DISTINCT(POST_ID) from booking";
      $result = $conn->query($sql);

if ($result->num_rows > 0) {

      while ($row = $result->fetch_assoc()) {

        $idsql = "select metar_key, meta_value from booking where POST_ID=\"".$row["POST_ID"]."\"";
        $idresult = $conn->query($idsql);

    while ($id = $idresult->fetch_assoc()) {
      $var[$id["metar_key"]] = $id["meta_value"];
        }

        echo "<tr>\n";
        echo "<td>".$row["POST_ID"]."</td>\n";
        echo "<td>".$var["_field_14"]."</td>\n";
        echo "<td>".$var["_field_15"]."</td>\n";
        echo "<td>".$var["_field_16"]."</td>\n";
        echo "</tr>\n";
      }
}


/*
      $sql = "SELECT _field_14, _field_15, _field_16 FROM booking";
      $result = $conn->query($sql);
*/

    ?>
  </table>
</body>
</html>

score -2 · Answer 3 · answered Jul 13 '18 at 07:24

I don't know if i got it right but i think you have to make a join. Let's assume the first table is table A, and the table which contains with the booking details is table B. You request will be :

SELECT post_id, field14, field15, field16 FROM B WHERE post_id IN (SELECT post_id FROM A);

score -2 · Answer 4 · answered Jul 13 '18 at 08:04

    <html>
    <head>
      <title>Booking</title>
      <style>
        table {
          border-collapse: collapse;
          width: 100%;
          color: #588c7e;
          font-family: monospace;
          font-size: 25px;
          text-align: left;
        } 
        th {
          background-color: #588c7e;
          color: white;
        }
        tr:nth-child(even) {background-color: #f2f2f2}
      </style>
    </head>
    <body>
      <table>
        <tr>
          <th>Field14</th> 
          <th>Field15</th> 
          <th>Field16</th>
        </tr>
    <?php while($res = mysqli_fetch_array($result)) { 
    echo'<tr>';
    echo"<td>".$res['_field_14']."</td>";
    echo"<td>".$res['_field_15']."</td>";
    echo"<td>".$res['_field_16']."</td>";
}
   ?>
      </table>
    </body>
    </html>
    <?php
      $conn = mysqli_connect("localhost", "admin", "", "test");
      // Check connection
      if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
      } 
      $sql = "SELECT _field_14, _field_15, _field_16 FROM booking";
      $result = $conn->query($sql);

    ?>

This code. Hope it helps

score -2 · Answer 5 · answered Jul 13 '18 at 09:18

<?php
$row = array(
    array(
        'meta_id' => 1556,
        'post_id' => 490,
        'metar_key' => '_field_14',
        'meta_value' => 'IND'
    ),
    array(
        'meta_id' => 1557,
        'post_id' => 490,
        'metar_key' => '_field_15',
        'meta_value' => 'LHSM'
    ),
    array(
        'meta_id' => 1558,
        'post_id' => 490,
        'metar_key' => '_field_16',
        'meta_value' => '2018-07-07'
    ),
    array(
        'meta_id' => 1558,
        'post_id' => 490,
        'metar_key' => '_field_16',
        'meta_value' => '2018-07-07'
    ),
    array(
        'meta_id' => 1559,
        'post_id' => 491,
        'metar_key' => '_field_14',
        'meta_value' => 'ERK'
    ),
    array(
        'meta_id' => 1560,
        'post_id' => 491,
        'metar_key' => '_field_15',
        'meta_value' => 'LHKE'
    ),
    array(
        'meta_id' => 1561,
        'post_id' => 491,
        'metar_key' => '_field_16',
        'meta_value' => '2018-07-08'
    )
);

$table_tr = array();
$table_td = array('POST_ID');
foreach($row as $k => $v) {
    if(!in_array(strtoupper(substr($v['metar_key'], 1)), $table_td)) {
        $table_td[] = strtoupper(substr($v['metar_key'], 1));
    }
    if(!isset($table_tr[$v['post_id']]['POST_ID'])) {
        $table_tr[$v['post_id']]['POST_ID'] = $v['post_id'];
    }
    $table_tr[$v['post_id']][strtoupper(substr($v['metar_key'], 1))] = $v['meta_value'];
}
?>

<!DOCTYPE HTML>
<html>
    <head>
        <style>
            table,table tr th, table tr td { border:1px solid #000; }
            table { width: 200px; min-height: 25px; line-height: 25px; text-align: center; border-collapse: collapse; }   
        </style>
    </head>
    <body>
        <table>
            <tr>
                <?php
                foreach($table_td as $k => $v) {
                    echo '<th>'.$v.'</th>';
                }
                ?>
            </tr>
            <?php
            foreach($table_tr as $tr) {
                echo '<tr>';
                foreach($table_td as $k => $v) {
                    echo '<td>'.(isset($tr[$v]) ? $tr[$v] : '-').'</td>';
                }
                echo '</tr>';
            }
            ?>
        </table>
    </body>
</html>

The result like this. enter image description here

$row is your mysql database data info also you can get it by your code.

Display data from mysql columns as rows in PHP

5 Answers5

Linked