I'm using this code and I'm beyond frustration:
try {
$dbh = new PDO('mysql:dbname=' . DB . ';host=' . HOST, USER, PASS);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}
catch(PDOException $e)
{
...
}
$stmt = $dbh->prepare('INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)');
$stmt->bindParam(':v1', PDO::PARAM_NULL); // --> Here's the problem
PDO::PARAM_NULL, null, '',
all of them fail and throw this error:
Fatal error: Cannot pass parameter 2 by reference in /opt/...
This question is related to
php
mysql
pdo
null
sql-insert
If you want to insert NULL
only when the value
is empty
or ''
, but insert the value
when it is available.
A) Receives the form data using POST method, and calls function insert with those values.
insert( $_POST['productId'], // Will be set to NULL if empty
$_POST['productName'] ); // Will be to NULL if empty
B) Evaluates if a field was not filled up by the user, and inserts NULL
if that's the case.
public function insert( $productId, $productName )
{
$sql = "INSERT INTO products ( productId, productName )
VALUES ( :productId, :productName )";
//IMPORTANT: Repace $db with your PDO instance
$query = $db->prepare($sql);
//Works with INT, FLOAT, ETC.
$query->bindValue(':productId', !empty($productId) ? $productId : NULL, PDO::PARAM_INT);
//Works with strings.
$query->bindValue(':productName',!empty($productName) ? $productName : NULL, PDO::PARAM_STR);
$query->execute();
}
For instance, if the user doesn't input anything on the productName
field of the form, then $productName
will be SET
but EMPTY
. So, you need check if it is empty()
, and if it is, then insert NULL
.
Tested on PHP 5.5.17
Good luck,
I had the same problem and I found this solution working with bindParam :
bindParam(':param', $myvar = NULL, PDO::PARAM_INT);
For those who still have problems (Cannot pass parameter 2 by reference), define a variable with null value, not just pass null to PDO:
bindValue(':param', $n = null, PDO::PARAM_INT);
Hope this helps.
In my case I am using:
SQLite,
prepared statements with placeholders to handle unknown number of fields,
AJAX request sent by user where everything is a string and there is no such thing like NULL
value and
I desperately need to insert NULL
s as that does not violates foreign key constrains (acceptable value).
Suppose, now user sends with post: $_POST[field1]
with value value1
which can be the empty string ""
or "null"
or "NULL"
.
First I make the statement:
$stmt = $this->dbh->prepare("INSERT INTO $table ({$sColumns}) VALUES ({$sValues})");
where {$sColumns}
is sth like field1, field2, ...
and {$sValues}
are my placeholders ?, ?, ...
.
Then, I collect my $_POST
data related with the column names in an array $values
and replace with NULL
s:
for($i = 0; $i < \count($values); $i++)
if((\strtolower($values[$i]) == 'null') || ($values[$i] == ''))
$values[$i] = null;
Now, I can execute:
$stmt->execute($values);
and among other bypass foreign key constrains.
If on the other hand, an empty string does makes more sense then you have to check if that field is part of a foreign key or not (more complicated).
Based on the other answers but with a little more clarity on how to actually use this solution.
If for example you have an empty string for a time value but you want to save it as a null:
if($endtime == ""){
$db->bind(":endtime",$endtime=NULL,PDO::PARAM_STR);
}else{
$db->bind("endtime",$endtime);
}
Notice that for time values you would use PARAM_STR, as times are stored as strings.
Try This.
$stmt->bindValue(':v1', null, PDO::PARAM_NULL); // --> insert null
When using INTEGER
columns (that can be NULL
) in MySQL, PDO has some (to me) unexpected behaviour.
If you use $stmt->execute(Array)
, you have to specify the literal NULL
and cannot give NULL
by variable reference.
So this won't work:
// $val is sometimes null, but sometimes an integer
$stmt->execute(array(
':param' => $val
));
// will cause the error 'incorrect integer value' when $val == null
But this will work:
// $val again is sometimes null, but sometimes an integer
$stmt->execute(array(
':param' => isset($val) ? $val : null
));
// no errors, inserts NULL when $val == null, inserts the integer otherwise
Tried this on MySQL 5.5.15 with PHP 5.4.1
When using bindParam()
you must pass in a variable, not a constant. So before that line you need to create a variable and set it to null
$myNull = null;
$stmt->bindParam(':v1', $myNull, PDO::PARAM_NULL);
You would get the same error message if you tried:
$stmt->bindParam(':v1', 5, PDO::PARAM_NULL);
Source: Stackoverflow.com