maryjane9110024 Posted January 13, 2015 Share Posted January 13, 2015 I am trying to update the database with isset to set the value of the variable to either null or the value entered by the user. Currently, if no value has been entered null is being set as a string causing the date field to populate with zeros. How do I code it so that it populates or sets the value as null and not as string null? Here's my code: (yes I know sql injections it's still in development ) <?php require_once('../config.php'); $parcel_id = isset($_GET['parcel_id']) ? $_GET['parcel_id'] : null; //$appeal_address_match = isset($_POST['AppealAddressMatch']) ? 1 : 0; (add to sql statement -- appeal_address_match='". $appeal_address_match ."',) $date_appeal_received = isset($_POST['DateAppealReceived']) ? $_POST['DateAppealReceived'] : null; $bos_meeting_date = isset($_POST['BosMeetingDate']) ? $_POST['BosMeetingDate'] : null; $late_returns_date = isset($_POST['LateReturnsDate']) ? $_POST['LateReturnsDate'] : null; $determination_notice = isset($_POST['DeterminationNoticeSet']) ? $_POST['DeterminationNoticeSet'] : null; $final_determination = isset($_POST['FinalDetermination']) ? $_POST['FinalDetermination'] : null; $analysis_recommendation = isset($_POST['AnalysisRecommendation']) ? $_POST['AnalysisRecommendation'] : null; $email_address = isset($_POST['EmailAddress']) ? $_POST['EmailAddress'] : null; $phone_number = isset($_POST['PhoneNumber']) ? $_POST['PhoneNumber'] : null; if(isset($_GET['parcel_id'])) { $db = new ezSQL_mysql(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST); $result = $db->query("UPDATE parcels SET date_appeal_received='" . $date_appeal_received . "', bos_meeting_date='". $date_appeal_received ."', late_returns_date='". $late_returns_date ."', determination_notice_sent_date='". $determination_notice ."', final_determination='". $final_determination ."', analysis_recommendation='". $analysis_recommendation ."', email_address='". $email_address ."', phone_number='". $phone_number ."' WHERE parcel_id='" . $parcel_id ."'"); /*if ($result == TRUE) { echo '{"success":true}'; } else { echo '{"success":false}'; } */ if($result) echo 'true'; else echo 'false'; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/ Share on other sites More sharing options...
ginerjm Posted January 13, 2015 Share Posted January 13, 2015 I believe you have to set your local vars to 'null' but in the query you DON"T wrap that var in quotes. Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502817 Share on other sites More sharing options...
Ch0cu3r Posted January 13, 2015 Share Posted January 13, 2015 It is because you have wrapped the date values within single quotes in your query here bos_meeting_date='". $date_appeal_received ."', <-- wrapped in single quotes late_returns_date='". $late_returns_date ."', <-- and here When your query is executed and the date values are null it will result in the query becoming bos_meeting_date='', <-- if the date value is null it will result in an empty value late_returns_date='', <-- same here If you do not want to add a value to a field if it is null (or empty) then do not include that field in your insert query. You should dynamically build the query. In your table schema make sure to apply DEFAULT NULL to those fields which do not require a value. Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502819 Share on other sites More sharing options...
maryjane9110024 Posted January 13, 2015 Author Share Posted January 13, 2015 (edited) I tried removing the quotes and having it as bos_meeting_date=". $date_appeal_received .", but it bombs when there is a value. How do I dynamically build a query? I get a different answer every link I looked at in my Google results. I just checked and the default is null for those fields. Edited January 13, 2015 by maryjane9110024 Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502820 Share on other sites More sharing options...
Ch0cu3r Posted January 13, 2015 Share Posted January 13, 2015 (edited) A query is defined within a string.All you do is concatenate the field/value pairs to the string to define the query. Once the query has been built you pass it to $db->query to execute it. // start to define the query $query = 'UPDATE parcels SET '; // now add the date_appeal_received field value if it is not null/empty if(!empty($date_appeal_received)) $query .= "date_appeal_received='$date_appeal_received', "; // now add the bos_meeting_datefield value if it is not null/empty if(!empty($bos_meeting_date)) $query .= "bos_meeting_date='$bos_meeting_date', "; // and so on // remove the comma after the last field/value pair $query = rtrim(', ', $query); // now add the where clause $query .= " WHERE parcel_id='$parcel_id'"; // Once the query has been built, execute it $result = $db->query($query); Edited January 13, 2015 by Ch0cu3r Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502824 Share on other sites More sharing options...
QuickOldCar Posted January 13, 2015 Share Posted January 13, 2015 (edited) default pattern in the database is zeros for date What you should do instead is not let anything be added to the database if a POST field is empty First off are you using GET for parcel id and also POST through a form? If the form is used the parcel id would be lost unless you passed it in a hidden value in the form, and then you check it as POST If need to do something odd like this might as well use REQUEST for the parcel id and can pass it back to the form in a hidden value so is always there. This is how I would handle it. You can pass the parcel id back into form as a hidden value. Can also pass the other variables back into the form. EDIT : wrote this before Ch0cu3r's advice, I missed this was an update <?php $errors = array(); if (isset($_REQUEST['parcel_id']) && trim($_REQUEST['parcel_id']) != '') { //can check the expected type as well $parcel_id = trim($_REQUEST['parcel_id']); } else { $parcel_id = ''; } if (isset($_POST['submit'])) { if ($parcel_id == '') { $errors[] = "No parcel id"; } if (isset($_POST['DateAppealReceived']) && trim($_POST['DateAppealReceived']) != '') { $date_appeal_received = trim($_POST['DateAppealReceived']); } else { $errors[] = "No date appeal received"; $date_appeal_received = ''; } if (isset($_POST['BosMeetingDate']) && trim($_POST['BosMeetingDate']) != '') { $bos_meeting_date = trim($_POST['BosMeetingDate']); } else { $errors[] = "No bos meeting date"; $bos_meeting_date = ''; } if (isset($_POST['LateReturnsDate']) && trim($_POST['LateReturnsDate']) != '') { $late_returns_date = trim($_POST['LateReturnsDate']); } else { $errors[] = "No late returns date"; $late_returns_date = ''; } if (isset($_POST['DeterminationNoticeSet']) && trim($_POST['DeterminationNoticeSet']) != '') { $determination_notice = trim($_POST['DeterminationNoticeSet']); } else { $errors[] = "No determination notice"; $determination_notice = ''; } if (isset($_POST['FinalDetermination']) && trim($_POST['FinalDetermination']) != '') { $final_determination = trim($_POST['FinalDetermination']); } else { $errors[] = "No final determination"; $final_determination = ''; } if (isset($_POST['AnalysisRecommendation']) && trim($_POST['AnalysisRecommendation']) != '') { $analysis_recommendation = trim($_POST['AnalysisRecommendation']); } else { $errors[] = "No analysis recommendation"; $analysis_recommendation = ''; } if (isset($_POST['EmailAddress']) && trim($_POST['EmailAddress']) != '') { $email_address = trim($_POST['EmailAddress']); } else { $errors[] = "No email"; $email_address = ''; } if (isset($_POST['PhoneNumber']) && trim($_POST['PhoneNumber']) != '') { $phone_number = trim($_POST['PhoneNumber']); } else { $errors[] = "No email"; $phone_number = ''; } } //end post submit if (empty($errors)) { $db = new ezSQL_mysql(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST); $result = $db->query("UPDATE parcels SET date_appeal_received='" . $date_appeal_received . "', bos_meeting_date='" . $date_appeal_received . "', late_returns_date='" . $late_returns_date . "', determination_notice_sent_date='" . $determination_notice . "', final_determination='" . $final_determination . "', analysis_recommendation='" . $analysis_recommendation . "', email_address='" . $email_address . "', phone_number='" . $phone_number . "' WHERE parcel_id='" . $parcel_id . "'"); if ($result) { echo 'submitted'; } else { echo 'error submitting'; } } else { foreach ($errors as $error) { echo "$error <br />"; } } ?> Edited January 13, 2015 by QuickOldCar Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502827 Share on other sites More sharing options...
Barand Posted January 13, 2015 Share Posted January 13, 2015 I do not know if it works with PDO but prepared statements with MySQLi will write NULL to a field if the bound parameter has a null value Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502829 Share on other sites More sharing options...
maryjane9110024 Posted January 13, 2015 Author Share Posted January 13, 2015 Thank you all for the information!! What if someone was deleting an entry that was entered? How would that update the value to null or empty? Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502836 Share on other sites More sharing options...
Barand Posted January 13, 2015 Share Posted January 13, 2015 Example: mysql> SELECT * FROM city; +----+------+------+------------+ | id | xpos | ypos | cityname | +----+------+------+------------+ | 1 | 40 | 60 | Manchester | | 2 | 80 | 60 | Leeds | | 3 | 90 | 160 | Leicester | +----+------+------+------------+ mysql> UPDATE city SET cityname=NULL WHERE id = 2; mysql> SELECT * FROM city; +----+------+------+------------+ | id | xpos | ypos | cityname | +----+------+------+------------+ | 1 | 40 | 60 | Manchester | | 2 | 80 | 60 | NULL | | 3 | 90 | 160 | Leicester | +----+------+------+------------+ Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502843 Share on other sites More sharing options...
maryjane9110024 Posted January 13, 2015 Author Share Posted January 13, 2015 (edited) yea that I know but I'm talking about with what Ch0cu3r said in his post above. Edited January 13, 2015 by maryjane9110024 Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502854 Share on other sites More sharing options...
maryjane9110024 Posted January 13, 2015 Author Share Posted January 13, 2015 Quickoldcar, I wish I could prevent them but with the form if they remove information and want to update then it will display and insert zeros into the database which is what i don't want to happen. so if the user removes the date from the field i want it to be null instead of zeros if that makes any sense? Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502858 Share on other sites More sharing options...
Barand Posted January 13, 2015 Share Posted January 13, 2015 start values again mysql> SELECT * FROM city; +----+------+------+------------+ | id | xpos | ypos | cityname | +----+------+------+------------+ | 1 | 40 | 60 | Manchester | | 2 | 80 | 60 | Leeds | | 3 | 90 | 160 | Leicester | +----+------+------+------------+ Now this code, where id=2 and ypos left blank $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); if (isset($_POST['id'])) { $id = $_POST['id']; // 2 $ypos = empty($_POST['ypos']) ? null : $_POST['ypos']; // empty $sql = "UPDATE city SET ypos = ? WHERE id = ?"; $stmt = $db->prepare($sql); $stmt->bind_param('ii', $ypos, $id); $stmt->execute(); } Tables values after running code mysql> SELECT * FROM city; +----+------+------+------------+ | id | xpos | ypos | cityname | +----+------+------+------------+ | 1 | 40 | 60 | Manchester | | 2 | 80 | NULL | Leeds | | 3 | 90 | 160 | Leicester | +----+------+------+------------+ Quote Link to comment https://forums.phpfreaks.com/topic/293897-set-the-value-as-null-and-not-as-string-null-when-updating-the-database/#findComment-1502861 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.