I have following table:
+----+-----------------------------------------+----------+
| id | sort | channel |
+----+-----------------------------------------+----------+
| m2 | ["m2","1","2","11","m4","m3","m5","m1"] | E |
| 2 | ["m2","1","2","11","m4","m3","m5","m1"] | H2 |
| 1 | ["m2","1","2","11","m4","m3","m5","m1"] | H1 |
| 11 | ["m2","1","2","11","m4","m3","m5","m1"] | H3 |
| m4 | ["m2","1","2","11","m4","m3","m5","m1"] | S1 |
| m3 | ["m2","1","2","11","m4","m3","m5","m1"] | C3 |
| m5 | ["m2","1","2","11","m4","m3","m5","m1"] | M4 |
| m1 | ["m2","1","2","11","m4","m3","m5","m1"] | G6 |
+----+-----------------------------------------+----------+
Using MySQL Query:
SELECT id, sort, channel FROM (SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL UNION SELECT m.marker, c.sort, m.channel FROM markers m, channels c, streams s WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch ORDER BY LOCATE(id, CONCAT("'", sort, "'"));
I got above table result and i need to sort id using sort field...so output needs to be:
| id |
------
m2
1
2
11
m4
m3
m5
m1
I think the problem is in the: ORDER BY LOCATE(id, CONCAT("'", sort, "'"))
but i try to modify query without desired output success...what i need to do to get above correct output?