-3

I have field in MySQL table. The field is called 'vehicles' When I add vehicles I add them by ID not name, so the field is populated like '2:3:4:6:7:9' 2 will be a car, 7 will be a bike, etc.

What I want to do quickly and simply is when I query the table I want to see if the field vehicle contains '2' is in that field within the 2:3:4:7:9.

I have tried a lot but coming up blank?

Thanks

zak
  • 25
  • 1
  • 7
  • 2
    You should normalize your database: Add a table with vehicles and a table to link it to the existing table. – jeroen Mar 01 '17 at 10:24
  • That is really time consuming surely? It must be possible to split the '1:3:5:7:8' and see if '3' exists and proceed from there? That has to be quicker and easier, thanks – zak Mar 01 '17 at 10:27
  • 1
    See http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad for why this is really bad idea. Changing the separator to comma would make your life a bit easier (find_in_set()), but still your queries would be highly inefficient. – Shadow Mar 01 '17 at 10:28
  • 1
    Doing query hacks because of bad database design is a lot more time-consuming. – jeroen Mar 01 '17 at 10:29
  • Just replace comma with colon in the duplicate topic and you get a plain answer. But the good answer is to change your design. – Shadow Mar 01 '17 at 10:36
  • So whats the best and most efficient way to do this then in your opinion? – zak Mar 01 '17 at 10:48
  • The most efficient way is what @jeroen suggested in the first comment. I linked you an SO question where you can find detailed arguments against what you are trying to do. – Shadow Mar 01 '17 at 10:53

2 Answers2

0

if you just want to locate any specific number then do like this

$data = "2:3:4:6:7:9";
echo "found on index : " . array_search("2", explode(":", $data));
Javed Sayyed
  • 149
  • 11
  • You do realize that you would have to get all rows from the database to be able to do this in php? – jeroen Mar 01 '17 at 10:31
-1

You can use FIND_IN_SET in quer query like

SELECT *
FROM yourTable
WHERE FIND_IN_SET('2',REPLACE(vehicle ,':',',')) > 0;

samples

mysql> SELECT FIND_IN_SET('1',REPLACE('2:3:4:7:9',':',',')) as a;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0,00 sec)

mysql> SELECT FIND_IN_SET('2',REPLACE('2:3:4:7:9',':',',')) as a;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0,00 sec)

mysql> SELECT FIND_IN_SET('7',REPLACE('2:3:4:7:9',':',',')) as a;
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0,00 sec)

mysql> SELECT FIND_IN_SET('8',REPLACE('2:3:4:7:9',':',',')) as a;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0,00 sec)
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • Nope, finds nothing : $result = mysqli_query($con,"SELECT *, ( 3959 * acos( cos( radians(53.83428999999999) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-3.0399797000000035) ) + sin( radians(53.83428999999999) ) * sin( radians( latitude ) ) ) ) AS distance FROM clients HAVING distance < 30 ORDER BY distance AND FIND_IN_SET('1',REPLACE(vehicles ,':',',')) > 0; LIMIT 0 , 20;"); – zak Mar 01 '17 at 10:46
  • @zak - it work, i have add some samples – Bernd Buffen Mar 01 '17 at 10:50