|
|
|
|
Joined: Mar 2008
Posts: 327
Enthusiast
|
|
Enthusiast
Joined: Mar 2008
Posts: 327 |
I have created a table and would like to insert multiple fields for each POST_ID in my POSTS table. I wrote a script but apparently it is too large of a task for the server to handle (my forum currently has 176,457 posts.) I dug around the dorebuildcontent script to try to reverse engineer it to suite my needs to no avail. Here is the code below I am working with: $query = "
select POST_ID
from ubbt_POSTS
";
$sth = $dbh -> do_query($query);
$total = $dbh -> total_rows($sth);
for($a=0;$a<$total;$a++){
list ($postid) = $dbh -> fetch_array($sth);
$postrow[$a]['postid'] = $postid;
}
for($a=0;$a<$total;$a++){
$query = "
insert into ubbt_NEW_TABLE (POST_ID)
values ({$postrow[$a]['postid']})
";
$sth = $dbh -> do_query($query);
}
How can I make this step through 200 posts at a time so it will work?
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
if you have shell axx, then you can do it in one shot and not have to worry about the php timeout.. 
|
|
|
|
|
Joined: Aug 2006
Posts: 1,360 Likes: 2
Veteran
|
|
Veteran
Joined: Aug 2006
Posts: 1,360 Likes: 2 |
In this case, how about rewriting your query...
INSERT INTO ubbt_NEW_TABLE(POST_ID)
SELECT POST_ID
FROM ubbt_POSTS
that query took 0.2464 seconds to execute for about half a million posts. Does require mysql 4.1 or above.
|
|
|
|
|
Joined: Mar 2008
Posts: 327
Enthusiast
|
|
Enthusiast
Joined: Mar 2008
Posts: 327 |
Wow, that worked like a charm Yarp! I never knew you could combine INSERTs with SELECTs. I'm running PHP5x, always like to keep things up-to-date.
Thanks again!
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
yes, nested queries are something that came about in 4.4 ? not sure, but it is sure nice to take advantage of.. at some point, i'd hope we bump up the mysql version so the stock code can start taking advantage of it to! 
|
|
|
|
|
|
|
![[Linked Image from siemons.org]](http://www.siemons.org/foto/yarp.gif)
