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:

How do u post a NULL to a Database?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

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

Post 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;
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 »

1. The column can't be marked as NOT NULL
2. Don't specify the column when inserting
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
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

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

Post 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.
User avatar
phdatabase
Forum Commoner
Posts: 83
Joined: Fri May 28, 2010 10:02 am
Location: Fort Myers, FL

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

Post by phdatabase »

Why not just interpret a '' result as '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 »

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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

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

Post 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.
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 »

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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

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

Post by Jonah Bron »

Oh yeah, I forgot about the quotation marks :roll:
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 »

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?
Attachments
This is what it caused.
This is what it caused.
ss.jpg (21.04 KiB) Viewed 1346 times
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 »

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
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 »

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.
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 »

As far as your query, please post the full query so we can relate to it
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 »

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.
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 »

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.
Post Reply