CodeIgniter Forums

Full Version: Remove values in comma separated list from database
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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?
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.
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