Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how can I update 1000s of rows?

Posted on 2008-06-10
2
Medium Priority
?
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

722 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