Remove partial string from record's field - update with new string

[bubbadawg]

I have a field in a database which contains a string. The string can contain multiple words separated by commas (i.e. planes, 757, Boeing). What's the best way to retrieve all records that have a particular string (word) in a field, remove that string(word) from the field and then update the field and record.

 

For example, I want to find all records that have 'planes' in the field 'asset_lightboxes'. Remove any instances of 'planes' from the string and update the record. In the example above the updated field would be - '757, Boeing'.

 

Thanks for the assistance.

[shedokan]

Supposing that all words end with a comma and start with a space you can get all rows that have a practicular word in them like so:

$result = mysql_query("SELECT id, asset_lightboxes FROM table WHERE asset_lightboxes LIKE '% planes,%'";

 

Then you can loop through each of them, replace " planes," with "", and update

while($r = mysql_fetch_row($r)){
  $r['asset_lightboxes'] = str_replace(" planes,", '', $r['asset_lightboxes']);
  mysql_query("UPDATE table SET asset_lightboxes = '".$r['asset_lightboxes']."' WHERE id =".$r['id']);
}

 

Or you can join all update queries to a giant string with ; at the end of each and update them all at once, but that depends on your server.

 

Hope I helped you.

[bubbadawg]

......Hope I helped you.

 

Thanks, you definitely did!

 

I think this should work. The only issue I think I will run into is the string being replaced (planes) may or may not have a comma after it depending on where it falls in the main string (i.e. first word or last)