IF in ORDER BY sql php

Question

Hey guys I am not sure if this can be done. I have a table with products like this:

each product has a category and some have a secondary category.

I want to be able to short in this case using the example table above the 'someother' category by Order2 and Οrder for example:

Item2 has as a main category the 'someother' category and order number "2"

Items 1 and 5 have 'someother' category as 2ndcategory with order2 -number "3" and "1"

To display the items I am using the following query:

$query= "SELECT * FROM products WHERE category = $cat_id or 2ndcategory = $cat_id  ORDER BY order ASC";

As it is right now I am displaying the items with main or 2nd category someother but the display order is item1, item2 and item5

I want to be able to display item5, item2 and item1 using in the case of items 5 and 1 order2 and in the case of item2 order.

I have tried the following but it doesn't seem to work.

$query3 = "SELECT * FROM products";
        $tests= $conn->query($query3);
        foreach($tests as $test){
            $test_cat = $test['2ndcategory'];
            $test_id = $test['order2'];
            $test_id_1 = $test['order1'];
            $test_code='';
                if($test_cat != NULL){
                    $test_code = $test_id;
                }else{$test_code = $test_id_1;
              }
            }
$query= "SELECT * FROM products WHERE category = $prod_id or 2ndcategory= $prod_id ORDER BY ".$test_code." ASC";

I have also tried

  $query3 = "SELECT * FROM products";
            $tests= $conn->query($query3);
            foreach($tests as $test){
                $test_code= $test['order2'];
                }

    $query= "SELECT * FROM products WHERE category = $prod_id or 2ndcategory= $prod_id ORDER BY IF($test_code != NULL){order2}else{order} ASC";

and

   $query= "SELECT * FROM products WHERE category = $prod_id or 2ndcategory= $prod_id ORDER BY 
    CASE orderby 
    WHEN order2 != NULL THEN order2
    WHEN  order2 == NULL THEN  order END 
    ASC";

I have also tried

ORDER BY        
CASE orderby        
WHEN order2 is not NULL THEN order2
ELSE  order END ASC";

and

ORDER BY 
CASE orderby WHEN order2 is not NULL THEN order2
CASE WHEN order2 is NULL THEN order
ELSE order END 
ASC

Any ideas would be great guys. Thank you in advance.

@Mech what do you mean mate this is a php question so the tag is php — JohnnyD, Oct 29 '20 at 13:27
`ORDER BY CASE WHEN order2 is not NULL THEN order2 END ASC, CASE WHEN order2 is NULL THEN order END ASC,` — Devsi Odedra, Oct 29 '20 at 13:28
when you need comparision with `null` then use `is null` not use `=` — Devsi Odedra, Oct 29 '20 at 13:31
@DevsiOdedra it doesn't seem to work. IT displays 2-1-3 in this example — JohnnyD, Oct 29 '20 at 13:34
Your description of what sorting you actually want to achieve here, is completely undecipherable to me. — CBroe, Oct 29 '20 at 13:52

score 2 · Accepted Answer · answered Oct 29 '20 at 14:00

2

Your description of what kind of sorting your after does not really make sense and this question is also more of an SQL question than a PHP one, however I think this question might be useful:

Can you add an if statement in ORDER BY?

answered Oct 29 '20 at 14:00

Kieran

46
3

I did see that and did try it but it does not seem to work for me, as nothing appears, `ORDER BY CASE orderby WHEN eb_prod_code_sec is not NULL THEN eb_prod_code_sec ELSE eb_prod_code END ASC";` – JohnnyD Oct 30 '20 at 06:05
I also tried it as `ORDER BY CASE orderby WHEN eb_prod_code_sec is not NULL THEN eb_prod_code_sec CASE WHEN eb_prod_code_sec is NULL THEN eb_prod_code ELSE eb_prod_code END ASC` @Kieran – JohnnyD Oct 30 '20 at 06:11
I managed to make it work, `Using the following link you can solve it by ORDER BY CASE WHEN order2 IS NULL THEN order ELSE order2 END ASC` – JohnnyD Oct 30 '20 at 07:40

IF in ORDER BY sql php

1 Answers1