PHP数组到表的查询

Question

我正在玩 MySQL 并陷入困境。我有一个项目表和一个包含属性和值的表。SELECT i.name, ia.name, iav.value FROM `item_attrib_values` iavJOIN item_atributs ia ON iav.ia_ID = ia.IDJOIN items i ON iav.i_ID = i.IDJOIN item_class ic ON ic.ID = i.ic_IDWHERE ic.ID = 1这是我的查询，效果很好。MySQL 中的结果如下所示：Item    Attrib          Value   Rohr 1  diameter        16Rohr 1  Fluid Code      FWRohr 1  From            3Rohr 1  To              2Rohr 1  Subcontractor   1Rohr 1  Paint           A3Rohr 1  Insulation      HSRohr 2  diameter        80Rohr 2  Fluid Code      FWRohr 2  From             1Rohr 2  To               3Rohr 2  Subcontractor    1Rohr 2  Paint           A3Rohr 2  Insulation      HS我现在的问题是，如何将其放入如下所示的表中：item  diameter  Fluid Code  From  To Subcontr.  Paint  InsulationRohr1    16        FW        3    2     1         A3        HSRohr2    80        FW        1    3     1         A3        HS我的第一个想法是首先查询所有属性来构建表头。-> 工作正常，但如何将值分配给相应的表头。特别是如果 1 件物品没有设置其中一项属性？我的第二个想法是，是否可以通过一个查询构建表，因为所有数据都已经在数组中。但我还没弄清楚如何排序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 ) ) 进入我想要的表。

Answer 1

获取结果并将其转换为数组，其中每个元素都是具有所有属性的项目。收集所有标题，然后使用这些标题作为列来构建表格 - 这样，即使某些行缺少其中一个属性，它仍然会正确显示。

<?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（您可以根据需要显示它，此时我假设为 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>

它是这样的： https: //jsfiddle.net/z0k5teqv/。请注意，Rohhr 1 的直径为“-”，已从 $data 中注释掉以显示这一点。

Answer 2

在显示结果之前，您需要连接到数据库并执行查询。连接yoiu可以使用PDO、MySQLi。
要显示 SQL 结果，您可以
针对您的情况使用循环示例：

<?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>');

}

?>

Answer 3

考虑以下...

$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'];

}

输出：

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

        )

)