上传文本文件每行进入mysql行

Question

I have email.txt stored in my server the path of email.txt is

/home/xxxx/public_htnl/email.txt

email.txt consists of several thousand emails of subscriber which gets updated daily. I want to import these emails to other database daily using cron.

I have tried this but its giving error

Could not load. Access denied for user 'test'@'localhost' (using password: YES)

I think I don't have permission to run LOAD DATA INFILE

My code is:

<?php

$db = mysql_connect('localhost', 'test', 'test')
or die('Failed to connect');
mysql_select_db('test', $db);


$string = file_get_contents("http://www.xyz.com/email.txt", "r");
$myFile = "/home/xxx/public_html/email.txt";

$fh = fopen($myFile, 'w') or die("Could not open: " . mysql_error());
fwrite($fh, $string);
fclose($fh);


$result = mysql_query("LOAD DATA INFILE '$myFile'" .
                  " INTO TABLE email");
if (!$result) {
die("Could not load. " . mysql_error());
}
?>

Any other good method can be accepted.please don't advice that store data directly in other database while storing emails etc..

Answer 1

As the root MySQL user, try running the query:

GRANT FILE on *.* to 'test'@'localhost';

If you still get the error, make sure your username and password for the test user are correct and that the host you are accessing MySQL from matches the host for the test user and password.

EDIT:

<?php

$db = mysql_connect('localhost', 'test', 'test') or die(mysql_error());

if (!mysql_select_db('test', $db)) die(mysql_error());

$emails = file("http://www.xyz.com/email.txt");

foreach($emails as $email) {
    $email = trim($email);
    if ($email == '' || strpos($email, '@') === false) continue;
    $email = mysql_real_escape_string($email);

    $query = "INSERT INTO `email` VALUES('$email')";
    $result = mysql_query($query);
    if (!$result) {
        echo "Failed to insert $email.  " . mysql_error() . "<br />
";
    }
}

If there are many, many emails, you can build an array of email addresses and do an extended insert every so often.

INSERT INTO emails VALUES('email1'), ('email2'), ('email3'), ('email4')