Assigning NULL values to variable and executing SQL update query

[samved]

I am facing problem to execute query by assigning NULL value to a variable and then executing query.In MySQL DB four fields Mobile,landline, pincode,dob are set as integer and date(for dob) respectively.The default is set as  NULL and NULL option is selected as yes.All these fields are not mandatory.The problem is that when I edit the form my keeping the value as empty in DB these are saved as 0, 0 , 0 & 0000-00-00 inspite of Null. I have tried everything but still the defect persist. Please help me to come out of the problem  :'(

 

The code, I have used:

 

<?php

//require_once 'includes/config.php';

 

$dbusername = $_POST['email'];

        $dbfirstname = $_POST['first_name'];

        $dblastname = $_POST['last_name'];

        //$dbmobile_number = $_POST['mobile'];

if (isset($_POST['mobile']))

 

{

$dbmobile_number = $_POST['mobile'];

 

}

       

else

 

{

$dbmobile_number = "NULL";

 

}

 

$dblandline_number = $_POST['landline'];

        $dbdob = $_POST['dob'];

 

if(isset($_POST['is_email']))

{

$dbSubscribe_Email_Alert = '1';

}

else

{

$dbSubscribe_Email_Alert = '0';

}

 

if(isset($_POST['is_sms']))

{

$dbSubscribe_SMS = 0;

}

else

{

$dbSubscribe_SMS = 0;

}

       

 

        $dbAddress_firstname = $_POST['shipping_first_name'];

        $dbAddress_lastname = $_POST['shipping_last_name'];

        $dbAddress = $_POST['shipping_address'];

        $dbcity = $_POST['shipping_city'];

        $dbpincode = $_POST['shipping_pincode'];

        $dbstate = $_POST['shipping_state'];

        $dbcountry = $_POST['shipping_country'];

 

echo "Welcome".$dbusername;

 

//if($_POST['btnSave'])

 

//if ($_POST['btnSave'])

 

//{

 

//echo "Inside query loop";

$connect = mysql_connect("localhost","root","") or die("Couldn't connect!");

mysql_select_db("salebees") or die ("Couldn't find DB");

 

//$query = mysql_query("SELECT * FROM users WHERE username='$username'");

 

$query = mysql_query("update users set firstname = '$dbfirstname', lastname = '$dblastname', mobile_number = '$dbmobile_number', landline_number = '$dblandline_number', dob = '$dbdob', Subscribe_Email_Alert = '$dbSubscribe_Email_Alert', Subscribe_SMS = '$dbSubscribe_SMS',  Address_firstname = '$dbAddress_firstname', Address_lastname = '$dbAddress_lastname', Address = '$dbAddress', city = '$dbcity', pincode = '$dbpincode', state = '$dbstate', country = '$dbcountry' where username = '$dbusername' ");

 

header("location:my_account.php");

 

 

//}

 

//else

 

//{

//die();

 

//}

 

 

?>

 

 

 

[phppaper]

Have you try to disable the default value in phpmyadmin and see?

[JasonLewis]

I was under the impression that the default for these column types was not allowed to be null.

 

However I may be wrong.

[samved]

Have you try to disable the default value in phpmyadmin and see?

e

 

Yup I tried but it still not takes NULL value

[phppaper]

FYI:

http://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql

[samved]

I was under the impression that the default for these column types was not allowed to be null.

 

However I may be wrong.

 

 

In database I have set NULL as yes, so it can accept NULL values.Also default I have set as NULL.

[phppaper]

http://php.net/manual/en/language.types.null.php

 

I think you have pass an empty string instead of NULL.

[samved]

http://php.net/manual/en/language.types.null.php

 

I think you have pass an empty string instead of NULL.

 

In my if else loop I have assigned value to NULL, so its not empty stirng

[jonsjava]

<?php
//require_once 'includes/config.php';

$dbusername = $_POST['email'];
$dbfirstname = $_POST['first_name'];
$dblastname = $_POST['last_name'];
//$dbmobile_number = $_POST['mobile'];
if (empty($_POST['mobile']))

{
$dbmobile_number = NULL;

}

else

{
$dbmobile_number = $_POST['mobile'];

}
if (empty($_POST['landline'])){
$dblandline_number = NULL;
}
else{
$dblandline_number = $_POST['landline'];
}
if (empty($_POST['dob'])){
$dbdob = NULL;
}
else{
$dbdob = $_POST['dob'];
}
if (empty($_POST['shipping_pincode'])){
$dbpincode = NULL;
}
else{
$dbpincode = $_POST['shipping_pincode'];
}
if(isset($_POST['is_email']))
{
$dbSubscribe_Email_Alert = '1';
}
else
{
$dbSubscribe_Email_Alert = '0';
}
if(isset($_POST['is_sms']))
{
$dbSubscribe_SMS = 0;
}
else
{
$dbSubscribe_SMS = 0;
}


$dbAddress_firstname = $_POST['shipping_first_name'];
$dbAddress_lastname = $_POST['shipping_last_name'];
$dbAddress = $_POST['shipping_address'];
$dbcity = $_POST['shipping_city'];
$dbstate = $_POST['shipping_state'];
$dbcountry = $_POST['shipping_country'];

echo "Welcome".$dbusername;

//if($_POST['btnSave'])

//if ($_POST['btnSave'])

//{

//echo "Inside query loop";
$connect = mysql_connect("localhost","root","") or die("Couldn't connect!");
mysql_select_db("salebees") or die ("Couldn't find DB");

//$query = mysql_query("SELECT * FROM users WHERE username='$username'");

$query = mysql_query("update users set firstname = '$dbfirstname', lastname = '$dblastname', mobile_number = '$dbmobile_number', landline_number = '$dblandline_number', dob = '$dbdob', Subscribe_Email_Alert = '$dbSubscribe_Email_Alert', Subscribe_SMS = '$dbSubscribe_SMS',  Address_firstname = '$dbAddress_firstname', Address_lastname = '$dbAddress_lastname', Address = '$dbAddress', city = '$dbcity', pincode = '$dbpincode', state = '$dbstate', country = '$dbcountry'      where username = '$dbusername' ");

header("location:my_account.php");


//}

//else

//{
//die();

//}


?>

 

If I understand what you want, if the data is empty, store as NULL. This code should do the trick. All I did was have it check to see if the POST data was empty. If the variable exists in the form, it will come across, so we cannot check to see if isset(), but empty(), because it will always be set, but it just may be empty. That is the check you need to be doing.

[samved]

<?php
//require_once 'includes/config.php';

$dbusername = $_POST['email'];
$dbfirstname = $_POST['first_name'];
$dblastname = $_POST['last_name'];
//$dbmobile_number = $_POST['mobile'];
if (empty($_POST['mobile']))

{
$dbmobile_number = NULL;

}

else

{
$dbmobile_number = $_POST['mobile'];

}
if (empty($_POST['landline'])){
$dblandline_number = NULL;
}
else{
$dblandline_number = $_POST['landline'];
}
if (empty($_POST['dob'])){
$dbdob = NULL;
}
else{
$dbdob = $_POST['dob'];
}
if (empty($_POST['shipping_pincode'])){
$dbpincode = NULL;
}
else{
$dbpincode = $_POST['shipping_pincode'];
}
if(isset($_POST['is_email']))
{
$dbSubscribe_Email_Alert = '1';
}
else
{
$dbSubscribe_Email_Alert = '0';
}
if(isset($_POST['is_sms']))
{
$dbSubscribe_SMS = 0;
}
else
{
$dbSubscribe_SMS = 0;
}


$dbAddress_firstname = $_POST['shipping_first_name'];
$dbAddress_lastname = $_POST['shipping_last_name'];
$dbAddress = $_POST['shipping_address'];
$dbcity = $_POST['shipping_city'];
$dbstate = $_POST['shipping_state'];
$dbcountry = $_POST['shipping_country'];

echo "Welcome".$dbusername;

//if($_POST['btnSave'])

//if ($_POST['btnSave'])

//{

//echo "Inside query loop";
$connect = mysql_connect("localhost","root","") or die("Couldn't connect!");
mysql_select_db("salebees") or die ("Couldn't find DB");

//$query = mysql_query("SELECT * FROM users WHERE username='$username'");

$query = mysql_query("update users set firstname = '$dbfirstname', lastname = '$dblastname', mobile_number = '$dbmobile_number', landline_number = '$dblandline_number', dob = '$dbdob', Subscribe_Email_Alert = '$dbSubscribe_Email_Alert', Subscribe_SMS = '$dbSubscribe_SMS',  Address_firstname = '$dbAddress_firstname', Address_lastname = '$dbAddress_lastname', Address = '$dbAddress', city = '$dbcity', pincode = '$dbpincode', state = '$dbstate', country = '$dbcountry'      where username = '$dbusername' ");

header("location:my_account.php");


//}

//else

//{
//die();

//}


?>

 

If I understand what you want, if the data is empty, store as NULL. This code should do the trick. All I did was have it check to see if the POST data was empty. If the variable exists in the form, it will come across, so we cannot check to see if isset(), but empty(), because it will always be set, but it just may be empty. That is the check you need to be doing.

 

I tried your code but it is still not working.One thing I want to tell is that when user register than in the insert query I have passed these variables as NULL, so in database it already exist as NULL.Now on running the update query they are changing to 0,0,0, 0000-00-00