associative array

[ams]

hai, i really need some help

I have code :

<?php
$conn     = mysqli_connect("localhost", "ams", "", "test");
$sql2     = "SELECT location_name from location inner join hardware on location.location_id = hardware.location_id where hardware_type_id='CAMERA' group by location_name order by location_name";
$result2  = mysqli_query($conn,$sql2);
while ($row = mysqli_fetch_assoc($result2)){
  $arr_locations[] = $row['location_name'];
}
$sql3    = "SELECT brand from hardware inner join location on hardware.location_id = location.location_id where hardware_type_id='CAMERA' group by brand order by brand";
$result3 = mysqli_query($conn,$sql3);
while ($row = mysqli_fetch_assoc($result3)){
  $arr_brands[] = $row['brand'];
}

foreach ($arr_locations as $location){
    foreach ($arr_brands as $brand){
        $query="select count(hardware_id) from hardware inner join location on hardware.location_id = location.location_id where location_name = '".$location."' and brand = '".$brand."'";
        $result = mysqli_query($conn, $query);
        $row = $result->fetch_array(MYSQLI_NUM);
        $locations = array($location);
        if(is_array($row)){
    foreach($row as $rows => $locations){
        echo"$locations $rows </br> ";
    }
}
                                            
}
       
}
?>

and the output:

0 0
4 0
0 0
6 0
3 0
3 0
5 0
5 0
0 0
5 0
0 0
5 0
1 0
4 0
0 0
5 0
0 0
3 0
1 0
0 0

 

expected output :

0 4

0 6

3 3

5 5

0 5

0 5

1 4

0 5

0 3

1 0

may someone help me to fix it?

thanks

 

[Barand]

Don't run queries inside loops - use joins and a single query

EG. If you are wanting the totals of each camera brand at each location from this sample data

  location_id  location_name             hardware_id    location_id   harware_type_id    brand
+-------------+---------------+         +-------------+-------------+------------------+---------+
|           1 | Athens        |         |           1 |           1 | LAPTOP           | ACER    |
|           2 | Birmingham    |         |           2 |           1 | LAPTOP           | DELL    |
|           3 | Copenhagen    |         |           3 |           1 | CAMERA           | NIKON   |
|           4 | Delhi         |         |           4 |           1 | CAMERA           | NIKON   |
|           5 | Edinburgh     |         |           5 |           1 | CAMERA           | NIKON   |
+-------------+---------------+         |           6 |           1 | CAMERA           | CANON   |                                  
                                        |           7 |           1 | CAMERA           | CANON   |
                                        |           8 |           2 | LAPTOP           | SONY    |
                                        |           9 |           2 | LAPTOP           | SONY    |
                                        |          10 |           2 | CAMERA           | MINOLTA |
                                        |          11 |           2 | CAMERA           | LEICA   |
                                        |          12 |           2 | CAMERA           | LEICA   |
                                        |          13 |           2 | CAMERA           | CANON   |
                                        |          14 |           2 | CAMERA           | CANON   |
                                        |          15 |           3 | PHONE            | SONY    |
                                        |          16 |           3 | PHONE            | SONY    |
                                        |          17 |           3 | CAMERA           | SONY    |
                                        |          18 |           3 | CAMERA           | SONY    |
                                        |          19 |           3 | CAMERA           | SAMSUNG |
                                        |          20 |           3 | CAMERA           | SAMSUNG |
                                        |          21 |           3 | CAMERA           | SONY    |
                                        |          22 |           4 | LAPTOP           | HP      |
                                        |          23 |           4 | LAPTOP           | HP      |
                                        |          24 |           4 | CAMERA           | NIKON   |
                                        |          25 |           4 | CAMERA           | CANON   |
                                        |          26 |           4 | CAMERA           | CANON   |
                                        |          27 |           4 | CAMERA           | CANON   |
                                        |          28 |           4 | CAMERA           | CANON   |
                                        |          29 |           5 | LAPTOP           | HP      |
                                        |          30 |           5 | LAPTOP           | HP      |
                                        |          31 |           5 | PHONE            | SAMSUNG |
                                        |          32 |           5 | PHONE            | SONY    |
                                        +-------------+-------------+------------------+---------+

then you can replace all your code with a single query

SELECT
      l.location_name
    , h.brand
    , COUNT(hardware_id) as stock
FROM 
      location l
        INNER JOIN
      hardware h USING (location_id)
WHERE 
      h.hardware_type_id = 'CAMERA'
GROUP BY 
      location_id, brand;  

+---------------+---------+-------+
| location_name | brand   | stock |
+---------------+---------+-------+
| Athens        | CANON   |     2 |
| Athens        | NIKON   |     3 |
| Birmingham    | CANON   |     2 |
| Birmingham    | LEICA   |     2 |
| Birmingham    | MINOLTA |     1 |
| Copenhagen    | SAMSUNG |     2 |
| Copenhagen    | SONY    |     3 |
| Delhi         | CANON   |     4 |
| Delhi         | NIKON   |     1 |
+---------------+---------+-------+