Update multiple rows outside loop - need help

[ElvansX]

I know it possible to update rows in loop. But how about outside the loop?
Normally i update in loop like this:

[code]
  $query = "SELECT id, title FROM table1 ORDER BY id ASC";
  $result = mysql_query($query) or die(mysql_error()); 
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $id = $row['id'];
    $title = $row['title'];

    $title = CUSTOM_FUNCTION($title); // change title's value

    $sql_update = "UPDATE table SET title = '$title' WHERE id=$id";
    mysql_query($sql_update) or die(mysql_error());
  } [/code]

This works just fine. Outside loop:
[code]
  $query = "SELECT id, title FROM table ORDER BY id ASC";
  $result = mysql_query($query) or die(mysql_error());
  $all_sql_update= "";
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $id = $row['id'];
    $title = $row['title'];

    $title = CUSTOM_FUNCTION($title); // change title's value

    $sql_update = "UPDATE table SET title = '$title' WHERE id=$id";
    mysql_query($sql_update) or die(mysql_error());
   
    // if i put function here working, but slow or time out.
    // connect other hosting db, select db, update, dc 
    // FUNCTION_UPDATE_OTHER_DB($sql_update);

    // Collect all update
    $all_sql_update= "$all_sql_update $sql_update";
  }
 
  // Not working :
  FUNCTION_UPDATE_OTHER_DB($all_sql_update); 
[/code]

This one wont work! I tried put after each update end with a comma (like insert), not working also.
Is this possible to make update outside loop?

Actually i need this to update other DB on other hosting. If i put the Update Function to update DB on other hosting in loop it wil works, but took long times to update each of it.

Connnect -> look for DB -> update row -> disconnect

for every rows. The database is not big, but if too much rows to update it will take long time or time out.
I did for Insert, well that easy.

Anyone done this before??

[artacus]

Yeah, PHP's mysql client only lets you define 1 query per mysql_query call. This is a security feature.

What you can do is [code]
$sql  = explode(';',$sql_update); //you need to add the ; in the original statement
for($i=0; $i<count($sql); $i++) {
    mysql_query($sql[$i]) or die(mysql_error());
}
[/code]

[ElvansX]

Thx! Get idea how to solved it after ur reply  :lol:

I create another function similar to FUNCTION_UPDATE_OTHER_DB, add code to count the records to update, then update 1 by 1. Like this

[CODE]
$query = "SELECT id, title FROM table ORDER BY id ASC";
  $result = mysql_query($query) or die(mysql_error());
  $all_sql_update= "";
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $id = $row['id'];
    $title = $row['title'];

    $title = CUSTOM_FUNCTION($title); // change title's value

    $sql_update = "UPDATE table SET title = '$title' WHERE id=$id";
    mysql_query($sql_update) or die(mysql_error());
       
    // Collect all update
    $all_sql_update= "$all_sql_update $sql_update;";
  }
 
  // Working now!
  FUNCTION_UPDATE_MANY_FOR_OTHER_DB($all_sql_update);
[/CODE]

this is important:
[CODE]$all_sql_update= "$all_sql_update $sql_update;";[/CODE]
put semicolon to seperate each update records. Make sure ur update didnt use any semicolon or else replace with something else.

then the function is something like this:

[CODE]
function FUNCTION_UPDATE_MANY_FOR_OTHER_DB($sql_query) {
   
    /// Count how many records to update ///
    $explode_sql_query = explode(";", $sql_query);
    $total_sql_query = count($explode_sql_query);

    /// Connect other host script here ///
 
    for ($i=0; $i<$total_sql_query; $i++) {
      mysql_query($explode_sql_query[$i]) or die(mysql_error());
    }

    /// Close connection here ///
}
[/CODE]

* If you believed in your programming, you will always find answer for your problem! 
Hope this will helps someone else :D