Jump to content


Photo

PHP - MySQL field string manipulation and value replacement

string

Best Answer DavidAM, 04 November 2013 - 05:56 PM

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, ',');
Go to the full post


  • Please log in to reply
5 replies to this topic

#1 RCurtis

RCurtis

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 04 November 2013 - 02:48 PM

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?

 



#2 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,949 posts
  • LocationSpring, TX USA

Posted 04 November 2013 - 03:02 PM

... 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).
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#3 RCurtis

RCurtis

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 04 November 2013 - 04:09 PM

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.



#4 AbraCadaver

AbraCadaver

    Cracka Memba

  • Gurus
  • 1,887 posts
  • LocationThe Republic of Texas

Posted 04 November 2013 - 04:19 PM

Probably an easier pattern, but:

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

mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

#5 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,949 posts
  • LocationSpring, TX USA

Posted 04 November 2013 - 05:56 PM   Best Answer

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, ',');

-- I haven't lost my mind, it's backed up on tape ... somewhere!

#6 RCurtis

RCurtis

    Newbie

  • New Members
  • Pip
  • 7 posts

Posted 04 November 2013 - 10:03 PM

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!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com