0

I have a table in my database called user_data. I want to select all rows of data from this table, where the user's advert_status is 'Active'.

However, there is also a column called advert_type in this table. The enum values of this are:

Deluxe, Premium and Basic.

I want to execute a MYSQLI query and list all users in the table where their advert_status is active. But i want to list deluxe users, then premium users and basic users - in that order.

Desired Result:

Deluxe User 1
Deluxe User 2
Deluxe User 3
Premium User 1
Premium User 2
Basic User 1
Basic User 2

Here's what i've tried.

$variable = $_GET['r'];
             if($variable == '0') {
             $query = $conn->query("SELECT * FROM user_data WHERE advert_status = 'Active' ORDER BY advert_type");

This doesn't do the job. Please can someone show me how to achieve my desired result? Thanks in advance.

M. Obren
  • 31
  • 1
  • 7
  • Show us some sample data so we can see the structure. Likely, the `ORDER BY` clause is sorting properly, but your expectation of how it is sorting may be misconstrued. Are the values numeric, with `0` representing Deluxe? Are they alphanumeric, with the value `deluxe` or similar? – D.N. Mar 31 '19 at 21:17
  • @D.N. as the question states, there is a column called advert_type with enum values set out as 'Deluxe', 'Premium' and 'Basic'. This is the structure / layout of the table column. – M. Obren Mar 31 '19 at 21:21
  • 1
    So for your desired results from the duplicate I flagged, `ORDER BY FIELD(advert_type, "Deluxe", "Premium", "Basic")` – Will B. Mar 31 '19 at 21:22

1 Answers1

0

You can use order by case when to fulfill your requirement:

 SELECT * FROM user_data 
 WHERE advert_status = 'Active' 
 ORDER BY
    Case when advert_type = "Deluxe" then 1
         when advert_type = "Premium" then 2
         else 3 end asc
  • For an alternative comparison syntax you can use `CASE advert_type WHEN "Deluxe" THEN 1 WHEN "Premium" THEN 2 ELSE 3 END ASC` However using CASE WHEN for sorting is typically known to be slow. – Will B. Mar 31 '19 at 21:26