How do u post a NULL to a Database?
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?
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.
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.
All the best from the United Kingdom.
- 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?
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
So, your code should be changed to
Code: Select all
$category = $_POST['category'];
$category = $category == 'none' ? 'NULL' : $category;- 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?
1. The column can't be marked as NOT NULL
2. Don't specify the column when inserting
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.
- 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?
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.
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.
- 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?
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?
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.
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.
All the best from the United Kingdom.
- 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?
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?
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.
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.
All the best from the United Kingdom.
- 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?
Oh yeah, I forgot about the quotation marks 
-
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?
Sadly this did not work, as it put the word NULL into the database field, and didn't set it to NULLJonah 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 toCode: Select all
$category = $_POST['category']; $category = $category == 'none' ? 'NULL' : $category;
Any other suggestions?
- Attachments
-
- This is what it caused.
- ss.jpg (21.04 KiB) Viewed 1345 times
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do u post a NULL to a Database?
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
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?
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.
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.
All the best from the United Kingdom.
Re: How do u post a NULL to a Database?
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?
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:
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.
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'");
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.
All the best from the United Kingdom.
Re: How do u post a NULL to a Database?
The field must be set to allow NULL values in the table definition:
The query must be written to update/insert the field as a NULL value, which means NULL must not have quotes around it:
In order to specifically pull records will NULL values, or values that are NOT NULL, you must specify this in the query:
That's all there is to it.
Code: Select all
ALTER TABLE `table_name` CHANGE `field` `field` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULLCode: Select all
UPDATE `table_name` SET `field` = NULL WHERE `foo` = `bar`
Code: Select all
SELECT `field` FROM `table_name` WHERE `field` IS NULL;
SELECT `field` FROM `table_name` WHERE `field` IS NOT NULL;