Inserting Null Values into MYSQL through PHP

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

Post Reply
Stacks
Forum Newbie
Posts: 24
Joined: Thu Jun 05, 2008 7:52 pm

Inserting Null Values into MYSQL through PHP

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Inserting Null Values into MYSQL through PHP

Post 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.
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.
Stacks
Forum Newbie
Posts: 24
Joined: Thu Jun 05, 2008 7:52 pm

Re: Inserting Null Values into MYSQL through PHP

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Inserting Null Values into MYSQL through PHP

Post by Benjamin »

Post the code of $business->addBusiness() or post the actual query being sent to MySQL.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Inserting Null Values into MYSQL through PHP

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