Queries with NULL values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Queries with NULL values

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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')
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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);
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yup, that's another solution, thanks
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Oh, I see we are still on the one liners :wink:
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

Yes, it seems... lol... I didn't notice!
Post Reply