Update multipe rows outside loop - need help

ElvansX · #1 (**permalink**) 12-02-06, 10:42

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

PHP 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'];



    $sql_update = "UPDATE table SET title = '$title' WHERE id=$id";

    mysql_query($sql_update) or die(mysql_error());

  }

This works just fine. Outside loop:

PHP 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'];



    $sql_update = "UPDATE table SET title = '$title' WHERE id=$id";

    $all_sql_update= "$all_sql_update $sql_update";

  } 

  mysql_query($all_sql_update) or die(mysql_error());

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

r937 · #2 (**permalink**) 12-02-06, 11:16

what version of mysql?

you should try a multi-table update --

Code:

update table1 as t1
inner
  join table as t
    on t.id = t1.id
   set t.title = t1.title

look! no loop at all!!

healdem · #3 (**permalink**) 12-02-06, 11:20

first guess I'd try putting a semi colon ";" after each SQL statement

$sql_update = "UPDATE table SET title = '$title' WHERE id=$id;";

I know mySQL query browser can handle multiple SQl statements in one iteration, so aside from how PHP talks to MySQL 5 then I dont think there should be a problem. It could be the there is a limit on the length (No characters in an SQL statement

However I do know that if you have a error in your SQL the Query Borwser halts execution, any statemets that have already run are OK, any after the fault are not run.

Unless youy KNOW that there is never going to be a fault in your SQL then the outside loop processing method should be fine, however I'd be tempted to use the in loop process... you have better controlk of error handling. The perfomance penalty isn't going that significant, after all you have already paid the pain of making the connection

HTH

ElvansX · #4 (**permalink**) 12-02-06, 11:39

Quote:

Originally Posted by healdem

first guess I'd try putting a semi colon ";" after each SQL statement

$sql_update = "UPDATE table SET title = '$title' WHERE id=$id;";

I know mySQL query browser can handle multiple SQl statements in one iteration, so aside from how PHP talks to MySQL 5 then I dont think there should be a problem. It could be the there is a limit on the length (No characters in an SQL statement

However I do know that if you have a error in your SQL the Query Borwser halts execution, any statemets that have already run are OK, any after the fault are not run.

Unless youy KNOW that there is never going to be a fault in your SQL then the outside loop processing method should be fine, however I'd be tempted to use the in loop process... you have better controlk of error handling. The perfomance penalty isn't going that significant, after all you have already paid the pain of making the connection

HTH

i tried already putting semicolon or comma end of each update code. Not working.

Any, this is what i meant:

PHP 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);

im using mysql ver 4, it that a problem??

r937 · #5 (**permalink**) 12-02-06, 11:43

what is CUSTOM_FUNCTION($title)? what does it do?

if this is a php function, then i am afraid you have no choice but to retrieve the data from mysql, perform the function on each row, and update it back (i.e. inside a loop)

if you can explain what the function is, maybe it can be done in mysq -- which is the only way you can avoid the loop

r937 · #6 (**permalink**) 12-02-06, 13:40

thread closed by request

Internet Services

Web Development

Digital Marketing

Consumer Tech