samved Posted May 12, 2011 Share Posted May 12, 2011 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(); //} ?> Quote Link to comment Share on other sites More sharing options...
phppaper Posted May 12, 2011 Share Posted May 12, 2011 Have you try to disable the default value in phpmyadmin and see? Quote Link to comment Share on other sites More sharing options...
JasonLewis Posted May 12, 2011 Share Posted May 12, 2011 I was under the impression that the default for these column types was not allowed to be null. However I may be wrong. Quote Link to comment Share on other sites More sharing options...
samved Posted May 12, 2011 Author Share Posted May 12, 2011 Have you try to disable the default value in phpmyadmin and see? e Yup I tried but it still not takes NULL value Quote Link to comment Share on other sites More sharing options...
phppaper Posted May 12, 2011 Share Posted May 12, 2011 FYI: http://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql Quote Link to comment Share on other sites More sharing options...
samved Posted May 12, 2011 Author Share Posted May 12, 2011 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. Quote Link to comment Share on other sites More sharing options...
phppaper Posted May 12, 2011 Share Posted May 12, 2011 http://php.net/manual/en/language.types.null.php I think you have pass an empty string instead of NULL. Quote Link to comment Share on other sites More sharing options...
samved Posted May 12, 2011 Author Share Posted May 12, 2011 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 Quote Link to comment Share on other sites More sharing options...
jonsjava Posted May 12, 2011 Share Posted May 12, 2011 <?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. Quote Link to comment Share on other sites More sharing options...
samved Posted May 12, 2011 Author Share Posted May 12, 2011 <?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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.