I'm trying to import a CSV file into MySQL database but it didn't work.
I have 5 columns in my database:
id , userID, movieID, rate, timestamp
And in my CSV file I have only 4 columns separated by two colons '::'.
This is the sample data:
1::1193::5::978300760
This is my query
$query = "LOAD DATA INFILE '$uploadfile' INTO TABLE rating_table FIELDS TERMINATED BY '::' LINES TERMINATED BY ' ' ( userID, movieID, rate, timestamp)";
And I'm using PDO_MySQL
this is the additional code so that you can understand.
$stmt = $dbh->prepare($query);
try{
if($stmt->execute()){
echo "Sucessful importing of CSV file into the MySQL database!";
}
}
catch (PDOException $e) {
echo "Error importing of CSV file into the MySQL database!";
echo "Error!: " . $e->getMessage() . "<br/>";
die();
}
echo "</p>";
I've tried many times but when I've checked in my database, it is still empty.
I can't seem to figure out what is wrong, can you help me? Thanks!
This is the error I've got.
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in C:\xampp\htdocs\movie\php\upload_file.php:57 Stack trace: #0 C:\xampp\htdocs\movie\php\upload_file.php(57): PDOStatement->execute() 1 {main} thrown in C:\xampp\htdocs\movie\php\upload_file.php on line 57
This is my connection settings:
$dbh = new PDO('mysql:host=localhost;dbname=movie','root', '', array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8", PDO::ATTR_EMULATE_PREPARES => FALSE, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE ));