Page 1 of 1

Find / Replace in MySQL

Posted: Wed Jun 25, 2008 10:14 am
by kdidymus
Folks.

Finally. I've found the code I need to use to replace EXACT strings throughout my entire database.

Code: Select all

$query = "UPDATE tree SET urn='$new' WHERE urn='$old'";
I need to apply this change to a large number of columns. How do I go about repeating the above code for numerous columns? I've tried adding commas and repeating the above commands but it returns a syntax error.

Somebody mentioned a FOR_WHILE loop but I'm happy to define a long query if somebody can tell me how to separate the elements.

Thanks in advance.

KD.

Re: Find / Replace in MySQL

Posted: Wed Jun 25, 2008 10:28 am
by jayshields
You say finally as if you've found some lost treasure, whereas this query is the most basic UPDATE query you could possibly use! I haven't read many of your other thread(s) in-depth, but it seems that you have very little knowledge of SQL queries. For example, this query will operate on multiple tuples (rows) on it's own, providing that the WHERE clause matches multiple tuples. Queries do not affect columns, nor is there such terminology in databases.

If I understand you correctly, you want to change mutiple fields in the same tuple, across multiple tuples which match the same criteria. You can do this by seperating SET clauses with commas (to SET multiple fields) and seprating WHERE clauses with commas (to act upon tuples which match multiple criteria).

If you want to change different fields in different tuples you will have to execute more than one query.

Re: Find / Replace in MySQL

Posted: Thu Jun 26, 2008 2:48 am
by kdidymus
Jay.

You're quite right about my inexperience. I only started producing PHP websites about two months ago and although it's a steep learning curve, I think I'm managing to get my head around most of it.

One (hopefully) FINAL query.

I want the following code to erase (or make 'null') the specified field when the second specified field has a specific value (in this case "blank.gif").

This code doesn't appear to be doing the trick.

Any suggestions?

Code: Select all

   $query = "UPDATE tree SET photo1urn='null' WHERE photographs='blank.gif'";
    $result = mysql_query($query)
    or die (mysql_error());
I tried using '' instead of 'null' but there are still loads of photo1urn fields showing an incorrect value.

In case you're wondering, this PHP code is designed to optimise a section of my table to reduce the overall size and delete any unneccesary entries.

Thanks.

KD.

Re: Find / Replace in MySQL

Posted: Thu Jun 26, 2008 2:56 am
by kdidymus
Okay. Sorry. The code DOES work (now that I've changed the value for the second field) but it still doesn't "NULL" the values.

The new code is this:

Code: Select all

$query = "UPDATE tree SET photo1urn='' WHERE photographs='tree/graphics/blank.gif'";
    $result = mysql_query($query)
    or die (mysql_error());
Two questions really.

Would changing the value of this and other fields to NULL reduce the overall size of my database and

if so..

What command would I have to run to change the value to NULL?

KD.

Re: Find / Replace in MySQL

Posted: Thu Jun 26, 2008 3:12 am
by kdidymus
I'm going to stop posting here until I've researched properly.

It was all a matter of using NULL instead of 'NULL'.

Thank you all for your help over the past few months. It's very much appreciated.

KD.