PHP mysql PDO refuses to set NULL value

Question 1

I am unable to set a nullable field with a default value of null to null using mysql pdo. I can do it using straight sql.

I have tried: (mostly from this question How do I insert NULL values using PDO?)

Null Int

bindValue(':param', null, PDO::PARAM_INT);

Null Null

bindValue(':param', null, PDO::PARAM_NULL);

'Null', Int

bindValue(':param', 'NULL', PDO::PARAM_INT);

'Null', Null

bindValue(':param', 'NULL', PDO::PARAM_NULL);

Null
```
bindValue(':param', null);
```
'Null'
```
bindValue(':param', 'NULL');
```
and the bindParam counterparts of 5 and 6 with a variable that held the value of the binding.

Everything from PDO results in the value being set to 0.

PHP Version: PHP 5.3.2-1ubuntu4.10

MYSQL SERVER VERSION: 5.1.63-0ubuntu0.10.04.1

EDIT Screenshot of column info

Question 2

The following works for me:

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);

$stmt->execute();

Pass in PHP's null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Question 3

Your first question:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.

Also worth noting (emphasis mine):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

This can cause racing conditions.

Your second question:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

From the documentation it says:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.

However note the following from the same source as well:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

So be sure to check the compatibility beforehand!

A few notes

Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.

Another note from the documentation:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Question 4

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }

Question 5

See my notes inline with the code:

    try{  
         $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));

         // this never executes because an Exception halts it here
         /*if($req == 0){
               $prepared_insertQry_toDuplicateRefTable->execute(array(something));
         }*/
     }
    catch(PDOException $e){
       // this catch grabs the exception and executes the code within instead
       // that code might log an error, echo the error message, or perform
       // alternative logic. In your case you want to execute alterntative logic
       // ie. your query
       $prepared_insertQry_toDuplicateRefTable->execute(array(something));

   }

Question 6

The PDO::exec() function returns the number of affected rows, including 0 if no rows are affected.

A line like this will die() because exec will return 0 which is interpreted as boolean false.

$dblink->exec("UPDATE `sometable` SET `somecolumn`=0 WHERE `somecolumn`=0") or die("Never use die for error handling.");

The best error handling practice for PDO is to use PDO exceptions. Enable PDO exceptions (of PDOException class, see docs) like this:

//enable Exception mode (uncaught exceptions work just like die() with the benefit of giving you details in logs of where execution was stopped and for what reason)
$pdoDBHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Remove or die() and exit(); and enable exception mode. I bet this will fix your "weird" problem. Also take a look at throwing Exceptions in PHP, even with procedural code (to replace die() and exit().

BTW exit stops execution just like die, except it is usefull in CLI mode because it returns a success/error code to the operating system. It really isn't meant for error handling.

PHP mysql PDO refuses to set NULL value

Answers

Your first question:

Your second question:

A few notes

php,mysql,pdo