使用具有Integer值的变量的PHP mySQL INSERT语句

Question

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

Update #1:

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    |                |
+-------------+------------------+------+-----+---------+----------------+

Update #2

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

Answer 1

UTF-8 Byte-Order-Mark (BOM)

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.¹

$ 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

Remove the BOM from the file

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

• Windows GUI editor: (Notepad ++)

• Using sed²:

  sed '1s/^\xEF\xBB\xBF//' < foo-withBOM > foo-withoutBOM.txt
  

• Using dos2unix command ³

• Using the tail command ⁴

  tail -c +4 foo-withBOM.txt > foo-withoutBOM.txt

---

¹Getting error “1366 Incorrect integer value: '1'” when importing file

²https://unix.stackexchange.com/a/381263/107777

³https://unix.stackexchange.com/a/381237/107777

⁴https://unix.stackexchange.com/a/381231/107777