Page 1 of 1

Massive MySQL queries

Posted: Tue Aug 01, 2006 4:10 pm
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]

Posted: Tue Aug 01, 2006 4:26 pm
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.

Posted: Tue Aug 01, 2006 4:36 pm
by volka
...and read about prepared statements supported bei mysqli and pdo
http://php.net/mysqli
http://php.net/pdo

Posted: Tue Aug 01, 2006 5:16 pm
by RobertGonzalez
Or you could use something designed for things like that... like Big Dump.

Posted: Tue Aug 01, 2006 5:23 pm
by Luke
aaahahaha big dump! :lol: :lol:

Posted: Tue Aug 01, 2006 5:24 pm
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?

THX

Posted: Thu Aug 03, 2006 3:26 pm
by sneaky
Thx Volka, I think prepared statements are exactly what I'm looking for.

-sneaky