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';
?>
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.
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'";
?>
keynot being wrapped in backticks - dev.mysql.com/doc/refman/5.5/en/reserved-words.htmlupdate table_name set value1 = case when '$value1' <> '' then '$value1' else value1 end where...and add same for value2.mysqli_query()query function.mysqli_query(). Another (and IMO better) option is to use PDO.