Insert values ​​from csv file into MySQL table

Question

I am trying to insert values ​​extracted from csv file into mysql table. It works, but the table is not populated. I tried to debug the last XXXX, but just can't see my mistake. Repeating the values ​​gives me the correct SQL, but when it comes to INSERT, there are no dice.

Many thanks for your help.

<?php 

$host = 'localhost';
$user = 'fulltime_admin';
$pass = 'secret';
$database = 'fulltime_db';

$db = mysql_connect($host, $user, $pass);
mysql_query($database, $db);

//////////////////////////////// EDIT //////////////////////////////////// 

$redirect_num = 500;   // Select how many rows to insert each time before refresh. 
// More rows = faster insertion. However cannot be too high otherwise it will timeout. 

$filename = "ps4_emails.csv"; // The file we are going to get the data from... 

$table = "`ps4_emails`"; 

////////////////////////////// END EDIT ////////////////////////////////// 

$file = file($filename); 
$lines = count($file); 

// Have we just redirected? 
$nextline = $_GET['nextline']; 
if (!isset($nextline)){ 
    $nextline = 0; 
} 

$query = "INSERT INTO ".$table." (email) VALUES ('".$final_line[0]."')";

for ($line=$nextline; $line<=$lines; $line++){ 

    $final_line = explode(",", $file[$line]); 

    if ($line!=$lines){ 
        mysql_query($query,$db); 

    } 

    if ($line % $redirect_num){ 

        // something needs to go here
    } else { 
        $nextline = $line+1; 
        exit ('<meta http-equiv="refresh" content="0;url=texttomysqlemails.php?nextline='.$nextline.'" />'); 
    } 

    echo  ( $line==$lines ) ? "Done" : ""; 

} 
?>

Answer 1

Put your query inside a loop to use it with a variable $final_line.

Try the following:

$final_line = explode(",", $file[$line]); 

if ($line!=$lines){ 
   $query = "INSERT INTO ".$table." (email) VALUES ('".$final_line[0]."')";
    mysql_query($query,$db); 
} 

Do not use mysql_*. It is deprecated and removed from PHP 7. Use mysqli_*or PDO.

Answer 2

script PHP CLI, .

, , PHP, , , fgetcsv(), csv .

<?php 

    $host = 'localhost';
    $user = 'fulltime_admin';
    $pass = 'secret';
    $database = 'fulltime_db';

    $db = mysql_connect($host, $user, $pass);
    mysql_query($database, $db);

    $filename = "ps4_emails.csv"; 
    $table = ""; 

    $handle = fopen('ps4_emails.csv', 'r');
    if ( ! $handle ) {
        echo 'File does not exists in this location';
        exit;
    }

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $query = "INSERT INTO `ps4_emails` (email) VALUES( '{$data[0]}')";        

        mysql_query($query,$db); 
    }
?>

script /,

>PHP .php

// - .

, - , .

, mysql_, ( PHP7) , PHP, PDO mysqli_ , ,

, , , - - .

<?php 

    $host = 'localhost';
    $user = 'fulltime_admin';
    $pass = 'secret';
    $database = 'fulltime_db';

    $restart_from = 0;

    $db = mysql_connect($host, $user, $pass);
    mysql_query($database, $db);

    $filename = "ps4_emails.csv"; 
    $table = ""; 

    $handle = fopen('ps4_emails.csv', 'r');
    if ( ! $handle ) {
        echo 'File does not exists in this location';
        exit;
    }

    // is it a restart?
    if ( file_exists('restart.txt') ) {
        // its a restart
        $restart_from = file_get_contents('restart.txt');

        // read up the file to the last good row inserted
        for ( $i=0; $i<=$restart_from; $i++ ) {
            $data = fget($handle, 1000);
        }
    }

    $upd_cnt = restart_from;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $query = "INSERT INTO `ps4_emails` (email) VALUES( '{$data[0]}')";        

        mysql_query($query,$db); 

        $upd_cnt++;
        file_put_contents('restart.txt', $upd_cnt);
    }
?>

, . , , , , .

Answer 3

LOAD DATA INFILE .

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

insert csv file data into mysql