Jump to content

Update multiple rows outside loop - need help


ElvansX

Recommended Posts

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??


Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.