Currently I am trying create an array multidimensional in php using two queries (Items and Categories). I made a search in the site but I did not found anything similar to what I am looking for. I appreciate if someone can help me using my code.
Please se bellow what am looking for and my code.
Tables:
TABLE Items;
+-----------------------------------+
| id | type | name |
+----------+------------+-----------+
| 1 | 4 | item_1 |
| 2 | 3 | item_2 |
| 3 | 2 | item_3 |
+-----------------------------------+
TABLE Categories;
+-----------------------------------+
| id | Item_id | name |
+----------+------------+-----------+
| 1 | 2 | Cat_a |
| 2 | 2 | Cat_b |
| 3 | 3 | Cat_x |
| 4 | 3 | Cat_z |
| 5 | 3 | Cat_b |
| 6 | 1 | Cat_b |
| 7 | 3 | Cat_y |
+-----------------------------------+
Result that I am looking for:
Array
(
[0] => Array
(
id => 1
name => Item_1
Type => 4
cats => Array
(
[6] => Cat_b
)
)
[1] => Array
(
id => 2
name => Item_2
Type => 3
cats => Array
(
[1] => Cat_a
[2] => Cat_b
)
)
[2] => Array
(
id => 3
name => Item_3
Type => 2
cats => Array
(
[3] => Cat_x
[4] => Cat_z
[5] => Cat_b
[7] => Cat_y
)
)
)
My code:
$result = mysqli_query($link, "SELECT * FROM Categories WHERE Item_id = '233'");
foreach ($result as $key => $value) {
$v[] = $value["id"];
}
foreach ($v as $key => $res) {
$query = mysqli_query($link, "SELECT * FROM Items WHERE category_id = '".$res."'");
foreach ($query as $k =>$att){
$var[$res][] = $att["name"];
}
}
echo '<pre>' . print_r($var,1) . '</pre>';
Use the following, Tested and working
$sql = "SELECT i.id,i.name,i.type,c.id AS CatKey,c.name As catName FROM `Items` AS i JOIN Categories AS c ON i.id=c.Item_id ORDER BY i.id ASC ";
$result = $conn->query($sql);
$res = array();
$i = 0;
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$ids = array_column($res, 'id');
if(in_array($row['id'], $ids)){
$res[$i-1]['cats'][$row['CatKey']] = $row['catName'];
}else{
$res[$i]['id'] = $row['id'];
$res[$i]['type'] = $row['type'];
$res[$i]['name'] = $row['name'];
$res[$i]['cats'] = array($row['CatKey'] => $row['catName']);
$i++;
}
}
}
echo '<pre>';
print_r($res);
Result:-
Array
(
[0] => Array
(
[id] => 1
[type] => 4
[name] => item_1
[cats] => Array
(
[6] => Cat_b
)
)
[1] => Array
(
[id] => 2
[type] => 3
[name] => item_2
[cats] => Array
(
[1] => Cat_a
[2] => Cat_b
)
)
[2] => Array
(
[id] => 3
[type] => 2
[name] => item_3
[cats] => Array
(
[3] => Cat_x
[4] => Cat_z
[5] => Cat_b
[7] => Cat_y
)
)
)
You can gather the data needed in one SQL statement. Then you basically do a break-sort to group the category with the items.
$qstr = "SELECT
a.`id` as `item_id`,
a.`type` as `item_type`,
a.`name` as `item_name`,
b.`id` as `cat_id`,
b.`name` as `cat_name`
FROM `Items` a
JOIN `Categories` b
ON a.`id` = b.`item_id`
WHERE Item_id = '233'";
$result = mysqli_query($link, $qstr);
$lastItem = '';
$rslt = array();
$rowno = -1;
while($row = mysqli_fetch_assoc($result)){
if($lastItem != $row['table_id']) {
$rowno++;
$rslt[$rowno] = array(
'id' => $row['item_id'],
'name' => $row['item_name'],
'Type' => $row['item_type'],
'cats' => array($row['cat_id'] => $row['cat_name'])
);
$lastItem = $row['table_id'];
} else {
$rslt[$rowno]['cats'][$row['cat_id']] = $row['cat_nsme'];
}
}
Do not serialize the queries. One query is enough.
$result = mysqli_query($link,
"select i.id, i.type, i.name, c.id as `cat_id`, c.name as `cat_name`
from Items i
join Categories c on c.Item_id=i.id
where i.id = '233'
order by i.id, c.id
");
$data = [];
$id = null;
$row_id = -1;
while($row = mysqli_fetch_assoc($result)){
if (is_null($id) || $id != $row['id']) {
$row_id++;
$id = $row['id'];
$data[] = [
'id' => $row['id'],
'name' => $row['name'],
'Type' => $row['type'],
'cats' => []
];
}
$data[$row_id]['cats'][$row['cat_id']] = $row['cat_name'];
}