How to split results in MySql PHP

3641 views mysql
-1

I am having issues with Splitting results with MySql. I am still new to MySql.

I have a table like this in MySql products_table

Id | Product | OWNER
---------------------
1  | Bag     | Admin
2  | Shoes   | Admin
3  | Shirts  | User
4  | Purse   | Admin
5  | Shoes   | User
6  | Sandals | User
7  | Watch   | Admin
8  | Jean    | User
9  | Slippers| Admin
10 | Laptop  | User
11 | Phones  | User
12 | Piano   | Admin
13 | Keyboard| User
14 | Template| Admin
15 | Desktop | Admin

I want a result that brings out maximum of 10 results, at least 4 products from user and 6 products from admin in Random order

So the result will give something like this.

1  | Bag     | Admin
4  | Purse   | Admin
11 | Phones  | User
14 | Template| Admin
3  | Shirts  | User
9  | Slippers| Admin
5  | Shoes   | User
8  | Jean    | User
7  | Watch   | Admin
12 | Piano   | Admin

Thank you very much in advance.

answered question

Maybe see UNION.

UNION? Do I need Union since its just one table @Strawberry

1 Answer

8

You can use UNION ALL:

(
 SELECT Id, Product, Owner 
 FROM products_table 
 WHERE Owner = 'User'
 ORDER BY RAND() 
 LIMIT 4   -- get random 4 rows for 'User'
)
UNION ALL
(
 SELECT Id, Product, Owner 
 FROM products_table 
 WHERE Owner = 'Admin'
 ORDER BY RAND() 
 LIMIT 6   -- get random 6 rows for 'Admin'
)
ORDER BY RAND() -- eventually random sorting again

posted this

Have an answer?

JD

Please login first before posting an answer.