Is it possible to change part of a db-fields content by sql statement?

Hi y´all,

recently we´ve setup a webshop (mysql & php based) for a customer. All fine
and neat...

Now, after some thirdparty remodding, we learned that parts of the article
detail pages no longer had the looks they once had.
Especially the content of tables within the descriptive text (all in one
field of the db) are now in need of a tiny little correction.

The tag <table> should somehow be enhanced to <table class="data"> but with
more than 1200 articles it´s no fun thing to consider doing it by hand

So my question regards the handling of given content of one single db-field,
call it 'product_description'.
Is it possible to build a statement that uses 'product_description' in each
and every article?

Sure:
SELECT 'product_description' FROM shopdb...

But how would I go on telling MySQL that I wish it to
check every single articles 'product_description',
search for all occurrences of "<table>" within them
and replace each one of them with "<table class="data">" in one step?

Would this require some "assistance" say from php? If so how would you
formulate that?

Sorry for my blunt request for a working solution but I wouldn´t ask here if
my own research had been successful.
Perhaps I can´t see the woods for the trees, so any help or push in the
right direction would really be appreciated...

Best regs

Alex

"Alexander Schoenhoff" <aschoenhoff@web.de> wrote in message
news:dcbm6e$dad$02$1@news.t-online.com...
> Hi y´all,
>
> recently we´ve setup a webshop (mysql & php based) for a customer. All
> fine
> and neat...
>
> Now, after some thirdparty remodding, we learned that parts of the article
> detail pages no longer had the looks they once had.
> Especially the content of tables within the descriptive text (all in one
> field of the db) are now in need of a tiny little correction.
>
> The tag <table> should somehow be enhanced to <table class="data"> but
> with
> more than 1200 articles it´s no fun thing to consider doing it by hand
>
> So my question regards the handling of given content of one single
> db-field,
> call it 'product_description'.
> Is it possible to build a statement that uses 'product_description' in
> each
> and every article?
>
> Sure:
> SELECT 'product_description' FROM shopdb...
>
> But how would I go on telling MySQL that I wish it to
> check every single articles 'product_description',
> search for all occurrences of "<table>" within them
> and replace each one of them with "<table class="data">" in one step?
>
> Would this require some "assistance" say from php? If so how would you
> formulate that?
>
> Sorry for my blunt request for a working solution but I wouldn´t ask here
> if
> my own research had been successful.
> Perhaps I can´t see the woods for the trees, so any help or push in the
> right direction would really be appreciated...
>
> Best regs
>
> Alex


Why not set the default CSS for <table> to be what you want and set up
specific classes for the other instances of <table> that need different
formatting - you should be able to search and replace using a text editor.

If you really have to do the tag modification on the fly take a look at the
MySQL language reference manual (section 12) there are some quite
comprehensive string manipulation functions there. This will slow down the
site or at least cane the database server unnecessarily though.

Ron

Hi Ron and thanks for your helpful response,

> Why not set the default CSS for <table> to be what you want and set up
> specific classes for the other instances of <table> that need different
> formatting - you should be able to search and replace using a text editor.

That´s correct.
However, as i wrote, the problem is, that there are way too many entries in
our articles-db do make those changes manually.
We could though change the tables within the "carrier-page" to be
"CSS-classified".
Thus a change in class-definition for the general table-tag (the ones in the
descriptive text without a class-definition) would do the trick...

So your advice in this case is quite logical and would help me correct the
described situation.

Thing is, it´s often the case that there are other, "normal" words or lines
within a db´s field that need correction, f.e. just plain text parts in
every articles description that have to be changed as per customers demand.
So I´m afraid my example with the table-tag was poorly chosen - it´s the
latter challenge - plain text - we´d need to master.

> If you really have to do the tag modification on the fly take a look at
the
> MySQL language reference manual (section 12) there are some quite
> comprehensive string manipulation functions there. This will slow down the
> site or at least cane the database server unnecessarily though.

Your implication is right again - the modifications are not meant to be on
the fly, as that would definitly slow down the server.
They are meant to be made by using one correctional statement like UPDATE
(if that is the right candidate for this task) or one process to be
initiated.

My own findings so far:
As one seems no to be capable of doing it by using MySQL alone, MySQL needs
to be supported by a (php-,asp-,jsp-)scripted procedure to make the required
changes.
1. select and open the db - no problem
2. choose the table(s) in the required db - no problem
3. SELECT the fields content (into an array?) - "wee bit" of a problem
4. use REGEXP to find the "suspects" - BIG problem
5. change them into the new form - still BIG problem
6. write the last changed field content back to the db - should be
possible to solve

As well as I´m capable of describing the process I´m a noob in writing the
necessary code f.e. in PHP when it comes to the regexp-part.
But if... say PHP is a must to make this work my request may be in the wrong
newsgroup....

However I´m still writing this here just in case someone else has already
mastered this and is willing to shed a light ...

Thanks for your comments in advance...