An INSERT question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

An INSERT question

Post by seodevhead »

Hey guys... I'm trying to figure out a problem I am having with the correct syntax of an INSERT statement I am trying to construct.

I have a mysql table that holds a name and an optional numeric code.. with the numeric code (ie. 302) being optional. So in my PHP, I have this:

Code: Select all

if (isset($_POST['numeric_code']))
    $clean['numeric_code'] = $_POST['numeric_code'];
else
    $clean['numeric_code'] = NULL;
So with this.. as you can see.. $clean['numeric_code'] can either have a value (ie. '302'), or no value at all (NULL).

So here in lies the problem I am having.

Code: Select all

CREATE TABLE names_and_codes (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   persons_name VARCHAR(100) NOT NULL,
   numeric_code TINYINT UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id)
);
Since that is the MySQL table I am trying to insert too... I am getting an error with the following INSERT query I am using if $clean['numeric_code'] is NULL. ie. $clean['numeric_code'] = NULL;

Code: Select all

$query = "INSERT INTO names_and_codes (persons_name, numeric_code) VALUES ('{$clean['persons_name']}', {$clean['numeric_code']})";
Anyone know a way I can work around this? I need to keep the INSERT query above in tact.. in other words, I can't take the numeric code entry out of the INSERT statement if it is indeed null.

Any help would GREATLY be appreciated. Thanks!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: An INSERT question

Post by Christopher »

If your numeric code is NULL then your SQL would be VALUES ('foo', ). As you can see that is an error. I would recommend using a default of 0.
(#10850)
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Re: An INSERT question

Post by seodevhead »

Hey arborint... yea.. that is exactly what is happening. It's just coming up blank.

Well see.. someone's numeric code may actually be zero. So I'm afraid of defaulting to 0.

Would it be okay to put quotes around the NULL in my php?

ie. $clean['numeric_code'] = "NULL";

So that way when it's read in the INSERT query it will be properly used as a NULL value? I guess I'd just have to keep track of when to quote the word NULL depending on whether a variable should contain a numeric value or a string value... right?

Thanks for your help!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: An INSERT question

Post by Christopher »

Quotes would solve the problem. MySQL will sort out that they are numbers of NULL.
(#10850)
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Re: An INSERT question

Post by seodevhead »

Is 0 another way to signify NULL in mysql?
Post Reply