Php PDO: Why does Inserting NULL yields to 0

Question

I searched any possible help that can be found online but still the problem with INSERT NULL using PHP PDO persists.

The script is a csvupload script originally came from here Import CSV into MySQL

To make the story short, Let me present the possible cause..

if($linearray[4]=='Unknown')
    $linearray[4]=null;
$linemysql = implode("','",$linearray);
$linemysql = "'".$linemysql."'";
$setsu->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$tsuika = $setsu->prepare("INSERT INTO tablename (SubAgentID, BookID, AgentID, SubAgentName, Risk, Area, CurrentBalance) VALUES ($linemysql)");
$tsuika -> bindValue(':Risk', null, PDO::PARAM_INT);
$tsuika ->execute();

Looking the code above, I explicitly set the field values on the prepare statment. On phpmyadmin the Risk field accepts NULL, set the default value to NULL, and has no problems. But when doing INSERT with PHP the value it gets is 0. Why?

Before Inserting, I echoed it and if the field $linearray[4] contains Unknown, it converts it to NULL yielding, '' for that part.

table structure

CREATE TABLE IF NOT EXISTS `subagentdb` (
`SubAgentID` int(10) NOT NULL AUTO_INCREMENT,
  `BookID` int(10) NOT NULL,
  `AgentID` int(10) NOT NULL,
  `SubAgentName` varchar(30) NOT NULL,
  `Risk` float DEFAULT NULL,
  `Area` varchar(20) NOT NULL,
  `CurrentBalance` float NOT NULL,
  PRIMARY KEY (`SubAgentID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

Your code seems to be good. The problem is not coming from ``` when you bind the value: `bindValue(':'Risk'', NULL, PDO::PARAM_INT);` ? — Debflav, Jul 30 '14 at 07:11
What else could I do? I'm not that php expert so honestly I'm lost — leipzy, Jul 30 '14 at 07:23
A couple of things; php's default is `null` (lowercase), so you could try that or maybe using `$setsu->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` above the `$setsu->prepare`. — l'L'l, Jul 30 '14 at 07:24
What do the backticks on ***':`Risk`'*** do? I've never seen it that way before. — l'L'l, Jul 30 '14 at 07:28
@I'L'l phpmyadmin uses backticks. I just I applied it here on the codes — leipzy, Jul 30 '14 at 07:36
@l'L'l I put $setsu->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); before $setsu->prepare but still it receives 0 — leipzy, Jul 30 '14 at 07:40
Have you tried to remove the backticks in your `bindValue()` ? Me too, I've never seen this before. — Debflav, Jul 30 '14 at 07:42
also removed the backticks on prepare statement but still its 0 — leipzy, Jul 30 '14 at 07:46
You might want to edit your question and post your updated code. — l'L'l, Jul 30 '14 at 09:49

score 0 · Answer 1 · answered Jul 30 '14 at 10:09

0

You're binding the value explicitly as PDO::PARAM_INT. Whatever value you pass will be cast to an int because of that. null casts to 0.

To actually bind an SQL NULL value, you need to bind the value as PDO::PARAM_NULL.

answered Jul 30 '14 at 10:09

deceze

491,798
79
706
853

I found out that explicitly putting NULL on prepared statements works... $tsuika = $setsu->prepare("INSERT INTO ".$tblmei." VALUES (7728,1,26,'testname1',NULL,'testArea',0)"); But since the script is a csv upload script any ideas on how I can explicitly put NULL on the query? – leipzy Aug 02 '14 at 07:49
Yes, if you literally write the SQL constant `NULL` into your query, it works. To do the same thing with bound parameters, **bind a PHP `null` value as `PDO::PARAM_NULL`!** Not `PDO::PARAM_INT`. – deceze Aug 02 '14 at 08:04
a while ago I havent used any PDO::PARAMs and it still works well I got an idea on how would I solve this – leipzy Aug 02 '14 at 08:08

score 0 · Answer 2 · edited Jul 30 '14 at 10:32

0

just use PDO::PARAM_NULL instead of PDO::PARAM_INT ? I think the NULL is converted to 0 (INT) instead of null value

edited Jul 30 '14 at 10:32

deceze

491,798
79
706
853

answered Jul 30 '14 at 10:11

DanIdle

41
7

It's still 0. The reason I code it that way is because I considered the featured answer here http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo – leipzy Jul 31 '14 at 04:39
you may try with this `$tsuika -> bindValue(':Risk', null, PDO::PARAM_STR);` can you tell us more about your table params and your php & PDO version ? – DanIdle Jul 31 '14 at 08:37
Still 0. I updated my post. You may look on the table structure. As per phpmyadmin, I saw Apache/2.4.7 (Win32) OpenSSL/1.0.1e PHP/5.5.9 – leipzy Jul 31 '14 at 08:46
why are you trying to bind a null value , if it is already in your $linemysql? – DanIdle Jul 31 '14 at 08:55
I echoed the result $linemysql gets before the INSERT. Yes it seems it gets the null value which is showed as '' when echoed, yes there are single quotes because I used implode("','",$linearray); And when I check the table, even on phpmyadmin, it still gets 0 – leipzy Jul 31 '14 at 09:37
I found out that explicitly putting NULL on prepared statements works... $tsuika = $setsu->prepare("INSERT INTO ".$tblmei." VALUES (7728,1,26,'testname1',NULL,'testArea',0)"); But since the script is a csv upload script any ideas on how I can explicitly put NULL on the query? – leipzy Aug 02 '14 at 07:50

Php PDO: Why does Inserting NULL yields to 0

2 Answers2

Linked