I am trying to import a (simple) CSV file into a MySQL table.
I can connect fine and I have tested the statement with some dummy data and verfied that it writes to the table correctly. My problem comes in when I attempt to use variables in the statement. Example:
$sql = "INSERT into `link_titles` (`title_id`, `title`, `description`) VALUES ('$data[0]', '$data[1]', '$data[2]')";
$conn->exec($sql);
This generates the error:
Uncaught PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '5' for column 'title_id' at row 1
So, I assume that that something is wrong with the first variable and I remove the single quotes from the first variable ($data[0]):
$sql = "INSERT into `link_titles` (`title_id`, `title`, `description`) VALUES ($data[0], '$data[1]', '$data[2]')";
$conn->exec($sql);
However, if I manually enter in the integer '5' (the value that's being read) it works.
$sql = "INSERT into `link_titles` (`title_id`, `title`, `description`) VALUES (5, '$data[1]', '$data[2]')";
$conn->exec($sql);
I've researched converting a string to an integer (PHP is supposed to handle this), but when I attempt to do so, it converts to a 0
echo (int)$data[0]; <------ Results in 0
I'm at a loss. A point in the right direction would we awesome
This is the mySQL command I used to create the link_titles table:
CREATE TABLE link_titles( id int not null auto_increment primary key, title_id int(10) unsigned not null, title varchar(120) default null, description varchar(512) default null, FOREIGN KEY fk_id(title_id) REFERENCES links(id) ON UPDATE CASCADE ON DELETE RESTRICT );
Resulting in this table:
desc link_titles;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title_id | int(10) unsigned | NO | MUL | NULL | |
| title | varchar(120) | YES | | NULL | |
| description | varchar(512) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
Taking the advice from the comments and the answer provided by @Ligemar, I modified my PHP code as follows:
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = $conn->prepare("INSERT into link_titles (title_id, title, description)
VALUES (:tid, :title, :description)");
$sql->bindParam(':tid', $tid);
$sql->bindParam(':title', $title);
$sql->bindParam(':description', $description);
//while loop to parse CSV file excluded for brevity
$tid = $data[0];
$title = $data[1];
$description = $data[2];
$sql->execute();
As before, I still get the same error:
Uncaught PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '5' for column 'title_id' at row 1
After much consternation leading to copious amounts of research, I was able to figure out the issue: UTF-8 Byte-Ordered-Mark (BOM) at the beginning of the file.1
$ file test.csv
test.csv: UTF-8 Unicode (with BOM) text, with very long lines, with CRLF, LF line terminators
After fixing the file, I was able to run the code with no problem resulting in successfully importing the file.
file test-nobom.csv
test-nobom.csv: UTF-8 Unicode text, with very long lines, with CRLF, LF line terminators
Apparently, Windows OSs (though this was done in Excel for Mac) adds this bogus character to the front of these text files. I found a number of ways to fix it
Using sed2:
sed '1s/^\xEF\xBB\xBF//' < foo-withBOM > foo-withoutBOM.txt
Using dos2unix command 3
Using the tail command 4
tail -c +4 foo-withBOM.txt > foo-withoutBOM.txt
1Getting error “1366 Incorrect integer value: '1'” when importing file
2https://unix.stackexchange.com/a/381263/107777
I'm going to assume you're coding this from PDO because you use the ->exec method.
Please use parameters: http://php.net/manual/en/pdo.prepare.php
Especially if you are just starting out with PHP, doing SQL injection will make you a poor developer and creates bad habits. Using a prepared SQL statement is the correct way to do it because the queries will be safe from injection attacks, you can change the DB backend easier, commit transactions with better debugging, and can provide caching and other things "sever-side".
Try something like this:
<?php
$sql = 'INSERT into `link_titles` (`title_id`, `title`, `description`)
VALUES (:first, :second, :somethingelse)';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$result = $sth->execute(
[
':first' => $data[0],
':second' => $data[1],
':somethingelse' => $data[2]
]
);
var_dump($result);
try this function ==> intval($data[0]);