CODEWITHSUNDEEP
phpmysql
Tim
Tim

Reputation: 13

PHP Form update MySql only if not null

Currently I have an update function that will update a row but if I leave one of the boxes empty, instead of not changing that value the value is deleted. If I only need to update one of the values, I would like to update this value and leave the other boxes blank. Is this possible?

Currently my code is this. 

<?php
$key = $_POST['key'];
$value1 = $_POST['value1'];
$value2 = $_POST['value2'];
UPDATE table SET value1 = '$value1',value2 = '$value2' WHERE key = '$key';
?>

Upvotes: 0

Views: 1234

Answers (2)

Travesty3
Travesty3

Reputation: 14479

You will have to check each value to see if it's blank, and if so, modify your query string to remove that field from the SET clause.

Something like this:

<?php
$set = array();

// make sure the user doesn't attempt to POST a column that doesn't exist in our table,
// which will lead to a SQL error, or worse, allow the user to run custom SQL.
$columns = array('value1', 'value2'); 

foreach ($_POST as $key=>$value) {
    if (in_array($key, $columns) && $value != '') {
        $set[] = $key ." = '". mysql_real_escape_string($value) ."'";
    }
}

if (!empty($set)) {
    $query = "UPDATE table SET ". implode(', ', $set) ." WHERE `key` = '". mysql_real_escape_string($_POST['key']) ."'";
}

Notice also I've used mysql_real_escape_string(). This is to prevent SQL injection. I don't know what MySQL library you're using, but you should use the appropriate sanitization method for whatever you're using. And in actuality, you shouldn't be using the regular mysql_* library, as it's deprecated. Instead, I would recommend PDO.

Upvotes: 1

Charles
Charles

Reputation: 437

Please use the PDO class for the final version.

<?php
$key = $_POST['key'];
$value1 = $_POST['value1'];
$value2 = $_POST['value2'];
if ($value1 == "")
$query = "UPDATE table SET value2 = '$value2' WHERE key = '$key'";
if ($value2 == "")
$query = "UPDATE table SET value1 = '$value1' WHERE key = '$key'";
else
$query = "UPDATE table SET value1 = '$value1',value2 = '$value2' WHERE key = '$key'";
?>

Upvotes: 0

Related Questions