-1

I'm trying to create a schedule. By selecting the date, I will have to display by company all the products that expire at that moment, let's take for example these two databases:

Company DB:

+------------+-----------+
| id_company | name      |
+------------+-----------+
| 1          | Microsoft |
+------------+-----------+
| 2          | Apple     |
+------------+-----------+
| 3          | IBM       |
+------------+-----------+

Products DB:

+-------------+----------------+---------------+---------------+
| id_products | id_pro_company | name_products | control_dates |
+-------------+----------------+---------------+---------------+
| 1           | 1              | pc            | 2020-10-02    |
+-------------+----------------+---------------+---------------+
| 2           | 2              | tablet        | 2020-10-10    |
+-------------+----------------+---------------+---------------+
| 3           | 3              | phone         | 2030-10-10    |
+-------------+----------------+---------------+---------------+

Query :

SELECT * FROM products as p JOIN company as c on c.id_company = p.id_pro_company and p.control_dates LIKE ?

Now i have all record i need, but how can i group the ouput in php. example:

$date = '%2020-10%';
$query = $mysqli -> prepare('SELECT * FROM products as p JOIN company as c on c.id_company = p.id_pro_company and p.control_dates LIKE ?');
$query -> bind_param('s',$date);
$query -> execute();
$result = $query -> get_result();
if($result -> num_rows > 0){
   //how group for company then for products then output the record?
}else{
    echo 'Nothing to do';
}

As you can see in the comment in the code I don't understand how to group companies with products inside.

Example output:

 1. Microsoft
    - PC
      * 1 - pc - 2020-10-02
 
 2. Apple
    -tablet
      * 2 - tablet - 2020-10-10
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Simone Rossaini
  • 8,115
  • 1
  • 13
  • 34

1 Answers1

1

with mysl you will always get a flat array (so data will be repeated) so the idea would be to do nested foreach on the same array: the first will output the vendor, the second with an if on the vendor will echo the product type and the third will echo the products. But this is very unefficient.

I'd go for something like three different queries: find vendors, find product type and find products.

Third way is to manipulate your array to split it into different arrays for each vendor but this is yet less efficient compared to multiple queries IMHO

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74