Most common value from associative array

[Shomy]

Hello to everyone.
I have gotten some static site for the selling of cremes( where the owner wants the users have a user area where they can change consumer information, where consumers can see previous orders, and most bought products by that customer. The first two things are not the problem, but because I am putting those products into a base like json string, my question is: how do I take the most bought products from the base and print them out? Any sort of tutorial, help is needed. I am willing to even pay for it, just so I can get it done.
This is what the base looks like.

https://imgur.com/a/TC7RD

Thank you

[mac_gyver]

your current scheme would require that you retrieve ALL the data for any consumer, decode the json data, sum the quantities for each product, then find the highest sums.

 

storing multiple values in a single database table column is a bad design, regardless of the format being used, making it difficult to perform any operation on the data. you need to normalize the data and store it as one row per data item, using the order id to relate the rows back to the order they correspond to. you would then be able to craft a simple sql query that gets the result you want.

[Barand]

A typical data model would be, for example

+-------------+
| customer    |
+-------------+
| cust_id     |---+
| name        |   |
| etc         |   |
+-------------+   |
                  |
                  |
                  |      +-------------+                                +-------------+
                  |      | order       |                                | product     |
                  |      +-------------+                                +-------------+
                  |      | order_id    |---+                       +----| product_id  |
                  |      | cust_ord_no |   |                       |    | descrip     |
                  |      | order_date  |   |                       |    | size        |
                  +-----<| cust_id     |   |                       |    | price       |
                         +-------------+   |                       |    +-------------+
                                           |                       |
                                           |                       |
                                           |    +-------------+    |
                                           |    | order_item  |    |
                                           |    +-------------+    |
                                           |    | ord_item_id |    |
                                           +---<| order_id    |    |
                                                | product_id  |>---+
                                                | quantity    |
                                                +-------------+

Getting total quantities from the order item table is now a simple exercise as mac_gyver said

[Shomy]

Thank you all for your answers, but I think you didn't understand me well. Mac Gyver is on a good track.

 

storing multiple values in a single database table column is a bad design, regardless of the format being used, making it difficult to perform any operation on the data. you need to normalize the data and store it as one row per data item, using the order id to relate the rows back to the order they correspond to. you would then be able to craft a simple sql query that gets the result you want.

 

I fully agree with you, but as I've said, the site is static without a shopping cart, and a base. But the only way I could put in the products the consumer had chosen was through json array.

 

your current scheme would require that you retrieve ALL the data for any consumer, decode the json data, sum the quantities for each product, then find the highest sums.

I've done something similar, and here's an example of the code:

<?php
 $query="SELECT * FROM cart WHERE user_id = 541 ORDER BY id DESC";
 $resultQ=mysqli_query($con,$query);
 while($result = mysqli_fetch_assoc($resultQ)){
  $items = json_decode($result['cart'], true);
  foreach($items as $item){
   $product = $item['PRODUCT'];
   $price = $item['price'];
   $qty = $item['qty'];
    ?>
   Products: <?=$product;?> <?=$price;?><br />
   <?php
  } 
 }
?>

 

 

 

And this is how it looks.

 

https://imgur.com/a/qP5dG

 

Shown here are all the products of some consumer. How do I now take products that occur most frequently from that list?

[Barand]

But the only way I could put in the products the consumer had chosen was through json array.

Frankly that's Bulls**t. You mean it's the lazy way you chose to do it.

 

If you can extract the JSON data now, you could have extracted it prior to writing to the db and written to correctly normalized tables.