how to insert row-by-row of content in a textfile into a mysql database?

Question

i have a textfile that contains content like this

bob
james
diva
games
library
info

and i was wondering how i can write a php script that would read the file line by line and insert the data into the database line by line as a new row for each line?

thanks

Answer 1

$conn = mysql_connect( 'localhost', 'root', 'password' );
if ( is_resource( $conn ) ) {
    if ( mysql_select_db('database', $conn) ) {
        $lines = file('file.txt')

        foreach ($lines as $line) {
            $query = mysql_query( 'INSERT INTO table (text) VALUES ( "' . $line . '" ) );
            if( !$query ) { die ('Error: ' . mysql_error(); ); }
        }
    }

}

Answer 2

There are a few approaches you can use depending on how big the file is.

1. Use MySQL - mysql can import CSV files directly using LOAD DATA [LOCAL] INFILE

2. PHP (small file): use file() to load the file into an array. Loop over the array and INSERT each line

3. PHP (large file): use fgets() to loop over the file a line at a time. Use this to build a bulk insert query (INSERT INTO ... VALUES (1), (2), (3))

Answer 3

This is a super-simplified answer but here's some of the basics:

$filename = "filename.txt";

while (!feof($fp)){
$string = fgets($fp, 1024); 
mysql_query("INSERT INTO <table> (<fieldname>) VALUES('$string')") 
}

Answer 4

Something like this should work:

$fh = fopen('file.txt','r');
$db =  new PDO("mysql:host=$host;dbname=$database", $username, $password);
while (!feof($fh)) {
  $line = fgets($fh);
  $db->exec("INSERT INTO mytable(line) VALUES ('$line');");
}

Answer 5

Be careful about feof and plaintext, feof don´t return true after read last line and put your loop infinit

make a doublecheck :S

while(!feof($fh)){
    $linha = fgets($fh,4096);
    if (!feof($fh)) { // doublecheck
        proccess what you need
    } else {fclose($fh); break 1;} // real eof
}

make a test