Page 1 of 1

Multiple MySQL Queries

Posted: Wed Jun 25, 2008 2:37 am
by kdidymus
Folks.

Would the code below work?

Code: Select all

<?php
/*  Program name: replace.php
 *  Description:  Replaces old URN with new URN.
 */
 
    // CONNECT TO MYSQL DATABASE
    include_once("../*******.inc.php");
    $cxn = mysql_connect($host,$user,$password)
           or die ("couldn't connect to server");
    mysql_select_db($database);
 
    // GET STRINGS TO REPLACE
    $old=$_POST['old'];
    $new=$_POST['new'];
 
    // REPLACE STRINGS IN DATABASE
    $query = "UPDATE tree SET urn = replace(urn,'$old','$new')";
    $query = "UPDATE tree SET motherurn = replace(motherurn,'$old','$new')";
    $query = "UPDATE tree SET fatherurn = replace(fatherurn,'$old','$new')";
    $query = "UPDATE tree SET sibling1urn = replace(sibling1urn,'$old','$new')";
    $query = "UPDATE tree SET sibling2urn = replace(sibling2urn,'$old','$new')";
    $query = "UPDATE tree SET sibling3urn = replace(sibling3urn,'$old','$new')";
    $query = "UPDATE tree SET sibling4urn = replace(sibling4urn,'$old','$new')";
    $query = "UPDATE tree SET sibling5urn = replace(sibling5urn,'$old','$new')";
    $query = "UPDATE tree SET sibling6urn = replace(sibling6urn,'$old','$new')";
    $query = "UPDATE tree SET sibling7urn = replace(sibling7urn,'$old','$new')";
    $query = "UPDATE tree SET sibling8urn = replace(sibling8urn,'$old','$new')";
    $query = "UPDATE tree SET sibling9urn = replace(sibling9urn,'$old','$new')";
    $query = "UPDATE tree SET sibling10urn = replace(sibling10urn,'$old','$new')";
    $query = "UPDATE tree SET sibling11urn = replace(sibling11urn,'$old','$new')";
    $query = "UPDATE tree SET sibling12urn = replace(sibling12urn,'$old','$new')";
    $query = "UPDATE tree SET sibling13urn = replace(sibling13urn,'$old','$new')";
    $query = "UPDATE tree SET sibling14urn = replace(sibling14urn,'$old','$new')";
    $query = "UPDATE tree SET sibling15urn = replace(sibling15urn,'$old','$new')";
    $query = "UPDATE tree SET sibling16urn = replace(sibling16urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1urn = replace(spouse1urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child1urn = replace(spouse1child1urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child2urn = replace(spouse1child2urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child3urn = replace(spouse1child3urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child4urn = replace(spouse1child4urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child5urn = replace(spouse1child5urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child6urn = replace(spouse1child6urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child7urn = replace(spouse1child7urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child8urn = replace(spouse1child8urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child9urn = replace(spouse1child9urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child10urn = replace(spouse1child10urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child11urn = replace(spouse1child11urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child12urn = replace(spouse1child12urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child13urn = replace(spouse1child13urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child14urn = replace(spouse1child14urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child15urn = replace(spouse1child15urn,'$old','$new')";
    $query = "UPDATE tree SET spouse1child16urn = replace(spouse1child16urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2urn = replace(spouse2urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child1urn = replace(spouse2child1urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child2urn = replace(spouse2child2urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child3urn = replace(spouse2child3urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child4urn = replace(spouse2child4urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child5urn = replace(spouse2child5urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child6urn = replace(spouse2child6urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child7urn = replace(spouse2child7urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child8urn = replace(spouse2child8urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child9urn = replace(spouse2child9urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child10urn = replace(spouse2child10urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child11urn = replace(spouse2child11urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child12urn = replace(spouse2child12urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child13urn = replace(spouse2child13urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child14urn = replace(spouse2child14urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child15urn = replace(spouse2child15urn,'$old','$new')";
    $query = "UPDATE tree SET spouse2child16urn = replace(spouse2child16urn,'$old','$new')";
    $result = mysql_query($query)
    or die ("Couldn't execute query.");
 
    // DISPLAY RESULT OF MYSQL ENQUIRY USING HTML
echo "<html>
<head>
<title>REPLACE URN | Status Report</title>
</head>
<body>
<p align='center'><font face='Arial' color='#C0C0C0' size='7'>SUCCESS!</font><font face='Arial' size='5' color='#C0C0C0'><br>
</font><font face='Arial' size='4'>All applicable instances of URN $old have been replaced with URN $new.</font></p>
<p align='center'><font face='Arial' color='#FF0000'>Click
<a target='_top' style='color: #FF0000; font-weight: bold' href='index.htm'>HERE</a> 
to return to the Site Administration Gateway.</font></p>
</body>
</html>";
?>
 
What this is intended to do is replace all relevant instances of a number with another.

But I'm not sure whether it is possible to run multiple queries this way...

KD.

Re: Multiple MySQL Queries

Posted: Wed Jun 25, 2008 2:53 am
by mikelbring
It will probably work but not how you want it to. You are just replacing the previous $query variable with a new one everytime. Probably the best way to do what you want to do is do like $query[] ="query" and just run a foreach loop with the array.

Or

Since they are just one table you are updating you can just run one query and have a comma in between each replace.

Re: Multiple MySQL Queries

Posted: Wed Jun 25, 2008 3:00 am
by kdidymus
Brilliant. Thank you.

One more question though.

Will this code replace PART of a string? i.e. If I choose to replace all instances of 583 with 5831898, will it also replace a string with a value 583-1 with 5831898-1? If not, can I add a wildcard?

KD.

Re: Multiple MySQL Queries

Posted: Wed Jun 25, 2008 4:23 am
by kdidymus
I've been researching this and found the command:

Code: Select all

$query = "SELECT REPLACE ('$old','$old','$new')";
Which would work fine APART from one of the URNs I want to change is "28". If I run the above command on the ENTIRE database it will also find and change things like "28th February" and "1928".

I need to specify which columns to alter to avoid this but I'm not at all sure how to go about it. I like the idea of using the FOR_WHILE loop but, likewise, this is beyond my knowledge.

Is there a way of setting the above command to work on SELECTED columns only?

KD.