ElvansX Posted December 2, 2006 Share Posted December 2, 2006 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 -> disconnectfor 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?? Quote Link to comment Share on other sites More sharing options...
artacus Posted December 2, 2006 Share Posted December 2, 2006 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 statementfor($i=0; $i<count($sql); $i++) { mysql_query($sql[$i]) or die(mysql_error());}[/code] Quote Link to comment Share on other sites More sharing options...
ElvansX Posted December 3, 2006 Author Share Posted December 3, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.