Page 1 of 1
Queries with NULL values
Posted: Fri Jul 02, 2004 5:44 am
by JayBird
Just wondering how you guys overcome this problem.
Say i have a form for a user to fill in with some details, for example:
Name:___________
Age:___________
Location:___________
The user DOES NOT have to fill all the fields in.
A normal query for this would look something like:
Code: Select all
$sql = INSERT INTO user_info(name, age, location) VALUES('$name', '$age', '$location');
Now that works fine, but if a user doesn't enter info into one of the fields, i want that field to to have the value NULL, not and empty string.
So if the user left out their age, the query should look something like:
Code: Select all
$sql = INSERT INTO user_info(name, age, location) VALUES('$name', NULL, '$location');
What ways are their to achieve this?
Thanks
Mark
Posted: Fri Jul 02, 2004 6:39 am
by redmonkey
I use something like this....
Code: Select all
<?php
function str_sql_safe($value)
{
if (empty($value))
{
return "''";
}
if (substr($value, 0, 1) == "'" && substr($value, -1) == "'")
{
return $value;
}
return '''' . addslashes($value) . '''';
}
$name = 'auser';
$age = '';
$location = 'somewhere';
if (isset($name) && !empty($name))
{
// some processing (min max length etc)
$name = str_sql_safe($name);
}
else
{
$name = 'NULL'; // or other default value
}
if (isset($age) && !empty($age))
{
$age = int_sql_safe($age);
}
else
{
$age = 'NULL'; // or other default value
}
if (isset($location) && !empty($location))
{
// some processing
$location = str_sql_safe($location);
}
else
{
$location = 'NULL'; // or other default value
}
$sql = "INSERT INTO user_info(name, age, location) VALUES($name, $age, $location)";
echo $sql;
?>
Which would output....
Code: Select all
INSERT INTO user_info(name, age, location) VALUES('auser', NULL, 'somewhere')
Posted: Fri Jul 02, 2004 6:41 am
by JayBird
ah, thats looks nice, i had something similar, but i thought there MUST BE and easier way, obviously not.
The way i as doing it was like this
Code: Select all
foreach($_POST as $key=>$value) {
if ($value == "") {
$new_values[$key] = "NULL"; // Set value to NULL if nothing entered
} else {
$new_values[$key] = "'$value'";
}
}
Cheerz
Mark
Posted: Fri Jul 02, 2004 7:15 am
by redmonkey
In many cases you need to do some further processing on users input (e.g. min/max length, check for ceratain characters, does it match a certain format) in which case a simple foreach statement is is useless.
You could group similar types together in an array and execute foreach staements on each array e.g......
Code: Select all
$a_string = 'this string could be null';
$b_string = 'this string could be null';
$e_string = 'this string requires default value';
$f_string = 'this string requires the same default value';
$a_int = 10;
$b_int = 11;
$null_strings = array('a_string' => $a_string, 'b_string' => $b_string);
$def_strings = array('e_string' => $e_string, 'f_string' => $f_string);
$null_int = array('a_int' => $a_int, 'b_int' => $b_int);
foreach($null_strings as $key => $value)
{
if (!empty($value))
{
$null_strings[$key] = str_sql_safe($value);
}
else
{
$null_strings[$key] = 'NULL';
}
}
foreach($def_strings as $key => $value)
{
if (!empty($value))
{
$def_strings[$key] = str_sql_safe($value);
}
else
{
$value = 'some default';
$def_strings[$key] = str_sql_safe($value);
}
}
foreach($null_int as $key => $value)
{
if (!empty($value))
{
$null_int[$key] = int_sql_safe($value);
}
else
{
$null_strings[$key] = 'NULL';
}
}
extract($null_strings);
extract($def_strings);
extract($null_int);
Posted: Fri Jul 02, 2004 12:01 pm
by scorphus
How about:
Code: Select all
$sql = 'INSERT INTO user_info(name, age, location)
VALUES(' .
(empty($name) ? "NULL" : "'$name'") . ', ' .
(empty($age) ? "NULL" : "'$age'") . ', ' .
(empty($location) ? "NULL" : "'$location'") .
')';
-- Scorphus
Posted: Fri Jul 02, 2004 12:29 pm
by JayBird
Yup, that's another solution, thanks
Posted: Fri Jul 02, 2004 12:41 pm
by redmonkey
Oh, I see we are still on the one liners

Posted: Fri Jul 02, 2004 12:44 pm
by scorphus
Yes, it seems... lol... I didn't notice!