How do I randomly display categories in a particular order

Question

I have an online store where the members can subscribe to any of the 3 packages (1, 2 & 3). Below is a sample of the Products DB table.

id    package          product       status
1     3                  Biro          1
2     1                  Paper         1
3     3                  Pin           0
4     2                  Eraser        1
5     2                  Blade         1
6     3                  Bag           1
7     2                  Pen           0
8     1                  Sharpener     1
9     1                  Shoe          1
10    2                  Chair         1

I displayed the package 3 products first, then package 2 products next then package 1 products last like this;

$get_products = mysql_query("SELECT * FROM products WHERE status='1' ORDER BY package DESC");

But what I want to really do is to randomly display all package 3 products first, then randomly display package 2 products and lastly display package 1 products randomly. So that no particular item will always be at the top while some will always be on the bottom.

How do I write the syntax?

Answer 1

It is possible to sort the products column randomly using only SQL by doing the following:

$get_products = mysql_query("SELECT * FROM products WHERE status='1' ORDER BY package DESC, RAND()");

However, please keep in mind ordering by "RAND()" in MySQL can be slow, especially if your columns are not indexed.

See the following links for reference:

MySQL: Alternatives to ORDER BY RAND()

How can i optimize MySQL's ORDER BY RAND() function?

mysql order by rand() performance issue and solution

Answer 2

First, split the products into groups by package as you fetch your query results:

while ($row = $result->fetch_assoc()) {
    $packages[$row['package']][] = $row;
}

Then shuffle the groups before you output them:

foreach ($packages as $package_products) {
    shuffle($package_products);
    foreach ($package_products as $product) {
        // output product
    }
}