[SOLVED] Update - part of a field - Can I?

[SusieWoosey]

Server version: 4.0.25-standard.  MySQL client version: 4.1.22. (Both provided by my webspace reseller)

 

Is it possible to update just part of a field? Let me see if I can make that clearer. I want to update just a couple of words in a field on my database, I know I can update an individual record, but there is more than one occurance of the words I want to change. Is it possible with the UPDATE command, or do I, as I suspect, have to write code to split the field contents , change the bit I want changed, then join it all back together and then place it back in the field?

 

Is that clear? I hope so, not easy to write it down.

 

Just for example suppose my field contains the following text:-

 

The first night of a 30 date tour with The Everly Brothers, Bo Diddley and The Rolling Stones, Little Richard was later added to the bill to boost poor tickets sales.

 

I have a number of records on my database that contain 'The Rolling Stones' - I want to change that to '<a href="ppage09.php#stones">Rolling Stones</a>' so that when it shows up in a browser, it shows a link for a page I have on The Rolling Stones. Some entries on the database (new ones) are already like that, older entries are not. I need to change all old entries to show the URL style. Can I do that with UPDATE, or do I need to write some code and process the field (making necessary changes), and then do an UPDATE.

 

Hope that's clear,

 

Suzanne (who now has an online database in MySQL accessed via a browser page, I did it at last) 

[trq]

UPDATE tbl SET fld = REPLACE(fld,'The Rollong Stones','<a href="ppage09.php#stones">Rolling Stones</a>');

[SusieWoosey]

Thanks very much, I knew there might be a way. I'm still learning the commands.

 

 

Very much appreciated.

 

 

 

 

Suzanne.

[fenway]

While the solution is syntactically correct, you're making a mistake by storing markup in the database like that -- a mistake you'll ultimately regret.  Do this in the PHP output.

[SusieWoosey]

Thanks for the comment Fenway, but I have to ask - Why? What problems is it likely to cause me?

 

I'd appreciate the advice. I have another MySQL database that stores a lot of URLs for another website, and all the URLs for that are created 'on the fly' as the webpage is put together, which is how I think you suggest it should be done.

 

How would I go about making just a small part of the field into a URL, such as my example words 'Rolling Stones', those two words are surrounded by lots of other text. Bearing in mind there is nearly 83,000 entries on the database and only about 250 of them refer to the Rolling Stones.

 

The data in the field doesn't change, unless I need to amend it or add something extra to it. The URL for the website is http://www.andibradley.com/whatya/index.php if you like to have a look-see.

 

 

Regards,

 

 

Suzanne.

[fenway]

It just goes against the grain of DB design.  What if you wanted to produce a report from this column? Then that report woul;d have HTML code in it too!  The idea is to store atomic information in each field, because while it's easy to go one way (add code), it's not simple to remove it in the general case.

 

So adding a simple if() would keep your database pristine; the fact that it doesn't change often doesn't mean that you should corrupt the value.