Page 1 of 3
How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 12:00 pm
by simonmlewis
I want to be able to select a field of Radio Buttons that has a value of 'none'.
Then on the page that runs the query, assign 'NULL' to the variable if it contains 'none'. And then pass that through the UPDATE query.
So that a field called 'category' has a NULL VALUE instead of it contain a value of ''.
I've tried this:
$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.
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 12:21 pm
by Jonah Bron
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: Mon Jun 07, 2010 12:23 pm
by AbraCadaver
1. The column can't be marked as NOT NULL
2. Don't specify the column when inserting
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 12:27 pm
by Jonah Bron
Also, errors are important; they're not just there to annoy you. What do the errors state?
I might be easier to just give the form element a value of "NULL" instead of "none", but that would open up some security holes.
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 1:01 pm
by phdatabase
Why not just interpret a '' result as 'NULL' ?
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 2:32 pm
by simonmlewis
The first response here probably does it.
As far as I know, there is no way assign a value of 'NULL' to a radio button, as it will pass through as the word 'NULL' and not the php value of NULL.
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 2:51 pm
by Jonah Bron
I know. That's because he doesn't want the PHP value of Null, he wants the MySQL value of Null. At least, that's that his post sounds like.
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 2:56 pm
by simonmlewis
The point of this query is to post a NULL value to a database. Whether it is done via converting a variable value to NULL or passing a NULL thru to it, either is fine.
But if you post "<input type='radio' name='category' value='null'>" to the query, it will literally enter the WORD 'null' into the database.
The first answer in this thread explains how to convert the variable into a NULL VALUE.
Re: How do u post a NULL to a Database?
Posted: Mon Jun 07, 2010 3:01 pm
by Jonah Bron
Oh yeah, I forgot about the quotation marks

Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 3:00 am
by simonmlewis
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;
Sadly this did not work, as it put the word NULL into the database field, and didn't set it to
NULL
Any other suggestions?
Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 3:12 am
by Eran
Jonah Broh was correct, you just didn't use his suggestion in a query but rather in phpmyadmin. Putting something in phpmyadin text input is the same as using "NULL" since all text is quoted by phpmyadmin, something you'd notice if you have looked at the query it runs after you submit the form. For this reason you have the NULL checkbox next to the text input.
As far as your query, please post the full query so we can relate to it
Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 3:21 am
by simonmlewis
Well it's a basic form field.
Radio buttons that produce the Categories from the database, but one hardcoded that could product the NULL value.
It just posts the values to the same page, and then UPDATEs in the standard style of MySQL query.
but if I post "null" the word null gets put into the category field, it doesn't set the field to a NULL value (with the tick in that phpmyadmin box).
I'm just asking, how do you convert a value of a field to a NULL value for the database.
Or - can you actually post a NULL from a radio button.
Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 4:42 am
by Eran
As far as your query, please post the full query so we can relate to it
Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 4:47 am
by simonmlewis
Are you not familiar with the basic posting of a value, to a UPDATE query?
I don't quite understand why I need to post something, when I am asking HOW to do something.
But anyway, if you don't know how to post a variable to an UPDATE, here's how:
Code: Select all
<?php
$id=$_POST['id'];
$category=$_POST['category'];
$application=$_POST['application'];
if ($update == "totalupdate") {
$query = mysql_query ("UPDATE products SET
category = '$category', application = '$application' WHERE id = '$id'");
And the form is the most basic of <input type='radio' name='category' value='DONT KNOW WHAT SHOULD GO HERE'>.
I am asking HOW do post a NULL to a variable and then to be able to run that in the query.
Re: How do u post a NULL to a Database?
Posted: Tue Jun 08, 2010 7:03 am
by Benjamin
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.