How to update/delete a single word in a line of text

Question

I need your help with a slight problem I have. How can I update a portion of group of text separated by comma. When the text is entered into the database, it's a single line of text with commas between each words, but when I want to echo it out I use the explode function to separate them into individual words and not a single line of text like I have it in the database.

So my question now is, how can I make an update/Delete to a single word in the database. Remeber, I have it as a single line of text in the database, and I'm not interested in updating the whole line of text...just a single word in the text..

Thanks.

$text = "name,fname,lname,class,age" ;    
$newtext = explode(",", $text) ;

Is it not an option to change the database into having these tags (or whatever they are) be in separate rows in the database, and then selecting all of them using a `WHERE` clause? — h2ooooooo, Jun 18 '12 at 08:16

score 0 · Answer 1 · answered Jun 18 '12 at 08:09

0

Could use the replace() function in the sql update query.

Update table SET text=REPLACE(text,'age','newstring')

answered Jun 18 '12 at 08:09

sel

4,982
1
16
22

1

That would, however, replace '`last_page_visited`' with '`last_pnewstring_visited`' – h2ooooooo Jun 18 '12 at 08:14
how about adding in WHERE text REGEXP '[[:<:]]{$f['age']}[[:>:]]' – sel Jun 18 '12 at 08:21
typo: should be WHERE text REGEXP '[[:<:]]age[[:>:]]' – sel Jun 18 '12 at 08:27
Even if it matched (which I think it doesn't) it'd still turn `var_one,age,var_two,last_page_visited,var_three` into `var_one,newstring,var_two,lastpnewstring_visited,var_three`. The regex that I think would work would be the following: `(^age,|,age$|,age,|^age$)`, however MySQL has no replace function with Regex, as far as I can see, and the only workaround is to use an UDF like the following: https://launchpad.net/mysql-udf-regexp – h2ooooooo Jun 18 '12 at 08:37
I am using mysql 5.5 with mysql workbench and i could run the above query. – sel Jun 18 '12 at 09:37

score 0 · Answer 2 · answered Jun 18 '12 at 08:16

Update or remove the word from your array, then implode the array and save the new value to the database field.

//Remove all instances of $value from $array
function array_remove($array, $value) {
  return array_filter($array, function ($e) use ($value) {
      return $e != $value;
    });
}

// Add only unique values
function array_add($array, $value) {
  if (!in_array($value, $array))
    $array[] = $value;
  return $array;
}

$text = "name,fname,lname,class,age" ;
$newtext = explode(",", $text) ;
$newtext = array_remove($newtext, 'lname'); // Remove lname
$newtext = array_add($newtext, 'mail');     // Add mail
$newtext = array_add($newtext, 'class');    // Won't add it again
$newtext = implode(',', $newtext);          // name,fname,class,age,mail

How to update/delete a single word in a line of text

2 Answers2

Linked