Update as NULL Mysql

Question

<form action="hi.php" method="post">
    <input type="text" name="id" />
    <input type="hidden" name="name" value="name" />
</form>

I send this input with no value. I want to update id column as NULL.

$id = $_POST['id'];
$name = $_POST['name'];

$mysqli->query("UPDATE table SET id=$id WHERE name='$name');

However that updates it as empty not NULL. How I can insert NULL in $id?

If I send a value with <input name="id">, it updates it correctly. However if I send it empty, column becomes empty, I want it as NULL.

i just wrote it basically. i know that form must have action and method attributes — Webber Depor
– Webber Depor, Commented Feb 28, 2016 at 19:52
And that there is no WHERE clause is also due to "i just wrote it basically"? — VolkerK
– VolkerK, Commented Feb 28, 2016 at 19:56

VolkerK · Accepted Answer · 2016-02-28 20:30:22Z

When using prepared statements and parameters, a NULL value in one of the parameters will also be treated as NULL by the MySQL server.
HTTP parameters are transported as strings. If no value has been given for an input control, the value for the key/value-pair will be an empty string (!=NULL). But you can still have somethig like if(emptystring) use NULL in your script.

e.g.

<?php
// only for this example; otherwise leave _POST alone....
$_POST['id'] = 1;
$_POST['name'] = '';
$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
if ($mysqli->connect_errno) {
    trigger_error( sprintf('mysqli connect error (%d) %s', $mysqli->connect_errno, $mysqli->connect_error), E_USER_ERROR);
    die;
}
mysqli_report(MYSQLI_REPORT_STRICT|MYSQLI_REPORT_ALL); // that's all the "error handling" for this example....
setup($mysqli);

// even if the user didn't fill in any value, the parameters should be in the request
// as empty strings
if ( !isset($_POST['name'], $_POST['id']) ) {
    echo 'missing POST paramete';
}
else {
    // <-- maybe some plausiblity checks here anyway; e.g. some assumptions about the id you can test, leaving it out as optional for now --->

    // decision point: applying trim() to _POST[name] and _then_ consider it NULL or not - you might disagree about the specifics, just an example.
    $name = trim($_POST['name']);
    if ( 0===strlen($name) ) {
        $name = NULL;
    }

    // <-- actually it would suffice to establish the databse connection here....
    $stmt = $mysqli->prepare('UPDATE soFoo set name=? WHERE id=?');
    $stmt->bind_param('ss', $name, $_POST['id']);
    $stmt->execute();

    printTable($mysqli);
}

function printTable($mysqli) {
    $result = $mysqli->query('SELECT * FROM soFoo ORDER BY id');
    foreach( $result as $row ) {
        var_export($row); echo "\r\n";
    }
}

function setup($mysqli) {
    $mysqli->query('CREATE TEMPORARY TABLE soFoo (id int, name varchar(32), primary key(id))');
    $mysqli->query("INSERT INTO soFoo (id,name) VALUES(1,'oldname1'),(2,'oldname2')");
}

prints

array (
  'id' => '1',
  'name' => NULL,
)
array (
  'id' => '2',
  'name' => 'oldname2',
)

Muhammed · Accepted Answer · 2016-02-28 19:52:46Z

0

I suggest you use PDO, and not mysql() but this will give you the idea:

$id = $_POST['id'];

if($id==''){
   $id='NULL';
}
else{
   $id = mysql_real_escape_string($id);
}

$qr = 'Update table SET id='.$id;
mysql_query($qr);

answered Feb 28, 2016 at 19:52

Muhammed

1,5711 gold badge11 silver badges18 bronze badges

2 Comments

Muhammed Over a year ago

he wrote it in his first line :)) $id = $_POST['id']; that where i took it.. Afterall question isn't about that, its about how to handle NULL, and I gave him the solution.

Muhammed Over a year ago

definetelly isset() pre-check. On entire POST or GET I usually run array_map() with trim, and mysql_real_escape_string, and others if I am not using PDO

Collectives™ on Stack Overflow

Update as NULL Mysql

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related