How do u post a NULL to a Database?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

Benjamin wrote:The field must be set to allow NULL values in the table definition:

Code: Select all

ALTER TABLE `table_name` CHANGE `field` `field` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL
The query must be written to update/insert the field as a NULL value, which means NULL must not have quotes around it:

Code: Select all

UPDATE `table_name` SET `field` = NULL WHERE  `foo` = `bar`
In order to specifically pull records will NULL values, or values that are NOT NULL, you must specify this in the query:

Code: Select all

SELECT `field` FROM `table_name` WHERE `field` IS NULL;
SELECT `field` FROM `table_name` WHERE `field` IS NOT NULL;
That's all there is to it.
You've not really answered the question.
If you saw the screenshot, you can see the field IS set to allow a Null value.
I can only do a UPDATE query for null if that is what is passed through from the form, of by converting a value to a NULL - which is what I am asking about here.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How do u post a NULL to a Database?

Post by Benjamin »

simonmlewis wrote:$category = $_POST['category'];
if ($category == "none") { $category = NULL;}

But it fails. Coz all it does is error, or just leave the field empty, but not NULL VALUE.
Jonah Bron wrote:null and "null" are two different things. Your SQL needs to look something like "UPDATE ... SET somecolumn = NULL".

So, your code should be changed to

Code: Select all

$category = $_POST['category'];
$category = $category == 'none' ? 'NULL' : $category;
??
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

Yep - I did try that and it just posted the word NULL to the field. Didn't set the field back to NULL.

Am I missing something here - do you have to two queries? One that sets the field TO NULL, and one that inserts the value?

ie. if the value is not 'none', then do this, but if it is 'none' then do that.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How do u post a NULL to a Database?

Post by Benjamin »

It cannot have quotes around it in the query.

Here's a snippet that will write an insert query:

Code: Select all

function write_insert_query($data, $table) {
    $insert_data = array();

    foreach ($data as $value) {
        if (is_null($value)) {
            $insert_data[] = 'NULL';
        } else {
            $insert_data[] = "'" . mysql_real_escape_string($value) . "'";
        }
    }

    return "INSERT INTO $table (`" . implode("`, `", array_map('mysql_real_escape_string', array_keys($data))) . "`) VALUES (" . implode(", ", $insert_data) . ")";
}

$data = array(
  'field' => NULL,
  'another_field' => 'foo'
);

mysql_query(write_insert_query($data, 'table_name'));
The example that Jonah Bron wrote should have been as follows, without single quotes around NULL.

Code: Select all

$category  = $_POST['category'];
$category = $category == 'none' ? NULL : $category;
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

I prefer simpler methods without functions.

But using that PHP method, all it does is empty the field, doesn't set the field to NULL.

Code: Select all

$category=$_POST['category'];
$category = $category ==  'none' ? NULL : $category;
$application=$_POST['application'];
$application = $application ==  'none' ? NULL : $application;

$query  = mysql_query ("UPDATE products SET 
category = '$category', 
application = '$application', 
title = '$title', 
description = '$description', 
video = '$video'
WHERE id = '$id'");

Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do u post a NULL to a Database?

Post by Eran »

Code: Select all

$query  = "UPDATE products SET
category = " . ($category == 'none' ? 'NULL' : ("'" . $category . "'") ) . ",
application = '$application',
title = '$title',
description = '$description',
video = '$video'
WHERE id = '$id'";
$result = mysql_query($query);
And by the way, this is the query that I asked you to submit earlier.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

Ohhh so close. It is converting the field to a NULL value, but it is converting both 'application' and 'category' to NULL whatever is passed through. ie, if "boat" is passed through, it still sets it to null.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do u post a NULL to a Database?

Post by Eran »

I gave an example for the category field, you should be able to figure the rest out yourself. Having said that..

Code: Select all

$category=$_POST['category'] == 'none' ? 'NULL' : ("'" . $_POST['category'] . "'");
$application=$_POST['application'] == 'none' ? 'NULL' : ("'" . $_POST['application'] . "'");


$query  = mysql_query ("UPDATE products SET
category = $category,
application = $application,
title = '$title',
description = '$description',
video = '$video'
WHERE id = '$id'");
Notice the quotes are in the variables.
Last edited by Eran on Tue Jun 08, 2010 11:03 am, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

I'm sorry, are you trying to confuse me?
You use one method that half works, a method I cannot quite work out.
They you say it is a teaser, and give me an alternative method.

It works - but only sets all to NULL - why is that?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do u post a NULL to a Database?

Post by Eran »

It's the same exact method -

Code: Select all

$category = $_POST['category'] == 'none' //If the value of category is equal to 'none'
    ? 'NULL'  //Then the value is NULL
    : ("'"  . $_POST['category'] .  "'"); //Otherwise quote the string value
The important thing is, as Jonah said previously, that the NULL value should not be quoted while string value should be quoted.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

Code: Select all

category=$_POST['category'] == 'none' ? 'NULL'  : ("'"  . $_POST['category'] .  "'");
$application=$_POST['application'] == 'none' ? 'NULL' : ("'" . $_POST['application'] . "'");
$query  = mysql_query ("UPDATE products SET 
category = '$category',
application = '$application'........
This when I say "none" to categories and "boat" to applications, puts boat into the applications field and just leaves the category field empty, but does not set it to NULL.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How do u post a NULL to a Database?

Post by Benjamin »

'NULL' !== NULL
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u post a NULL to a Database?

Post by simonmlewis »

Thanks, but you have not given any indication of where that should go in the script.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: How do u post a NULL to a Database?

Post by AbraCadaver »

simonmlewis wrote:Thanks, but you have not given any indication of where that should go in the script.
You really need to rethink your process. You need quotes around the column value in the query if its a string, especially if it has spaces, but you can't have quotes around it if its NULL. So you need to determine whether the value should be NULL and insert it without quotes or if it is a string (category name) and quote it.

This might help:

Code: Select all

$category = $category ==   'none' ? 'NULL' : "'$category'";
$query  = mysql_query  ("UPDATE products SET
category = $category,
application = '$application',
title = '$title',
description = '$description',
video = '$video'
WHERE id = '$id'");
Last edited by AbraCadaver on Tue Jun 08, 2010 12:15 pm, edited 1 time in total.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How do u post a NULL to a Database?

Post by Benjamin »

I'll put as much effort into my answers as you put into interpreting them.
Post Reply