#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    205
    Rep Power
    35

    Is There A Way To Make This Better and Quicker?


    I am trying to check a field in a database table to check for a value and then update each field. My code works but it takes too long and usually times out since the table has nearly 200,000 rows and processes each field individually.

    Or is the only solution to add a limit in there and do it with pagination?

    PHP Code:
    <?php

    $result 
    mysql_query("SELECT field FROM table") or die (mysql_error());
    while (
    $row mysql_fetch_array($result))
    {
    // Check field for something and update from this to that
     
    if(field == "this"){
     
    mysql_query("UPDATE table SET field= 'that' WHERE field='something'");
     }
    }

    ?>
    Last edited by Chet; May 1st, 2013 at 10:29 AM.
    Thank You,

    Chet
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    UPDATE table SET field= 'that' WHERE field='that';

    Note that you are currently setting all field to 'that'.

    Comments on this post

    • ManiacDan agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    205
    Rep Power
    35
    Originally Posted by NotionCommotion
    UPDATE table SET field= 'that' WHERE field='that';

    Note that you are currently setting all field to 'that'.
    That code was just an example...
    I updated it to add the where part.
    Last edited by Chet; May 1st, 2013 at 10:33 AM.
    Thank You,

    Chet
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,008
    Rep Power
    376
    Originally Posted by Chet
    I am trying to check a field in a database table to check for a value and then update each field. My code works but it takes too long and usually times out since the table has nearly 200,000 rows and processes each field individually.

    Or is the only solution to add a limit in there and do it with pagination?

    PHP Code:
    <?php

    $result 
    mysql_query("SELECT field FROM table") or die (mysql_error());
    while (
    $row mysql_fetch_array($result))
    {
    // Check field for something and update from this to that
     
    if(field == "this"){
     
    mysql_query("UPDATE table SET field= 'that' WHERE field='something'");
     }
    }

    ?>
    $query =

    UPDATE table
    SET field = IF (field = this, that, this)
    WHERE field = 'something'

IMN logo majestic logo threadwatch logo seochat tools logo