move words from txt file to mysql table

[hassank1]

I've a text file that contains words as follows :

 

word1

word2

word3

word4

word5

etc..

 

I want to create a table in mysql (id,word) ... and to place each word in a recrod..

 

ex : insert into table1 values('','word1');

insert into table1 values('','word2');

insert into table1 values('','word3');

 

is there a method or something to do that automatically ? because I won't do that manually for about 10,000 words :S !!

 

thx

 

 

[DarkWater]

<?php
$filename = 'foo.txt';
foreach (file($filename) as $line) {
    $line = mysql_real_escape_string($line);
    $query = "INSERT INTO table1 (word) VALUES ('$line')";
    //do query stuff
}
?>

 

That should be enough to get you started.

[Jeremysr]

This should do it:

 

$lines = file('your_filename');

foreach ($lines as $line) {
    $line = chop($line);
    mysql_query("INSERT INTO table1 values('', '$line');");
}

 

Edit: whoops, too late. It won't let me delete my post...

[hassank1]

Ok thx guys

 

I've about 10,000 words in my file.. so if I tried to insert all of them in a short time would that cause a problem or a may I get a warning from the hosting site ?

[DarkWater]

Well, I'm not your hosting company, so I don't know.  I'd say that it should be fine, but you never know.  You might as well try.

[.josh]

I did pretty much the exact same thing with about 27k lines (got an 'extended' English word list that I wanted to play around with), and it executed the script in like 2s.

[.josh]

oops. I just looked in my db and there's actually 227,000 rows.  But anyways, it only took a few seconds to execute when I first inserted.  And you only have 10,000.

[corbin]

If you're really worried about it, you could combine the queries to do like 100 inserts at a time.

[.josh]

You can't do more than 1 query at a time through php....or is there a way to insert multiple rows with 1 query? I'm no sql expert, just wondering.

[DarkWater]

INSERT INTO table (word) VALUES ('foo'), ('lol'), ('etc');

[corbin]

Yeah, the example DarkWater provided was exactly what I meant.

 

 

 

Oh, I think it's just MySQL by the way....  I don't think Oracle or MSSQL or any other dialect does it.

[DarkWater]

I'm 99% sure that it's NOT part of the SQL standard, so yeah. >_<

[PFMaBiSmAd]

There are also Prepared Statements and LOAD DATA LOCAL INFILE 'file_name' methods that would help when inserting large amounts of data.