+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Remove values in comma separated list from database (/showthread.php?tid=68098)
Remove values in comma separated list from database - googlemy - 05-24-2017
Lets say i have a table in my database there looks like this:
|-------------------------|
| id | numbers |
|-------------------------|
| 1 | 1,3,5,7 |
| 2 | 2,4,6,8 |
| 3 | 1,2,3,4,5,6,7,6,8 |
|-------------------------|
I want to remove let us say 4 for all the rows that has the number 4 in the numbers column. What is the sql call to this?
RE: Remove values in comma separated list from database - skunkbad - 05-24-2017
Comma separated values, serialization, json, or any other stringified type of object is not ideal to store in a database. Search around for "database normalization", and learn the right way to create databases/tables.
The only thing you can do here is use LIKE, but as you are aware that isn't ideal because 4 would match 40, 404, 84, etc.
You could select all rows, then let PHP handle it, and that's really not ideal.
So, consider database normalization, because it's the right thing to do for data integrity.
RE: Remove values in comma separated list from database - neuron - 05-25-2017
You need to use regular expression to accomplish this. but as shankbad suggested it is better to change the structure of your table.
for example u can convert yout table to this:
| ID | product_name | images |
|---------------------------------------|
| 1 | Phone | img1.jpg
| 2 | Phone | img2.jpg
| 3 | Canon | img1.jpg
| 4 | Canon | img2.jpg
| 5 | Canon | img3.jpg
...
this way it will be easier to manage and write queries