0

I want to insert some data into my MySql table using PDO. If it's not empty it will have Integer datatype so i use PDO::PARAM_INT, but the problem is this data is not required so sometimes it's empty.

It make me have an error:

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value.

So is it anyway I can pass this empty value variable into my database without having error when it have empty value? here's my code snippet: 

$stmt = $db->prepare("INSERT INTO table (column) VALUES (:column)");
$stmt->bindParam(':column',$_POST['value'], PDO::PARAM_INT);
$stmt->execute();
wscourge
  • 10,657
  • 14
  • 59
  • 80
Ying
  • 1,282
  • 4
  • 19
  • 34

2 Answers2

-1

An empty string is incorrect integer value all the same. Although been permissive in the past, nowadays MySQL tends to be more strict with types, enforcing the STRICT MODE by default, hence not accepting an empty string for the integer field anymore.

Besides, PDO::PARAM_INT doesn't cast your values.

Therefore, you have to cast them manually

In case you want to retain a possible NULL value, convert your input like this

$value = is_null($_POST['value']) ? null : (int)$_POST['value'];

If you want to just convert to integer unconditionally, then just make it

$value = (int)$_POST['value'];

Both snippets will convert an empty string to 0 which will be gladly accepted by MySQL

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • so it's not possible to use Bindparam in this case? – Ying Mar 05 '17 at 09:55
  • nice, but may i know why bindparam is ugly and useless? is it batter to sanitizing than using bindparam or there is any saver way than this? – Ying Mar 05 '17 at 10:23
  • there is no difference between NULL and null, so I dont see how it would be solved that way – Phil May 17 '18 at 11:31
  • also casting a null to int , will store 0 in the db, it will not set the field to NULL – Phil May 17 '18 at 11:42
-2

In My Case I use like this:

if(empty($_POST['value'])){ $value = NULL; } else { $value = $_POST['value']; }
$stmt->bindParam(':column', $value);

It works Best for me, try from your side :-)

Umesh Gaire
  • 23
  • 5