How can I parse this textfile?

[NuclearIce]

I have looked at literally hundreds of ways to parse but I do not know how to do it myself I just don't understand it really.

I have a word list text file like this

abbreviation
abuser
acceptances
acceptor
acceptors
accesses
accessories
accessory
accident
accidents

and it goes on about 100,000 words.


How could I grab those words and drop them into this table code to put them in my mysql database.


insert into `wordlist`(`wor d`) values ('this is where the words go');
insert into `wordlist`(`wor d`) values (word2');
insert into `wordlist`(`wor d`) values ('word3');

etc...

[NuclearIce]

Anyone out there that can help?

[Dormilich]

Anyone out there that can help?

… and is not on vacation.

for 100,000 words I recommend a Prepared Statement to speed up the writing process. additionally you can insert more than 1 entry per execution:

INSERT INTO `wordlist` (`word`) VALUES ('word1'),('word2'),('word3');

to get each word, read the file linewise (and while you are there, execute the Prepared Statement). see fgets()

[rudiedirkx]

If you're sure (I'm sure you are) every line contains one word, you can use file() which would make the whole thing possible with just one line of code (I love those):

$sql = 'INSERT INTO x (word) VALUES (\'' . implode("'), ('", array_map('trim', file())) . "');";

The array_map('trim ', [array]) is to clear all the newlines left in by file(). See php.net/file for details.

-- edit
You might want to addslashes() the list too.

[Dormilich]

I’m not quite sure, if an array of 100,000 members will slow down the script due to the memory usage …

[NuclearIce]

If I cut it down to 10,000?

[Dormilich]

I don’t know much about such issues. I only know that I would make it so, that I don’t have to deal with it. (which means that in my current state of enlightenment, I’d use Prepared Statements)

[rudiedirkx]

If the content is in a text file, you have to somehow get it into PHP and into MySQL, so at the very least all the content will be in the memory. I often have PHP scripts - intentionally - that use > 10 MB memory and they work just fine.
Just make sure you insert at least 2% of your content per query/in one query (so 2.000 words per INSERT query would be fine, 100.000 might be too much for MySQL (too long a query)). This way you need max 50 queries, which is still well manageable.

What you could also do, is make a CSV or XML or XLS from the textfile and import it into MySQL using either PhpMyAdmin or the native MySQL import program. Most secure and fast solution, but wouldn't be automatic, but manual.

[Dormilich]

go for the manual import.