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(); //} ?> Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/ 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? Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214355 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. Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214366 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 Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214369 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 Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214370 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. Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214371 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. Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214373 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 Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214375 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. Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214376 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 Link to comment https://forums.phpfreaks.com/topic/236193-assigning-null-values-to-variable-and-executing-sql-update-query/#findComment-1214386 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.