A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearusing nulls in a mysqli prepared statement - page 1
User InfoPosts
using nulls in a mysqli prepared statement#1
In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL. Is there any way of doing this?

posted date: 2008-12-16 07:33:00


Re: using nulls in a mysqli prepared statement#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-16 07:33:01


Re: using nulls in a mysqli prepared statement#3
The comments to the PHP documentation on mysqli_stmt::bind_param indicate that passing in NULL was not easily possible.Please see @creatio's answer: http://www.momige.com/a/6892491/18771Solutions offered in the comments do some pre-preparation work on the prepared statement, replacing the "?" markers with "NULL" for every param that has the PHP null value. The modified query string is then used.The following function is from user comment 80119:function preparse_prepared($sQuery, &$saParams){ $nPos = 0; $sRetval = $sQuery; foreach ($saParams as $x_Key => $Param) { //if we find no more ?'s we're done then if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false) { break; } //this test must be done second, because we need to //increment offsets of $nPos for each ?. //we have no need to parse anything that isn't NULL. if (!is_null($Param)) { continue; } //null value, replace this ? with NULL. $sRetval = substr_replace($sRetval, 'NULL', $nPos, 1); //unset this element now unset($saParams[$x_Key]); } return $sRetval;} (It's not really the coding style I would have done it in, but if it works...)

posted date: 2008-12-16 08:12:00


Re: using nulls in a mysqli prepared statement#4
FWIW, this breaks on "WHERE column_name != ?" because comparisons to NULL are always NULL. This also breaks on "WHERE question = 'et tu, brute?'"

posted date: 2008-12-16 09:55:00


Re: using nulls in a mysqli prepared statement#5
by my side, i store every parameters in an array and pass them in Bind_param function by array_shift($myArray). NULL is accepted like that..S.

posted date: 2009-08-05 10:57:00


Re: using nulls in a mysqli prepared statement#6
I know this is an old thread, but it's possible to bind a true NULL value to the prepared statements (read this).You can, in fact, use mysqli_bind_parameter to pass a NULL value to the database. simply create a variable and store the NULL value (see the manpage for it) to the variable and bind that. Works great for me anyway.Thus it'll have to be something like:<?php $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world'); // person is some object you have defined earlier $name = $person->name(); $age = $person->age(); $nickname = ($person->nickname() != '') ? $person->nickname() : NULL; // prepare the statement $stmt = $mysqli->prepare("INSERT INTO Name, Age, Nickname VALUES (?, ?, ?)"); $stmt->bind_param('sis', $name, $age, $nickname);?>This should insert a NULL value into the database.

posted date: 2011-07-31 13:39:00


Re: using nulls in a mysqli prepared statement#7
For anyone coming looking at this because they are having problems binding NULL in their WHERE statement, the solution is this:There is a mysql NULL safe operator that must be used:<=>Example:<?php$price = NULL; // NOTE: no quotes - using php NULL$stmt = $mysqli->prepare("SELECT id FROM product WHERE price <=> ?"); // Will select products where the price is null$stmt->bind_param($price);?>

posted date: 2012-04-26 12:15:00


Re: using nulls in a mysqli prepared statement#8
Nice, exactly what I needed to know, just made it a bit more generic: foreach($param as $k => $v) { if($v == '') $param[$k] = NULL; }, so basically any null entry is converted to a true NULL, carefull with nested arrays though, just make it recursive if ur using those^^ This should be the selected answer though, thks

posted date: 2014-05-08 07:03:00


Re: using nulls in a mysqli prepared statement#9
<?php$mysqli=new mysqli('localhost','root','','test');$mysqli->query("CREATE TABLE test_NULL (id int(11))");if($query=$mysqli->prepare("insert into test_NULL VALUES(?)")){ $query->bind_param('i',$null); //note that $null is undefined $query->execute();}else{ echo __LINE__.' '.$mysqli->error;}?>

posted date: 2014-12-31 13:27:00


Re: using nulls in a mysqli prepared statement#10
I can't believe how long I've struggled with this, and the answer was almost obvious. Strictly: you can't. Structurally: bind_param. By code: $query_param_01 = (null===$param_01) ? "?" : "NULL"; +1 to everyone. (I'll stick with {$query_param_01} as there's still no injection issues. Feel free to correct.

posted date: 2016-02-07 22:08:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.