Page 1 of 1

An INSERT question

Posted: Mon Mar 10, 2008 10:51 pm
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!

Re: An INSERT question

Posted: Mon Mar 10, 2008 11:07 pm
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.

Re: An INSERT question

Posted: Mon Mar 10, 2008 11:16 pm
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!

Re: An INSERT question

Posted: Tue Mar 11, 2008 12:15 am
by Christopher
Quotes would solve the problem. MySQL will sort out that they are numbers of NULL.

Re: An INSERT question

Posted: Tue Mar 11, 2008 7:58 am
by seodevhead
Is 0 another way to signify NULL in mysql?