Change one word in a mediumtext column

MySQL, PHP

[yowza]

I have a mediumtext column with about 2,000 rows in it. I would like to search through all rows for a word and replace it with the word with a link.

For instance: Search for "widget"
Replace all instances of "widget" with <a href="www.widget.com/widgets">widget</a>

I imagine this is possible, but don't know how to do it.

Thanks.

[dcrombie]

This can be done in MySQL - something like:

UPDATE table SET field = REPLACE("before", "after", field);
(you'll have the check if that's the right function name and syntax)

I strongly recommend NOT doing this in the database however, but on the PHP-side when you extract the text for a page. There are many reasons for this.

[yowza]

Thanks. Do you mean you recommend actually replacing the text when I query the database and echo it onto the page? I know that I can do this with PHP and ereg_ or preg_replace. However, I was thinking that it would result in faster-loading pages if it was replaced in the database instead of on the fly.

Why do you recommend not doing it in MySQL? I plan to do this for a potentially large number of keywords. I don't think that replacing the keywords on the fly would be very efficient.

[dcrombie]

1) PHP is very fast;
2) It keeps your data clean, leaving future options open;
3) If the keywords or links change you only have to modify the PHP - not run increasingly complex find/replaces over the database;
4) You shouldn't be storing redundant information in a database;
5) PHP is very fast;

;)

[timster]

I plan to do this for a potentially large number of keywords. I don't think that replacing the keywords on the fly would be very efficient.

I agree with dcrombie. You'd be giving away a lot of the flexibility you bought by using a database if you crank these links into your data. This is all about making your life easier down the road.

You probably don't want to put a large number of "replace" lines into the PHP code. I'd suggest making a meta-data table, containing your keywords and their corresponding links. Just loop through them to make your replaces.

BTW, for just changing keywords, you may be able to get away with using str_ireplace which I hear tell is a little faster than regex replaces.

If you're still set on putting the links into your data, do yourself a favor and don't change the original data. Crank the new data into different columns.

[yowza]

Thanks. I guess I'll try it in PHP first and if I see a big hit in performance I'll explore other options.

I definitely plan on doing it as you explained timster.

Thanks for your help!