Solved

how can I update 1000s of rows?

Posted on 2008-06-10
2
167 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
ID: 21756546
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
ID: 21757075
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

20 Experts available now in Live!

Get 1:1 Help Now