Take text from a file and insert to a mysql table using a script

Question

I would like to take data (some text) from a file and insert it into a mysql field, using a script.

I have tried:

mysql -u root -p password dbname << INSERT INTO tblename (fieldname) VALUES (LOAD_FILE('filename'));

and

mysql -u root -p password dbname << EOF INSERT INTO tblename (fieldname) VALUES ('$filename'); EOF

Solution

Not strictly Ubuntu related, but you want LOAD DATA INFILE, the documentation of which can be found at:

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

Probably something like..

echo "LOAD DATA INFILE 'filename' INTO TABLE tblename;" | mysql -u root -p password dbname

You could also use dbname.tblename and then drop dbname from the mysql command, they'll be equivalent.

Comments

[Matt]

I wrote it in PHP and then added php file.php to a script

[Matt]

Thank you for your Answer, I did not know you could do it that way. I have given you a one up, but it is still not taking the data from the file and putting it in the field, it just comes up with null in the field. Im using Ubuntu if that helps. Thank you.

[Caesium]

I guess you'd have to post your table schema and a sample of the file for us to work out what might be going wrong :)