IF ELSE mysql query for LIKE operator

Question

I have a mysql query like this.

SELECT item FROM items WHERE catid=3 AND tag LIKE '%".$tag[1]."%' OR tag LIKE '%".$tag[2]"%' LIMIT 4

How can I tell mysql to search item those have tag LIKE '%".$tag[1]."%' first, before it continue the search process to find items those have tag LIKE '%".$tag[2]."%'. Because right now it looks like mysql randomly choose what condition to execute. Sometimes it displays 4 results that all are match tag LIKE '%".$tag[2]."%' which is I don't want to. I want mysql to prioritize tag LIKE '%".$tag[1]."%' first.

It is kind of unclear question can you please explain for better understanding. — Ayaz Ali Shah, Jul 13 '17 at 05:27
@Mr.Developer I would like to tell Mysql to find items those have `tag LIKE '%tag1'` condition first before trying to find items those have `tag LIKE '%tag2%'` condition. Is it possible for doing that using mysql query or I need to do that from php? — zoora, Jul 13 '17 at 05:32
`LIKE` is boolean operator, which is either `TRUE` or `FALSE` (or `NULL`) - it can't be `< 4`. Please clarify your question. — el.pescado - нет войне, Jul 13 '17 at 05:39
Do you mean get the first 3 (or 1 or 2 if there are less than 3) results for tag1 and the rest from tag2 from your data source in your result set? — P.Salmon, Jul 13 '17 at 05:42
@Mr.Developer P.Salmon I have refined the question. Please have a look. Thanks guys! — zoora, Jul 13 '17 at 06:22
@zoora Can you try below my answer it is working same as you have mentioned in the question after edit. — Ayaz Ali Shah, Jul 13 '17 at 06:27
@Mr.Developer um sorry I have refined the question again, the tag is actually an php array — zoora, Jul 13 '17 at 06:32
@zoora Okay this array `$tag[1]` and `$tag[2]` has some value like `tag1` and `tag2` ? — Ayaz Ali Shah, Jul 13 '17 at 06:36
You cannot mix bits of mysql and php like this. If you know PHP use that. To clarify your question you should publish sample data and expected results based on the sample data. You should also add a php tag to attract the attention of the php community. — P.Salmon, Jul 13 '17 at 07:26

Ayaz Ali Shah · Answer 1 · 2017-07-13T05:49:38.043

0

You want to sort the result so try below

WHERE catid=3 AND `tag` LIKE '%tag%' ORDER BY IF( `tag` LIKE 'tag1%',0, IF( `tag` LIKE '%tag2', 1, 2 ) )

edited Jul 13 '17 at 05:49

answered Jul 13 '17 at 05:40

Ayaz Ali Shah

3,453
9
36
68

Surely you won't have any tag2 records? – P.Salmon Jul 13 '17 at 05:43
@P.Salmon I am sorry what do you means? – Ayaz Ali Shah Jul 13 '17 at 05:45
@P.Salmon Yes i have edited, but you comment is correct. Thank you for your notice +1 – Ayaz Ali Shah Jul 13 '17 at 05:52

score 0 · Answer 2 · answered Jul 13 '17 at 05:55

Maybe something like this using union with a limit for tag1

MariaDB [sandbox]> select * from items;
+--------+--------+--------+----------+-----------+
| ITMSTK | ITMQTY | ITMCOG | ITMURN   | ITMSOU    |
+--------+--------+--------+----------+-----------+
| WN778  | 1      |      2 | 12345112 | WEB       |
| WN776  | 1      |      1 | 12345112 | WEB       |
| WN771  | 1      |      2 | 12345112 | WEB       |
| WN845  | 1      |      1 | 22544548 | ADVERT    |
| WN846  | 1      |      1 | 22544548 | ADVERT    |
| WN845  | 1      |     20 | 44848643 | TELEPHONE |
+--------+--------+--------+----------+-----------+
6 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> (SELECT itmsou FROM items WHERE 3=3 AND itmsou LIKE '%web%' limit 2)
    -> union all
    -> SELECT itmsou FROM items WHERE 3=3 AND itmsou LIKE '%advert%'
    -> ;
+--------+
| itmsou |
+--------+
| WEB    |
| WEB    |
| ADVERT |
| ADVERT |
+--------+
4 rows in set (0.00 sec)

I have refined my question please have a look thanks! – zoora Jul 13 '17 at 06:27 — zoora, Jul 13 '17 at 06:27

score 0 · Accepted Answer · answered Jul 14 '17 at 02:32

I found the answer from the other post

SELECT item FROM items WHERE catid=3 AND 
   (tag like '%".$tag[1]."%' or tag like '%".$tag[2]."%') order by case 
       when tag like '%".$tag[1]."%' then 1 
       when tag like '%".$tag[2]."%' then 2 
       else 3 
   end 
LIMIT 4

score 0 · Answer 4 · answered Jul 14 '17 at 02:57

0

I would suggest you take a look at below:

select *
   ,case when tag  like '%$keyword1%' then 1
        when tag   like '%$keyword2%' then 2 
    end as [priority]
from table_name
where tag   like '%$keyword1%'  or tag   like '%$keyword2%' 
order by [priority]

This actually still gives you the result of keyword2 but at same time it prioritized the keyword1 at the top.

Alternatively you maybe interested in sorting by "relevance" instead of your wild card search.

answered Jul 14 '17 at 02:57

Fernan Vecina

144
1
8

What is the difference between your answer and this answer https://stackoverflow.com/a/45093695 – zoora Jul 14 '17 at 03:43
that one only return keyword 1 result if is found while mine is actually using both keyword 1 and keyword 2 but sorting the result putting result using keyword 1 at the top – Fernan Vecina Jul 14 '17 at 03:46

IF ELSE mysql query for LIKE operator

4 Answers4