How to order a Select query in MySQL based on the most occurrence of another table

user_6200226 0
Viewed 39

I have a product table and a memory table. The product table contains product ID and product name:

product_id product
1 RAM
2 HDD
3 SSD

The memory table contains different memory as below:

memory_id memory
1 128 GB
2 256 GB
3 512 GB
4 1 TB
5 8 GB
6 16 GB
7 32 GB

I have created two drop-down select options for products and for memory and save them in a cart table as below:

order_id product_id memory_id
1 1 1
2 1 2
3 1 1
4 2 3
5 2 4
6 2 3

I am wondering is there any way to do DESC order the select options for memory based on the occurrence of that specific product_id in cart table? My target is to order the select options of the memory with the most used cases.

For example, my current select options for memory looks like:

<select name="memory" id="memory">
  <option value="1">128 GB</option>
  <option value="2">256 GB</option>
  <option value="3">512 GB</option>
  ...
</select>

But what I am looking for when I would select the product 2, the select for memory will be:

<select name="memory" id="memory">
  <option value="3">512 GB</option>
  <option value="4">1 TB</option>
  <option value="1">128 GB</option>
  <option value="2">256 GB</option>
  <option value="5">8 GB</option>
  ...
</select>

I'm also wondering how would that impact on the query time, assuming that I have a cart table with thousands of rows.

mysql php
1 Answers

You could group by memory_id then order by count(*) DESC

SELECT memory_id 
FROM cart
GROUP BY product_id
ORDER BY count(*) DESC

If you want to fetch data for that select in one query, You could join memory table then select memory_id and memory, and group by both fields.

user_182775510
Related