Using Nulls in a MySQLi Prepared Statement

using nulls in a mysqli prepared statement

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.

How to bind DEFAULT and NULL in prepared statement?

This is actually an XY Problem - you don't bind DEFAULT or even, in most cases, NULL (although you can).

Your problem is that you're using a shorthand INSERT that doesn't specify the columns, so you have to add data for all of them:

INSERT INTO tblProjects VALUES (?, ? ...);

If you specify which columns to use, you can omit any you'd want to be default (an auto-incremented PRIMARY KEY for example). Also any column you want to be NULL is quite likely to have DEFAULT NULL, in which case you just omit that as well.

INSERT INTO tblProjects (col2, col4 ...) VALUES (?, ? ...)

---

Note: it is possible to just use SQL keywords in the query, like so:

INSERT INTO tblProjects VALUES (DEFAULT, ?, ?, NULL ...)

... but I wouldn't say it was best practice

mysqli prepared statements, insert NULL using bind params

use: $column2 = null; not: $column2 = "null";

Inserting null values to database using prepared statements PHP MYSQL

Your query is wrong:

if ($stmt = $mysqli->prepare("INSERT INTO login VALUES('',?,?,?,?,?,'')")) {

It should be something like:

if (!empty($name) || !empty($pass) || !empty($email))
    {
        $stmt = $mysqli->prepare("INSERT INTO login(`name`,`password`,`email`,`sex`,`city`) VALUES(?,?,?,?,?)");
        $stmt->execute([$name, $pass, $email, $sex, $city]);
        echo "result inserted";

    } else {
        echo 'You have not entered all of the fields.';
    }

In this instance, if the variables are not empty then perform insert. Else if they are empty fire a echo stating the fields haven't been filled in.

If you are happy for the fields to be null simply change !empty() to empty() but as Fred -ii- stated above, ensure your database allows NULL within them fields.

Prepared statement: column cannot be null

You're looking for a POST variable that wasn't sent with the request. This is what the undefined index notice is trying to tell you. You don't check to see if it exists before passing it to the database, so it ends up getting passed as null. Just give a default value of an empty string (here using the null coalesce operator) and it will work fine.

$tipimage = $_REQUEST['tipimage'] ?? "";

mysqli prepared statement - do not update NULL values

You could change your query as follows:

UPDATE members SET
    username = IFNULL(?, username),
    email = IFNULL(?, email) -- and so on for all fields
WHERE...

It could also be more efficient to check the value of your parameters first, and build the query dynamically, including only fields for which you have a non-null value to update with.

Convert MySQL query into prepared statement with possible null value

Why not just

$varC = ($varC == '-') ? null : $varC;

?

Also, you are missing the commas in your query

$query .= "SET VARA = ?, VARB = ?, VARC = ? ";

Edit:
I just ran the code with my changes and it seemed to work okay. For reference, this is the code I used:

<?php
error_reporting(-1);
ini_set('display_errors', 'On');

$dbc = mysqli_connect("127.0.0.1", "test", "test", "test");

$ID = "1";
$varA = "a";
$varB = "b";
$varC = "-";

$varC = ($varC == '-') ? null : $varC;

$query = "UPDATE myTable ";
$query .= "SET VARA = ?, VARB = ?, VARC = ? ";
$query .= "WHERE ID = ?";

$stmt = mysqli_prepare($dbc, $query);
$bind = mysqli_stmt_bind_param($stmt, "ssss", $varA, $varB, $varC, $ID);
$exec = mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Could you try adding the error reporting lines to the top of your code and see if you are getting any errors?

Java Prepared Statement with IS NULL

This is a well known limit of SQL databases. For most databases, you must write ... WHERE field IS NULL to test for NULL values of field. Neither ... field = NULL, nor ... field = :param where :param is a parameter of a parameterized query ever match a NULL value.

So the only solution is to explicitely write field IS NULL is your query. Said differently you need 2 different queries, one for non null values (and only one parameter) and the other one for null values (and 2 parameter).

statement = "SELECT * FROM my_table WHERE name = ? AND nickname = ?";
statement = "SELECT * FROM my_table WHERE name = ? AND nickname IS NULL";

You can use the trick (beware of parentheses as explained by JBNizet)

statement = "SELECT * FROM my_table WHERE name = ? AND (nickname = ? OR nickname IS NULL)";

If you want to still use 2 parameters for the query asking for NULL values.

---

Related Topics