CODEWITHSUNDEEP
phpmysqlsortingmysqlicolumnsorting
Shehary
Shehary

Reputation: 9992

PHP MySQL, Sort result by 3 col's

I'm trying to sort the products based on OfferType Sequence and OutofStock

The table looks like

id    name        offertype    sequence    outofstock 
1     Alpha          1             3            0
2     Beta           2             1            0
3     Charlie        3             2            0
4     Delta          4             4            0
5     Random-1       0             5            0
6     Random-2       0             6            0
7     Random-3       0             7            0 
8     Random-4       0             8            0
9     Random-5       0             9            0
10    Random-6       0             10           1

The Goal is

  1. All products having offertype (Alpha, Bravo, Charlie, Delta) comes on Top and display according to sequence (Beta, Charlie, Alpha, Delta)
  2. Then next display all Random Products but each time page refresh these Random Products shuffle
  3. and if product is outofstock must stay at bottom as Last Product.

Note: sequence col can be removed if all products having offertype shuffle too when page refresh but they must stay on top of Random Products.

What I have tried is ORDER BY rand(), ORDER BY FIND_IN_SET() and PHP function array_rand() but not able to sort the products in desired order.

Upvotes: 0

Views: 49

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

It's a bit tricky, but not so much. First you need the primary sortings to put out of stock products at the bottom, followed by the group of random products. But then you need a little trick to apply different sortings to the group of random products and the group of products having an offer type. You can solve this in multiple ways, but I think a case is the most obvious:

ORDER BY
  -- Highest rule. Out of stock products always at the bottom.
  outofstock,
  -- Second important rule, offertype 0 (= random products) go at the bottom
  offertype = 0,
  -- Third rule is combined. Within the groups defined above, you need to sort by different fields.
  -- If offer type = 0 (random), then sort by rand(), else sort by sequence.     
  case when offertype = 0 then
    rand()
  else
    sequence
  end

If I implement your Note, then the non-random products can have a random shuffle too, in which case you can simply sort by rand() as the third condition:

ORDER BY
  -- Highest rule. Out of stock products always at the bottom.
  outofstock,
  -- Second important rule, offertype 0 (= random products) go at the bottom
  offertype = 0,
  -- Third rule: within the groups defined above, sort randomly
  rand()

Upvotes: 1

Related Questions