CODEWITHSUNDEEP
mysqlarraysbulkinsert
jorduncan
jorduncan

Reputation: 11

Inserting several thousand entries into MySQL with one query

I'm trying to store around 6,000 post codes into my database but of course I don't want to individually insert them.

I have a text file containing the post codes I need for the task

The text file looks like this,

SS99 6LB,SS8 9RA,SS7 9SR,SS7 9SS,SS7 9ST

I'd like to insert them all into the database in one hit.

The table structure is simply ID(INT), Postcode(VAR).

Upvotes: 1

Views: 199

Answers (2)

A.B
A.B

Reputation: 20445

first of all assign this text from the text file to a string

$string ='that text';
$arr = explode(",",$string);

thats an idea/concept you can now make a loop

$sql="";
foreach($arr as $ar)
{
 //$sql.=($sql=="")?"":",";
 //$sql.=$ar;
  mysql_query("insert into table(Postcodes) values ($ar)");
}

check syntax of quotes around postecodes according to your server

for performance you can write it will insert all in one query 

$sql="insert into table(Postcodes) values";
foreach($arr as $ar)
{
 $sql.=($sql=="insert into table(Postcodes) values")?"":",";

  $sql.="(".$ar.")";
}
mysql_query($sql);

Upvotes: 0

Tom
Tom

Reputation: 6663

You can use LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'filename.csv' 
INTO TABLE tablename
LINES TERMINATED BY ','
(Postcode) 
;

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 2

Related Questions