我正在玩 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 中的結(jié)果如下所示：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我現(xiàn)在的問題是，如何將其放入如下所示的表中：item diameter Fluid Code From To Subcontr. Paint InsulationRohr1 16 FW 3 2 1 A3 HSRohr2 80 FW 1 3 1 A3 HS我的第一個想法是首先查詢所有屬性來構(gòu)建表頭。-> 工作正常，但如何將值分配給相應(yīng)的表頭。特別是如果 1 件物品沒有設(shè)置其中一項屬性？我的第二個想法是，是否可以通過一個查詢構(gòu)建表，因為所有數(shù)據(jù)都已經(jīng)在數(shù)組中。但我還沒弄清楚如何排序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 ) ) 進入我想要的表。
3 回答
三國紛爭
TA貢獻1804條經(jīng)驗 獲得超7個贊
獲取結(jié)果并將其轉(zhuǎn)換為數(shù)組，其中每個元素都是具有所有屬性的項目。收集所有標題，然后使用這些標題作為列來構(gòu)建表格 - 這樣，即使某些行缺少其中一個屬性，它仍然會正確顯示。
<?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"
}
}
構(gòu)建的 HTML（您可以根據(jù)需要顯示它，此時我假設(shè)為 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 中注釋掉以顯示這一點。
元芳怎么了
TA貢獻1798條經(jīng)驗 獲得超7個贊
在顯示結(jié)果之前，您需要連接到數(shù)據(jù)庫并執(zhí)行查詢。連接yoiu可以使用PDO、MySQLi。
要顯示 SQL 結(jié)果，您可以
針對您的情況使用循環(huán)示例：
<?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>');
}
?>
GCT1015
TA貢獻1827條經(jīng)驗 獲得超4個贊
考慮以下...
$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
)
)
- 3 回答
- 0 關(guān)注
- 187 瀏覽
添加回答
舉報
0/150
提交
取消