Page 1 of 1

Inserting Null Values into MYSQL through PHP

Posted: Tue Feb 23, 2010 2:21 pm
by Stacks
Hello,

I'm having an issue. I'm inserting into a business table. I'm inserting address, name, slogan. If no slogan is provided I would like to insert NULL into the database rather than an empty string.

I figured doing this

Code: Select all

        if ($_POST['slogan'] == "")
            $_POST['slogan'] = NULL;
 
could set the post value to NULL before doing this.

Code: Select all

$business->addBusiness($_POST['businessName'], $_POST['phone'], $_POST['address'], $_POST['city'], $loginId, $_POST['businessType'], $_POST['slogan'], $_POST['URL'], $_POST['businessDescription']);
Can you guys point out if I'm doing this completely wrong, or if there is another method to do this.

Re: Inserting Null Values into MYSQL through PHP

Posted: Tue Feb 23, 2010 2:34 pm
by AbraCadaver
If you're using MySQL and the the data is empty "" and the column is not set to NOT NULL then I believe it will be NULL. PHP null and MySQL null are different.

Re: Inserting Null Values into MYSQL through PHP

Posted: Tue Feb 23, 2010 3:43 pm
by Stacks
AbraCadaver wrote:If you're using MySQL and the the data is empty "" and the column is not set to NOT NULL then I believe it will be NULL. PHP null and MySQL null are different.
I figured PHP Null would be different then MYSQL Null. However what you say above is not true. My columns are not set to not null. Allowing Null Values. With Default set to Null. When I insert an empty string the database stores an empty string.

If My insert statement did not include those columns I'm sure then it would set them to Null. However I want to keep my code clean and don't want a if to determine the correct insert statement to use. I want to use my one insert statement. There must be a way to set the PHP variable to a MYSQL NULL value.

Does someone know how I could set the php variables to a MYSQL Null value?

Re: Inserting Null Values into MYSQL through PHP

Posted: Tue Feb 23, 2010 4:30 pm
by Benjamin
Post the code of $business->addBusiness() or post the actual query being sent to MySQL.

Re: Inserting Null Values into MYSQL through PHP

Posted: Tue Feb 23, 2010 5:09 pm
by AbraCadaver
To clarify:

These will both insert NULL into slogan. The second one because slogan is omitted:

Code: Select all

INSERT INTO table_name (slogan) VALUES (NULL)
 
INSERT INTO table_name (business_name) VALUES ('Some business')
So will this because you are not denoting that $slogan is a string thus ending up with '':

Code: Select all

$slogan = "";
some_query("INSERT INTO table_name (slogan) VALUES ($slogan)");
This will insert an empty string:

Code: Select all

INSERT INTO table_name (slogan) VALUES ('')
So will this, notice the quotes in the query indicating an empty string:

Code: Select all

$slogan = "";
some_query("INSERT INTO table_name (slogan) VALUES ('$slogan')");
So as astions is probably thinking, your addBusiness() method is probably quoting all values, when it should either check for empty ones or have another mechanism for skipping them.