Solved

how can I update 1000s of rows?

Posted on 2008-06-10
2
166 Views
Last Modified: 2013-12-13
hi experts,

using the function update1 I am trying to do the following:

start with: $limit1 =0;
use: update1($limit1);
echo $mymessage;

refresh the page ...
$limit1 +=50;
use: update1($limit1);
echo $mymessage;

refresh again until $limit1 > mysql_num_rows($query).


regards,
update1($limit1) {

.....

mysql_query("UPDATE  ... WHERE .... ORDER BY id LIMIT $limit1,50");
 

}

Open in new window

0
Comment
Question by:uk1900
2 Comments
 
LVL 31

Accepted Solution

by:
Frosty555 earned 500 total points
Comment Utility
I'm guessing you want to do this so that you can do the updates in batches without the database hanging while it tries to update all the rows.

Your idea looks okay to me. The update command takes only a single number for the limit clause. Pass it 50, and UPDATE will update 50 rows, and then return. Simply devise your statement so that performing the update causes the record to no longer satisfy the WHERE clause, you can just call that over and over to keep updating more and more until zero rows are returned, in which case you're done.

Another thing to think about is that the UPDATE command will not be noticably slow until you are updating tens of thousands of records. Updating a mere 1000 records is nothing for MySql. Even on my desktop computer I can perform update statements on databases with hundreds of thousands of records, and it only takes 15-20 seconds or so to complete. So only in the most extreme of circumstances, where you have many tens of thousands of records, and users who are so impatent that they just can't sit and wait for it to finish, should you have to worry about this at all.
0
 

Author Comment

by:uk1900
Comment Utility
I forced to use only 50 rows each time because the value to update depends on $limit1
In other words, the value to update in the first  50 rows, is not the same in the next 50 rows &.

That's why I need to repeat this while $limit1 < $max:
--------------------------
refresh the page ...
$limit1 +=50;
use: update1($limit1);
echo $mymessage;
--------------------------

Q: how can I refresh the page and echo a message each time?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now