Page 2 of 3

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

Posted: Tue Jun 08, 2010 7:11 am
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.

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

Posted: Tue Jun 08, 2010 7:18 am
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;
??

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

Posted: Tue Jun 08, 2010 7:31 am
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.

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

Posted: Tue Jun 08, 2010 7:38 am
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;

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

Posted: Tue Jun 08, 2010 7:51 am
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'");


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

Posted: Tue Jun 08, 2010 7:57 am
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.

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

Posted: Tue Jun 08, 2010 8:03 am
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.

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

Posted: Tue Jun 08, 2010 8:05 am
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.

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

Posted: Tue Jun 08, 2010 8:11 am
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?

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

Posted: Tue Jun 08, 2010 11:06 am
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.

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

Posted: Tue Jun 08, 2010 11:21 am
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.

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

Posted: Tue Jun 08, 2010 11:47 am
by Benjamin
'NULL' !== NULL

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

Posted: Tue Jun 08, 2010 11:52 am
by simonmlewis
Thanks, but you have not given any indication of where that should go in the script.

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

Posted: Tue Jun 08, 2010 12:12 pm
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'");

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

Posted: Tue Jun 08, 2010 12:13 pm
by Benjamin
I'll put as much effort into my answers as you put into interpreting them.