Query to PHP array to table

Question

I am playing around MySQL and got stuck.

I have an item Table and a table with attributes and values.

SELECT i.name, ia.name, iav.value FROM `item_attrib_values` iav
JOIN item_atributs ia ON iav.ia_ID = ia.ID
JOIN items i ON iav.i_ID = i.ID
JOIN item_class ic ON ic.ID = i.ic_ID
WHERE ic.ID = 1

This is my Query and it works fine.

The Result in MySQL looks like this:

Item    Attrib          Value   
Rohr 1  diameter        16
Rohr 1  Fluid Code      FW
Rohr 1  From            3
Rohr 1  To              2
Rohr 1  Subcontractor   1
Rohr 1  Paint           A3
Rohr 1  Insulation      HS
Rohr 2  diameter        80
Rohr 2  Fluid Code      FW
Rohr 2  From             1
Rohr 2  To               3
Rohr 2  Subcontractor    1
Rohr 2  Paint           A3
Rohr 2  Insulation      HS

My problem is now, how do I get this into a Table that looks like this:

item  diameter  Fluid Code  From  To Subcontr.  Paint  Insulation
Rohr1    16        FW        3    2     1         A3        HS
Rohr2    80        FW        1    3     1         A3        HS

My first Idea was to QUery all Attributes first to build the Table head. -> Works fine but how do I assign the values to the corresponding tableheads. Especialy if 1 Item does not have one of the attribs set?

My second Idea was, if it is possible to build the Table out of one Query because all data are already in the array.

But I haven't figured out how to sort this

Array ( [0] => Array ( [Item] => Rohr 1 [attrib] => diameter [Value] => 16 ) [1] => Array ( [Item] => Rohr 1 [attrib] => Fluid Code [Value] => FW ) [2] => Array ( [Item] => Rohr 1 [attrib] => From [Value] => 3 ) [3] => Array ( [Item] => Rohr 1 [attrib] => To [Value] => 2 ) [4] => Array ( [Item] => Rohr 1 [attrib] => Subcontractor [Value] => 1 ) [5] => Array ( [Item] => Rohr 1 [attrib] => Paint [Value] => A3 ) [6] => Array ( [Item] => Rohr 1 [attrib] => Insulation [Value] => HS ) [7] => Array ( [Item] => Rohr 2 [attrib] => diameter [Value] => 80 ) [8] => Array ( [Item] => Rohr 2 [attrib] => Fluid Code [Value] => FW ) [9] => Array ( [Item] => Rohr 2 [attrib] => From [Value] => 1 ) [10] => Array ( [Item] => Rohr 2 [attrib] => To [Value] => 3 ) [11] => Array ( [Item] => Rohr 2 [attrib] => Subcontractor [Value] => 1 ) [12] => Array ( [Item] => Rohr 2 [attrib] => Paint [Value] => A3 ) [13] => Array ( [Item] => Rohr 2 [attrib] => Insulation [Value] => HS ) )

Into the Table I want.

Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Jul 17 '20 at 12:50
What am i missing? My i`ve postet the Query and the Result and the Array My Question is, how do i sort the array so that i can get the table out — El Gonzo, Jul 17 '20 at 12:55
If I have to explain the link, it rather seems to diminish the point of providing the link, wouldn't you agree? — Strawberry, Jul 17 '20 at 12:56
I dont know which create and select could help with sorting an php array which i have posted. — El Gonzo, Jul 17 '20 at 13:18
Well, I'd go with CREATE and INSERT statements for `item_atributs`, `items`, and `item_class` , together with the desired result from your query — Strawberry, Jul 17 '20 at 13:20

score 0 · Answer 1 · edited Jul 18 '20 at 13:58

Consider the following...

$data = array();

$result = mysqli_query($db,$query);

while($row = mysqli_fetch_assoc($result)){
    $data[] = $row;
}

foreach($data as $v){
    $new_array[$v['item']][$v['attrib']] = $v['value'];
}

Outputs:

Array
(
    [Rohr 1] => Array
        (
            [diameter] => 16
            [Fluid Code] => FW
            [From] => 3
            [Insulation] => HS
            [Paint] => A3
            [Subcontractor] => 1
            [To] => 2
        )

    [Rohr 2] => Array
        (
            [diameter] => 80
            [Fluid Code] => FW
            [From] => 1
            [Insulation] => HS
            [Paint] => A3
            [Subcontractor] => 1
            [To] => 3
        )

)

score 0 · Answer 2 · answered Jul 17 '20 at 14:04

Before display results you need connect to database and execute query. For connect yoiu can use PDO, MySQLi.
For display SQL results you can use loop
Example for your case:

<?php
// make connect to database
$db_host = 'localhost';// This MySQL host, for XAMPP/LAMPP/etc. that will be 'localhost'
$db_user = 'my_user';// This is user for your DB, you authorize in PHPMyAdmin with this user
$db_password = 'qwerty';// This is password for your DB, you authorize in PHPMyAdmin with this password
$db_name = 'item_attrib_values';// This is your DB name
$db = new mysqli($db_host, $db_user, $db_password, $db_name);
if ($db->connect_errno) {
    // if you script can't connect to database will be abort execution and displayed message 'Connection failed'
    exit('Connection failed');
}

// before execute query you need make "prepare", this will be analyze SQL query
// instead of data you should use `?` this is placeholder, MySQLi understand what instead of this symbol should be real data
// Important notice: don't wrap placeholder `"?"`, `'?'` - this is string data but `?` - placeholder
$stmt = $db->prepare("SELECT i.name, ia.name, iav.value FROM `item_attrib_values` iav
JOIN item_atributs ia ON iav.ia_ID = ia.ID
JOIN items i ON iav.i_ID = i.ID
JOIN item_class ic ON ic.ID = i.ic_ID
WHERE ic.ID = ?");
if(!$stmt) {
    // if you script doesn't make "prepare" for SQL query will be abort script execution and displayed message 'Preparation failed'
    exit('Preparation failed');
}
// You completed preparation, but now MySQLi doesn't know about data for execute
// Let's get data for MySQLi

if(!$stmt->bind_param("i", 1)){
    // binding data for MySQLi, first argument is type ("i" that's integer) and second argument is data
    // if bind not executed You abort script and get error message
    exit('Binding failed');
}

$results = null;
$row = null;
// That's all, you can run execution
if(!$stmt->execute();) {
    // Run execution, if execution failed $result will be `false`
    exit('Execution failed');
} ele {
    // If execution success you can get results
    $results = $stmt->get_result();
}
$stmt->close();// Closing statement, that's good practice

// OK, you have $result, but now you can't get data, you need convert this to array
$result
if(!is_null($result)) {
    // check, if you can results
    echo('<table>');
    while ($row = $result->fetch_all()) {
        // this loop will be execute while you have results
        // print "rows"
        echo('<tr>');
        foreach ($row as &$value) {
            // print "columns" with your data
            echo('<td>'.$value.'</td>');
        }
        echo('</tr>');
    }
    echo('</table>');
}
?>

You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Jul 18 '20 at 13:59

score 0 · Accepted Answer · answered Jul 17 '20 at 14:14

Get your result and transform it to an array where each element is an item with all attribs. Gather all headers and then build the table using those headers as columns - this way even if one of the attribs is missing for some rows it will still be displayed correctly.

<?php

$data = [ 
//[ 'Item' => 'Rohr 1' , 'attrib' => 'diameter' , 'value' => '16' ] ,
[ 'Item' => 'Rohr 1' , 'attrib' => 'Fluid Code' , 'value' => 'FW' ], 
[ 'Item' => 'Rohr 1' , 'attrib' => 'From' , 'value' => '3' ] ,
[ 'Item' => 'Rohr 1' , 'attrib' => 'To' , 'value' => '2' ] ,
[ 'Item' => 'Rohr 1' , 'attrib' => 'Subcontractor' , 'value' => '1' ], 
[ 'Item' => 'Rohr 1' , 'attrib' => 'Paint' , 'value' => 'A3' ] ,
[ 'Item' => 'Rohr 1' , 'attrib' => 'Insulation' , 'value' => 'HS' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'diameter' , 'value' => '80' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'Fluid Code' , 'value' => 'FW' ], 
[ 'Item' => 'Rohr 2' , 'attrib' => 'From' , 'value' => '1' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'To' , 'value' => '3' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'Subcontractor' , 'value' => '1' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'Paint' , 'value' => 'A3' ] ,
[ 'Item' => 'Rohr 2' , 'attrib' => 'Insulation' , 'value' => 'HS' ] ,
]; 

$headers = ['item'];
$result = [];
foreach($data as $row) {
  if (!isset($result[$row['Item']])) {
    $result[$row['Item']] = ['item' => $row['Item']];
  }
  
  $result[$row['Item']][$row['attrib']] = $row['value'];

  if (!in_array($row['attrib'], $headers)) {
    $headers[] = $row['attrib'];
  }
}

var_dump($headers);
var_dump($result);

$html = '<table><tr>';
foreach ($headers as $header) {
    $html .= '<th>'.$header.'</th>';
}
$html .= '</tr>';

foreach ($result as $row) {
  $html .= '<tr>';
  foreach ($headers as $header) {
    $html .= '<td>'.($row[$header] ?? '-').'</td>';
  }
  $html .= '</tr>';
}
$html .= '</table>';

echo $html;

$headers:

array(8) {
  [0]=>
  string(4) "item"
  [1]=>
  string(10) "Fluid Code"
  [2]=>
  string(4) "From"
  [3]=>
  string(2) "To"
  [4]=>
  string(13) "Subcontractor"
  [5]=>
  string(5) "Paint"
  [6]=>
  string(10) "Insulation"
  [7]=>
  string(8) "diameter"
}

$result:

array(2) {
  ["Rohr 1"]=>
  array(7) {
    ["item"]=>
    string(6) "Rohr 1"
    ["Fluid Code"]=>
    string(2) "FW"
    ["From"]=>
    string(1) "3"
    ["To"]=>
    string(1) "2"
    ["Subcontractor"]=>
    string(1) "1"
    ["Paint"]=>
    string(2) "A3"
    ["Insulation"]=>
    string(2) "HS"
  }
  ["Rohr 2"]=>
  array(8) {
    ["item"]=>
    string(6) "Rohr 2"
    ["diameter"]=>
    string(2) "80"
    ["Fluid Code"]=>
    string(2) "FW"
    ["From"]=>
    string(1) "1"
    ["To"]=>
    string(1) "3"
    ["Subcontractor"]=>
    string(1) "1"
    ["Paint"]=>
    string(2) "A3"
    ["Insulation"]=>
    string(2) "HS"
  }
}

HTML built (you can display it as you want at this point i assumed html):

<table><tr><th>item</th><th>Fluid Code</th><th>From</th><th>To</th><th>Subcontractor</th><th>Paint</th><th>Insulation</th><th>diameter</th></tr><tr><td>Rohr 1</td><td>FW</td><td>3</td><td>2</td><td>1</td><td>A3</td><td>HS</td><td>-</td></tr><tr><td>Rohr 2</td><td>FW</td><td>1</td><td>3</td><td>1</td><td>A3</td><td>HS</td><td>80</td></tr></table>

This is how it looks like: https://jsfiddle.net/z0k5teqv/ . Notice "-" for diameter for Rohhr 1 that is commented out from $data to show this.

Sry for the late answer. Thx for this. Helps me alot to understand how to work this with the array. THX — El Gonzo, Jul 27 '20 at 08:18

Query to PHP array to table

3 Answers3