PHP SQL table, values with same id in same row

Question

I have this SQL table and I would like to display the data to html table like this:

I have this php code: My question is how can I do that all values with same post_id be in same row. The pictures are illustrations. Thank you for your help in advance!

<!DOCTYPE html>
<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>

I think this is a very similar question: https://stackoverflow.com/questions/1241178/mysql-rows-to-columns - you're trying to turn your columns to rows it seems to me. I will try to make an SQL fiddle with your particular example. — dmgig, Jul 12 '18 at 19:57
Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — FilipRistic, Jul 12 '18 at 23:36

score 0 · Answer 1 · answered Jul 12 '18 at 20:10

Here is a fiddle: http://sqlfiddle.com/#!9/5f1584/1 (i changed the data a bit, being lazy).

I used haudoing's answer here, with some adjustments (mostly the group by). It seemed the most straight-forward. https://stackoverflow.com/a/13947385/399696

SELECT 
T1.post_id, 
T2.meta_value AS _field_14, 
T3.meta_value AS _field_15, 
T4.meta_value AS _field_16
FROM docs AS T1
LEFT JOIN docs T2 ON T2.post_id=T1.post_id AND T2.metar_key='_field_14'
LEFT JOIN docs T3 ON T3.post_id=T1.post_id AND T3.metar_key='_field_15'
LEFT JOIN docs T4 ON T4.post_id=T1.post_id AND T4.metar_key='_field_16'
GROUP BY post_id

This is the result:

post_id   _field_14   _field_15     _field_16
490       IND         LHSM          2018-07-07
491       ERK         LHKE          2018-07-08

Is it not possible to do it in php? – Bence Jul 13 '18 at 06:50 — Bence, Jul 13 '18 at 06:50

score 0 · Accepted Answer · answered Jul 16 '18 at 14:06

In PHP:

$newRows = array();
foreach($rows as $row){
  $newRows[$row['post_id']][$row['metar_key']] = $row['meta_value'];
}

Then

foreach($newRows as $post_id => $row){
  $html[] = '<tr>';
  $html[] = '<td>';
  $html[] = $post_id;
  $html[] = '</td>';
  $html[] = '<td>';
  $html[] = $row['field_14'];
  $html[] = '</td>';
  $html[] = '<td>';
  $html[] = $row['field_15'];
  $html[] = '</td>';
  $html[] = '<td>';
  $html[] = $row['field_16'];
  $html[] = '</td>';
  $html[] = '</tr>';
}

echo join("\n", $html);

Example: http://sandbox.onlinephpfunctions.com/code/96df0e56361dbc4c9f84be9a5993deef49b208fc

PHP SQL table, values with same id in same row

2 Answers2