Jump to content

Assigning NULL values to variable and executing SQL update query


samved

Recommended Posts

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();

 

//}

 

 

?>

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

<?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.

Link to comment
Share on other sites

<?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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.