Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Unanswered: Update multipe rows outside loop - need help

    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($resultMYSQL_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($resultMYSQL_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??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2006
    Posts
    2
    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($resultMYSQL_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??
    Last edited by ElvansX; 12-02-06 at 12:43.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thread closed by request
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •