Mysql Search & Replace

[niagara]

Hello, this is the first time I have posted to the forums here, so please bare with me.

 

I have a mySQL database with several fields within the records.

 

I want to search for a string of text in one particular field called 'comments' and add in something else to the field, even though the field contains other information.

 

For example...

 

My 'comments' field contains something like 'Formerly Bob's car.'

 

I want to add data to the field through a UPDATE command through MYPHPADMIN.

 

This command line command doesn't work properly on TCH, as it returns even records without 'Formerly' in the 'comments' field.

 

SELECT * FROM cars WHERE comments=REPLACE(comments, 'Formerly ', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>'); (which doesn't work at TCH) . It processes ok, but comes back with anything in particular it wishes.

 

I want my change to be in the 'comments' field to look like...

 

Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>Bob's car.

 

I will later go in and manually place text before the </a>

 

What would also be the SELECT command line to view (to verify) this will work, without messing things up?

 

How would I write the UPDATE command line to modify my records?

 

Much appreciated.

 

Thank you.

Edited May 4, 2009 by niagara

[OJB]

I thought the syntax was:

 

>SELECT REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>') FROM cars

 

That will show whether it will work I guess

 

 

then the update code would be:

 

>UPDATE cars SET comments = REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>')

[TCH-Bruce]

Welcome to the forums niagra

[niagara]

Asking for addditional WHERE code verification, forgot to mention that in my original email.

 

Thank you very much for your reply on the code.

 

I tried the SELECT line. It worked.

 

One additional thing I would like to add to the UPDATE command you replied with....

 

SELECT REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>''>http://www.domainname.com/cars.phtml?id="></a>''>http://www.domainname.com/cars.phtml?id="></a>''>http://www.domainname.com/cars.phtml?id="></a>') FROM cars

 

UPDATE cars SET comments = REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>')

 

I want to add a WHERE statement in there that will only apply changes to particular records.

 

Here is the issue.

 

I am actually doing it for locomotives. But wanted to be annon., if you understand.

 

I have a field for railroad name, a field for locomotive series as for instance...

 

railroad='BNSF'

subdir=4500

 

now... is this the proper way to rewrite this.

 

SELECT REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>') FROM cars WHERE railroad='BNSF' AND subdir=4500

 

UPDATE cars SET comments = REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>') WHERE railroad='BNSF' AND subdir=4500

 

Are these new SELECT and UPDATE command lines directly above correct, with the WHERE statement added?

 

I thought the syntax was:

 

 

 

 

>SELECT REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>') FROM cars

 

That will show whether it will work I guess

 

 

then the update code would be:

 

>UPDATE cars SET comments = REPLACE(comments, 'Formerly', 'Formerly <a href="http://www.domainname.com/cars.phtml?id="></a>')

Edited May 4, 2009 by niagara

[niagara]

Thanks Bruce.

 

Been with TCH since March 2002 or 2003. Wish I was with TCH before that.

 

Just getting into basic mySQL now, as my friend built my database structure and I added the records.

 

 

Welcome to the forums niagara

Edited May 4, 2009 by niagara

[niagara]

I managed to get the WHERE command to work and I changed some lines of code.

 

I was wondering if anyone knows of a special code where, if I deleted the </a> within my comments field, which I managed to do, is there a code to append it to the end of the following code....

 

UPDATE diesel SET comments = REPLACE(comments, '', '') WHERE railroad='ns' AND subdir='1300'

 

 

Here is the situ.

 

A field may now look like..... in editor mode... RIGHT NOW,

 

Formerly <a href="http://www.niagararails.com/diesel.phtml?id=12322">NW'>http://www.niagararails.com/diesel.phtml?id=12322">NW 1335

 

But I want to add </a> to the END of the field to look like...

 

Formerly <a href="http://www.niagararails.com/diesel.phtml?id=12322">NW 1335</a>

 

The numbers before the </a> are different in each record, so you can to a traditional S/R.

 

thx

Edited May 4, 2009 by niagara

[OJB]

try something like this:

 

 

>UPDATE diesel SET comments = CONCAT(comments, '</a>') WHERE railroad='ns' AND subdir='1300'

 

What this does is concatenate the current comment and appends </a> to the end of it.

 

Not sure if those where clauses are the correct clauses for your use but it gives the idea of how to do it.

[niagara]

Thank you, OJB.

 

Worked exactly as I needed it to.

 

Sure makes life a lot easier with these codes.

 

 

try something like this:

 

 

>UPDATE diesel SET comments = CONCAT(comments, '</a>') WHERE railroad='ns' AND subdir='1300'

 

What this does is concatenate the current comment and appends </a> to the end of it.

 

Not sure if those where clauses are the correct clauses for your use but it gives the idea of how to do it.

[OJB]

Glad it worked, Niagara.

 

Feel free to post any more questions or PM me directly. I work with MySQL everyday in a professional capacity so I 'should' be able to help if you have any further questions.

[TCH-Thomas]

Welcome to the forum, niagra.

[niagara]

OJB:

 

I tried sending a PM to you but got a server error at about 7:45am this morning.

 

I wanted to ask you if there was a simpliar way to write these 2 lines.

 

The point of my question is to add data to RANDOM records for ONE specified field, where I tell it which records in a group of units gets data placed in it.

 

Here would be my first one.

 

UPDATE diesel SET comments='Formerly BLAH BLAH BLAH' WHERE model='NS' AND subdir='1300' AND unit='1304' AND unit='1308' AND unit='1313' AND unit='1345' AND unit='1374' AND unit='1387'

 

Is there a way to consolidate the unit numbers so I don't have to keep putting AND unit= coding in there for all the units?

 

Possibly an array or some sort? There is no pattern to the numbers. I could have 3 in a row then none for 30 and every other. As long as I specify the unit.

 

Also, How would that code you gave me for adding text to the END of a field look like with several random units specified in the UPDATE command line?

 

 

thx.

 

niagara

[OJB]

You can use the 'IN' clause

 

>UPDATE diesel SET comments='Formerly BLAH BLAH BLAH' WHERE model='NS' AND subdir='1300' AND unit IN('1304', '1308', '1313', '1345', '1374', '1387')

 

If you want to do it truly random though you can 'group by rand()' and then limit.

 

 

So if you wanted to select 5 random rows from your database you could do the following:

 

>SELECT * FROM diesel ORDER BY RAND() LIMIT 5

 

for the update command using the IN clause you could do:

 

>UPDATE diesel SET comments = CONCAT(comments, '</a>') WHERE railroad='ns' AND subdir='1300' AND unit IN('1304', '1308', '1313', '1345', '1374', '1387')

[niagara]

Thank you OJB.

 

Exactly what I needed again.

 

I have always been the type who struggles trying to understand new computer command language, but once I physically see one example, I get it from there on.

 

I will probably get around to using these commands later this week. I've got family visiting over the next few days........

 

You truly have taught me a lot on this stuff, with just a few commands.

 

Getting used to using these commands. Thank god I back up my databases after I do changes, as I always do, but made sure I did before I ran that one command I originally posted to you through this forum about paste something at the end of the field. Had to restore my DB last night as the command initially (not yours) cut all the guts off my selected records except the first character.

 

Have a good day OJB.

 

 

<code snipped out to save scrolling>