Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how can I update 1000s of rows?

Posted on 2008-06-10
2
Medium Priority
?
175 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 1500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

876 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