Order comma separated strings of a column using MySQL

Question

enter image description hereGood day! Paging all database engineers and masters may i have your attention for a while.

I am currently developing our Travel Recommender System and we're having a problem with ORDER BY LIKE SEPARATED COMMAS IN MYSQL.

I have two tables: -tb_users(this table has a field of "Traveler_Style" where it contains of registered style of what kind of traveler they are. the values of "Traveler_Style" is based on their preferences. Sample value of this is Backpacker, Nightlife)

-attractions(this table has a field of "traveler_style" consist of Backpacker, Nightlife, luxurytraveler )

In my search engine when the user enters an attraction in will ORDER BY LIKE based on the registered "Traveler_Style" on tb_users that will "LIKE" or Match to the record in the table attractions.

I tried to declare a variable to call the value of tb_user Traveler style which is $style_fetch and here's my idea to select the query

$query6 = "SELECT * FROM `attractions` WHERE CONCAT(`categories`, `tourist_spot`, `province`) LIKE '%".$valueToSearch."%' ORDER BY FIELD(traveler_style, '$style_fetch') DESC limit 8";

Expected output should be order by like based on the traveler style of a user

enter image description here

Hmm is there any sql statement for this kind of query? or trick. i just want to show the result based on the traveler style. Thanks for the comment :) — Pablo, Aug 23 '17 at 09:26
Can you include the table structure and the expected output table in your question? I cannot understand your question. — Clinton Lam, Aug 23 '17 at 10:09
@RaeIan Have you tried something like this? SELECT col1, CONCAT(col2, col3) AS newcol FROM tbl WHERE newcol LIKE '%text%' ORDER BY newcol — Clinton Lam, Aug 23 '17 at 10:59
Sir @ClintonLam hmm. I tried sir but not working :( , i just want to order them by like $style_fetch where the value of that variable is coming from the field of tb_users "Traveler_User" the sample value of this column is "Backpacker, Beach Goer" where i cannot use "LIKE in order by because sir the inputted data on the field "Traveler_Style" of table tb_users is separated by commas this data was came from the check box that they selected as who they are as a traveler — Pablo, Aug 23 '17 at 11:14
@RaeIan Can you separate Traveler_Style into different column? A better way to do this by separating the table. You should add a table containing Traveler_Style only — Clinton Lam, Aug 23 '17 at 11:30
You may change your question title to "Order comma separated strings of a column using MySQL" — Clinton Lam, Aug 23 '17 at 13:43
Hello sir, @ClintonLam late reply i already created separated table for traveler style and extracted the data that i needed from table attraction to the new table for styles which is tb_touristspot_styles it results hundreds of rows and the problem is i need to split this given output below and insert again with the same id for identification. sample table output fields ---- **id** | **style** rows ---- 22 | Beach Goer, Backpacker and so on.. — Pablo, Aug 23 '17 at 17:24
@RaeIan You should separate Beach Goer and Backpacker into two different row. Please check the answer I gave you below. — Clinton Lam, Aug 23 '17 at 23:17

Clinton Lam · Answer 1 · 2017-08-23T23:35:13.123

There are many ways of doing it, here are three approaches.

Using the MySQL function substring_index(). But you will need to know the number of styles there will be. Check this answer.

substring_index(Traveler_Style,',',1) => first value substring_index(substring_index(Traveler_Style,',',-2),',',1) => second value substring_index(substring_index(Traveler_Style,',',-1),',',1) => third value

Something like this

SELECT SUBSTRING_INDEX(Traveler_Style,',',1) AS col1,
       CONCAT(SUBSTRING_INDEX(Traveler_Style,',',1), 
              substring_index(substring_index(Traveler_Style,',',-2),',',1)) AS col2
FROM table 
WHERE col1 LIKE '%text%' OR
      col2 LIKE '%text%' 
ORDER BY col1, col2

Separate the table column. You may need to create more columns when needed. Not flexible.

Create an new table so that they can hold multiple traveler styles. You then join the tables and make queries. I prefer this method as it is more flexible and scalability.

   attractionTable         styleTable
+===================+   +===============+
| id | tourist_spot |   | id |    style |
+===================+   +===============+
|  1 |  Attraction1 |   |  1 | Backpack |
|  2 |  Attraction2 |   |  1 |    Beach |
|  3 |  Attraction3 |   |  2 |    Beach |
+===================+   |  2 |    Relax |
                        |  3 | Backpack |
                        +===============+

Notice there is no more comma in traveler style. They are separated in different rows.

In the above example Attraction1 have 2 related style Backpack and Beach. Attraction2 have 2 related style Beach and Relax. Attraction3 have 1 style Backpack.

Joining the two table and filter by LIKE

SELECT tourist_spot, style FROM attractionTable, styleTable 
WHERE attractionTable.id = styleTable.id
  AND style LIKE '%%'
ORDER BY style

    +===============================+
    | id | tourist_spot |     style |
    +===============================+
    |  1 |  Attraction1 |  Backpack |
    |  1 |  Attraction1 |     Beach |
    |  2 |  Attraction2 |     Beach |
    |  2 |  Attraction2 |     Relax |
    |  3 |  Attraction3 |  Backpack |
    +===============================+

Now all you need to do is to filter out different style.

Hope this may help.

Order comma separated strings of a column using MySQL

1 Answers1