Bill Guion wrote:
I'm trying to write a MySQL UPDATE query where one or more variables may
be NULL. So, I'm trying something like:
$last_name = $_POST['last_name'];
$first_name = $_POST['first_name'];
$suffix = $_POST['suffix'];
$suffix = empty($suffix) ? NULL : $suffix;
$phone = $_POST['phone'];
$phone_index = $_POST['phone_index'];
$update_query = "UPDATE 'phones'
SET 'last_name' = $last_name, 'first_name' =
$first_name,
'suffix' = $suffix, 'phone' = $phone
WHERE 'phone_index' = $phone_index;";
However, when I echo out this query, I get:
UPDATE 'phones' SET 'last_name' = Doe, 'first_name' = John, 'suffix' = ,
'phone' = 123-456-7890 WHERE 'phone_index' = 323;
I think, for this to properly update the record, it should be:
UPDATE 'phones' SET 'last_name' = Doe, 'first_name' = John, 'suffix' =
NULL, 'phone' = 123-456-7890 WHERE 'phone_index' = 323;
1) You don't need quotes around your field names.
2) You do need quotes around your data, plus you should use
mysql_real_escape_string to stop sql injection attacks:
$phone = mysql_real_escape_string($_POST['phone']);
3) What error do you get once you fix both of those up?
Your query should end up looking like:
$query = "UPDATE phones SET last_name='" .
mysql_real_escape_string($_POST['last_name']) . "', ...
or
$last_name = mysql_real_escape_string($_POST['last_name']);
$first_name = mysql_real_escape_string($_POST['first_name']);
// set a default of NULL
$suffix = "NULL";
if (!empty($_POST['suffix'])) {
// note - you need to add the quotes around the data here
$suffix = "'" . mysql_real_escape_string($_POST['suffix']) . "'";
}
$query = "UPDATE phones set last_name='${last_name}',
first_name='${first_name}' ..., suffix=${suffix}";
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
