Previous Thread
Next Thread
Print Thread
Hop To
#222098 02/08/2009 2:15 AM
Joined: Mar 2008
Posts: 326
D
Enthusiast
Enthusiast
D Offline
Joined: Mar 2008
Posts: 326
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:
PHP Code
$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
SD Offline
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.. 2c

Joined: Aug 2006
Posts: 1,358
Y
Veteran
Veteran
Y Offline
Joined: Aug 2006
Posts: 1,358
In this case, how about rewriting your query...

SQL 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.


[Linked Image from siemons.org]
Joined: Mar 2008
Posts: 326
D
Enthusiast
Enthusiast
D Offline
Joined: Mar 2008
Posts: 326
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
SD Offline
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! smile


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Version 7.7.5 Images suddenly not displaying
by Stovebolt - 05/04/2024 11:19 AM
Bots
by Outdoorking - 04/13/2024 5:08 PM
Do I need to rebuild my database?
by Baldeagle - 04/07/2024 2:58 AM
Who's Online Now
2 members (Nightcrawler, Ruben), 878 guests, and 139 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20240506)