PHP mySQL INSERT Statement using variables with Integer value

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

Ok, first of all, your query is vulnerable to Sql Injection....however, if you want to continue on that path, you need to concatenate the value, like: `$sql = "INSERT into link_titles (title_id, title, description) VALUES (".$data[0].", '$data[1]', '$data[2]')";`...if you want to do things right, you should go for prepared statements http://php.net/manual/es/pdo.prepared-statements.php — Hackerman, Jun 28 '18 at 21:53
Just curious, have you considered LOAD DATA INFILE for this? — Don't Panic, Jun 28 '18 at 21:53
@Hackerman - thanks for the info. Just so you know, this isn't something I'm doing for public consumption...it's merely to import data into a table and learn some PHP while at it. Right after typing the question, I continued my research and found this on [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php). I attempted it and got the same results. — Allan, Jun 28 '18 at 22:00
@Don'tPanic - Yes. I considered it but went with PHP so I can get more experience on it (I'm not a dev). I may end up that way, but I'd like to learn how solve this issue for future use. — Allan, Jun 28 '18 at 22:01
@Bleach - I don't have that anymore (did it directly in mySQL). I can provide you with a description of the table (`desc link_titles;`) if that will work — Allan, Jun 28 '18 at 22:41

score 1 · Answer 1 · answered Jun 28 '18 at 23:05

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);

Though this wasn't the solution (it was a BOM issue), I do appreciate the coding technique suggestion and I have already changed my code using this technique. — Allan, Jun 29 '18 at 15:25
Yeah PHP isn't the best with integer types ;-) best of luck to ya @Allan I would be curious if a mysql function could have solved your problem as well. — akahunahi, Jul 02 '18 at 21:28
Trying to import them directly through mySQL is what got me researching - it also gave me an integer error (taking PHP out of the equation). — Allan, Jul 03 '18 at 13:29

score 0 · Accepted Answer · answered Jun 29 '18 at 15:24

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

score -1 · Answer 3 · edited Jun 20 '20 at 09:12

-1

its var $data[0] is not an integer

try this function ==> intval($data[0]);

http://php.net/manual/pt_BR/function.intval.php

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 28 '18 at 23:08

A Smythy C Costa

72
4

1

Same as if I used `(int)$data[0];` the values comes out to `0` (zero) – Allan Jun 28 '18 at 23:11
$ data [0] is a string. because you do not change your column in the title_id database int (10) to title_id varchar () – A Smythy C Costa Jun 28 '18 at 23:25