I have an admin script that needs to update multiple rows at one time. In the past, I have written it like so:

$updateArray = array("one", "two", "three", "four");

foreach($updateArray as $id=>$value)
{
    $SQL = "UPDATE table SET field='$value' WHERE id='$id'";
    mysql_query($SQL);
}

Now, my question is, is there a way to construct an SQL statement so that I don't have to loop through and query the database multiple times? I realize that this is more of an SQL question than it is a PHP question but this board is one of the only places I know of where I can get results quickly.

Any insight would be appreciated.

    i think this is impossible, your solution seems to be okay

      I have tried this myself, doing several queries at the same time. But I bunched all the queries into one... It didn't work.

      In phpmyadmin you can do several queries at once. There's a place to start I think...

      Good Luck.

        $updateArray = array("one", "two", "three", "four");

$sql = "";

foreach($updateArray as $id=>$value)

{
   if  ($sql == "" )  {
       $sql = "UPDATE table SET   field='$value'WHERE id in ('$id'";

   } else {
      $sql .= ",'$id";
  }
}
$sql .= ")"; 
mysql_query($sql);

          ive also tried multiple statements in one go by separating them with ';' and got nowhere fast ... it puzzled me as it seems mysql can do it ... unless the command processor breaks them into separate queries before firing them off?

            Originally posted by flatpooks
            ive also tried multiple statements in one go by separating them with ';' and got nowhere fast ... it puzzled me as it seems mysql can do it ... unless the command processor breaks them into separate queries before firing them off?

            That's right. You have to split your queries up and forech them... as above.

              Heh, check phpMyAdmin's source for multiple querys 🙂

                Thanks for the answers, especially yours Lars, I will give that a go. As for checking phpMyAdmin's source, I was considering that, but I didn't feel like searching through a ton of code. I've never used phpMyAdmin, but I know that there's probably quite a bit of code there.

                Thanks again.

                  Originally posted by Thora_Fan
                  As for checking phpMyAdmin's source, I was considering that, but I didn't feel like searching through a ton of code. I've never used phpMyAdmin, but I know that there's probably quite a bit of code there.

                  There is, I took a look. I couldn't make sense of it. So, I'll be carefull to recommend that again 🙂

                  And Lars post was very good...

                    Write a Reply...