PHP - MySQL field string manipulation and value replacement

[RCurtis]

Very much a noob here...please bear with me!

 

I have a MySQL table that has a particular column that contains data that "looks" like CSV data.

Example:

 

We'll say that the column name is "class"

 

record 1 - 1,3,5,2,8,10

record 2 - 6,4,8,10,2,1

 

I need to read through each record (I know how to do this...) read each field value into a string (can do this...)

Here is my challenge:

 

Once I have the data read into a string value I need to be able to "remove" a given value from the string and write it back.

 

In other words, let's assume I want to remove "1":

 

Data would become:

 

record 1 - 3,5,2,8,10

record 2 - 6,4,8,10,2

 

Note that any of the field values could have a "1" *and* a "10".  Simply looking for the "1"s in a string search isn't going to cut it.  I am assuming the data needs to be pulled into an array and do some comparing...but my brain is "losing it" here...no clue how to proceed!  Can someone point me in a direction?

 

[DavidAM]

... Can someone point me in a direction?

Fix your database design! Normalize the table so you do NOT have multiple values in a single column. There should be a parent table and a child table with a one-to-many relationship. Then the "update" you want to do becomes a simple DELETE statement.

 

 

Otherwise, you will need a query (SELECT), a for loop, explode(), array_search(), implode(), and another query (UPDATE).

[RCurtis]

Thanks very much for the quick reply!

 

I wish that I *could* change the database...but it is part of a website CMS that I really can't change.  *I* am the one that is (somewhat) going out on a limb and trying to do something behind the scenes.

[AbraCadaver]

Probably an easier pattern, but:

$var = preg_replace('/,1\b|\b1,/', '', $var);

[DavidAM]

Probably an easier pattern, but:

$var = preg_replace('/,1\b|\b1,/', '', $var);

 

I'm not sure that will work if "1" is the ONLY value in the column (there's no comma to match).

 

If I were stuck with this data, I would either use the explode/implode sequence:

$list = '1,3,5,2,8,10';

$list = explode(',', $list);
if (($sub = array_search(1, $list)) !== false) unset($list[$sub]);
$list = implode(',', $list);

OR add a comma to the beginning and the end of the string and then replace ",1," with ",", and then trim the leading/trailing commas:

$list = '1,3,5,2,8,10';

$newlist = ',' . $list . ',';
$newlist = str_replace(',1,', ',', $newlist);
$newlist = trim($newlist, ',');

[RCurtis]

DavidAM...The explode/implode snippet worked perfectly!! Thank you so much.  I had a feeling it was going to involve explode...but I've never used implode before and I certainly don't think I would have ever gotten the entire if statement down on my own...Thanks!