1

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

Sarvap Praharanayuthan
  • 3,673
  • 6
  • 43
  • 65
jorduncan
  • 11
  • 2

2 Answers2

2

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

Tom
  • 6,173
  • 3
  • 17
  • 41
0

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);
A.B
  • 17,478
  • 3
  • 28
  • 54
  • that will run 6000 queries... you can do much better than that. – serakfalcon May 28 '14 at 16:59
  • 1
    basically he wants to insert it, he has not said that he wants performance, mysql can run taht much – A.B May 28 '14 at 17:05
  • 1
    yeah, but it's dumb to do it that way when you can use `LOAD DATA INFILE`, or even concatenate all the values into a single string and send it to the DB in a single query. To make things worse, your example uses the deprecated mysql extension. – serakfalcon May 28 '14 at 17:10
  • this can be right if question was about "with best approcah" or "with better performance" i just showed him a way to do it with php as its "php" question :) – A.B May 28 '14 at 17:16