Massive MySQL queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sneaky
Forum Newbie
Posts: 2
Joined: Tue Aug 01, 2006 4:00 pm

Massive MySQL queries

Post by sneaky »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi, this is my first post on this forum. I am trying to add large amounts of data to an sql database using php. The fields being added have 3 columns and anywere from 1,000 to 10,000 rows. The primary key for each field is set to auto increment. The way I am doing it right now is this:

Code: Select all

$i = 0;
foreach($un as $val){
 mysql_query("INSERT INTO database(un,pw,login) VALUES('$val','$pw[$i]','$login[$i]')") or die(mysql_error());
 $i++;
}
the $i variable is just to keep the $pw and $login values the same as the $un values. This of course does one mysql_query per array element in the $un array. I am fairly new to sql/php and am not sure if there is a better way to enter large amounts of data. This method takes a long time to execute, if anyone knows how I could speed the process up I would greatly appreciate it.

-sneaky


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL supports what are referred to as extended inserts, where multiple rows can be inserted in the same query. There are various length limitations but you can pool many rows before those become a problem. You may also want to look into having MySQL import the data from a file.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

...and read about prepared statements supported bei mysqli and pdo
http://php.net/mysqli
http://php.net/pdo
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Or you could use something designed for things like that... like Big Dump.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

aaahahaha big dump! :lol: :lol:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

The only reason I didn't put any warnings in my post is because I knew you would violate them anyway. :twisted: Ok, now that the fun is over, sahll we get back on track?
sneaky
Forum Newbie
Posts: 2
Joined: Tue Aug 01, 2006 4:00 pm

THX

Post by sneaky »

Thx Volka, I think prepared statements are exactly what I'm looking for.

-sneaky
Post Reply