wongle Posted July 7, 2022 Share Posted July 7, 2022 (edited) I am having issues when I untick a checkbox and leave it blank and update my PHP / MySQL form, the data is not saved in the database. Updates text / date fields are working fine. Code (The prepare and execute statements have been shorten for easier reading) $learning_opportunities = isset($_POST['learning_opportunities']) ? $_POST['learning_opportunities'] : $contact['learning_opportunities']; $stmt = $pdo->prepare('UPDATE contacts SET current_living_situation=?, learning_opportunities=?,contact_for_other_purposes=?, where id = ?'); $result = $stmt->execute([$current_living_situation,$personal_strengths,$learning_opportunities,$declaration_organisation,$declaration_signature_date, $declaration_signature, $_POST['id']]); if($result == true){ $details = "<b>All Data Updated</b>"; // Insert new record into the contacts table $stmt = $pdo->prepare('INSERT IGNORE INTO client_activity (id,client_id,date,time,details,username) VALUES (?,?,?,?,?,?)'); $client_activity = $stmt->execute([ null,$_POST['id'],date("Y/m/d"),date("H:i:s"),$details,$_SESSION['name'] ]); if($client_activity == true){ $msg = 'Updated Successfully!'; Form code <input type="checkbox" name="learning_opportunities" value="learning_opportunities" <?php if($contact['learning_opportunities']=="Yes"){ echo 'checked'; } ?>> About courses or learning opportunities.<br> If I change the variable the below, it allows me to uncheck the box, but not select it again $learning_opportunities = isset($_POST['learning_opportunities']); I have read countless articles and tutorials and can't get it to update the data. Any help or advise would be much appreciated. Edited July 7, 2022 by wongle Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/ Share on other sites More sharing options...
Barand Posted July 7, 2022 Share Posted July 7, 2022 When you connect to the db, do you set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION so that pdo errors are reported? Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598038 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 21 minutes ago, Barand said: When you connect to the db, do you set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION so that pdo errors are reported? My code for the error reporting is but has never worked try { $pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (\PDOException $e) { throw new \PDOException($e->getMessage(), (int)$e->getCode()); // If there is an error with the connection, stop the script and display the error. exit('Failed to connect to database!'); } Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598039 Share on other sites More sharing options...
Barand Posted July 7, 2022 Share Posted July 7, 2022 If errors aren't being reported they may be being logged. Have you checked error logs? Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598040 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 2 minutes ago, Barand said: If errors aren't being reported they may be being logged. Have you checked error logs? The error logs log everything, but can't see anything for the page in question. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598041 Share on other sites More sharing options...
Barand Posted July 7, 2022 Share Posted July 7, 2022 Try forcing an error (such as trying to connect with invalid password, for example) and see if that gets logged. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598042 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 7 minutes ago, Barand said: Try forcing an error (such as trying to connect with invalid password, for example) and see if that gets logged. Yes an entry was logged for access denied to the database using wrong password. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598043 Share on other sites More sharing options...
Barand Posted July 7, 2022 Share Posted July 7, 2022 56 minutes ago, wongle said: $stmt = $pdo->prepare('UPDATE contacts SET current_living_situation=?, learning_opportunities=?,contact_for_other_purposes=?, where id = ?'); $result = $stmt->execute([$current_living_situation,$personal_strengths,$learning_opportunities,$declaration_organisation,$declaration_signature_date, $declaration_signature, $_POST['id']]); You should be getting an error from the above - 4 ? placeholders but 7 values in the paremeter array Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598044 Share on other sites More sharing options...
mac_gyver Posted July 7, 2022 Share Posted July 7, 2022 the code you have posted is not your actual code, and it's likely that the apparent sql syntax error is not really present, since you state other fields are working. there's a different list and order of fields in the query than the values that are being supplied when the query is executed. you are probably updating the checkbox value into some other column. post your actual code to get help with what is wrong with it. you also have a mistake in that the value attribute for this particular checkbox is not the string "Yes" that you think it is in the code setting the checked attribute. when you make the connection, you should also set emulated prepared queries to false (you want to run real prepared queries), and set the default fetch mode to assoc (so that you don't need to specify it in each fetch statement.) lastly, there's no good reason to catch the connection error, then rethrow it with only some of the values. you are loosing the line number and file name by doing this. simple remove the try/catch logic for the connection and let php directly handle the connection exception. the only time you should catch an handle a database exception in your code are for errors that the visitor can recover from, such as when inserting/updating duplicate or out or range data values. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598045 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 Just now, Barand said: You should be getting an error from the above - 4 ? placeholders but 7 values in the paremeter array I have only shortened them to make them easier to read. There is 30 in each. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598046 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 9 minutes ago, mac_gyver said: the code you have posted is not your actual code, and it's likely that the apparent sql syntax error is not really present, since you state other fields are working. there's a different list and order of fields in the query than the values that are being supplied when the query is executed. you are probably updating the checkbox value into some other column. post your actual code to get help with what is wrong with it. you also have a mistake in that the value attribute for this particular checkbox is not the string "Yes" that you think it is in the code setting the checked attribute. when you make the connection, you should also set emulated prepared queries to false (you want to run real prepared queries), and set the default fetch mode to assoc (so that you don't need to specify it in each fetch statement.) lastly, there's no good reason to catch the connection error, then rethrow it with only some of the values. you are loosing the line number and file name by doing this. simple remove the try/catch logic for the connection and let php directly handle the connection exception. the only time you should catch an handle a database exception in your code are for errors that the visitor can recover from, such as when inserting/updating duplicate or out or range data values. Thanks for your reply, my full code is; $housing_situation_transport_childcare = isset($_POST['housing_situation_transport_childcare']) ? $_POST['housing_situation_transport_childcare'] : $contact['housing_situation_transport_childcare']; $personal_goals = isset($_POST['personal_goals']) ? $_POST['personal_goals'] : $contact['personal_goals']; $currently_spend_time = isset($_POST['currently_spend_time']) ? $_POST['currently_spend_time'] : $contact['currently_spend_time']; $skills_training = isset($_POST['skills_training']) ? $_POST['skills_training'] : $contact['skills_training']; $personal_strengths = isset($_POST['personal_strengths']) ? $_POST['personal_strengths'] : $contact['personal_strengths']; $current_living_situation = isset($_POST['current_living_situation']) ? $_POST['current_living_situation'] : $contact['current_living_situation']; // programme completion & progression $learning_actual_end_date = isset($_POST['learning_actual_end_date']) ? $_POST['learning_actual_end_date'] : $contact['learning_actual_end_date']; $partcipant_complete_course = isset($_POST['partcipant_complete_course']) ? $_POST['partcipant_complete_course'] : $contact['partcipant_complete_course']; $withdrawal_reason = isset($_POST['withdrawal_reason']) ? $_POST['withdrawal_reason'] : $contact['withdrawal_reason']; $participant_intended_learning = isset($_POST['participant_intended_learning']) ? $_POST['participant_intended_learning'] : $contact['participant_intended_learning']; $pcp_education = isset($_POST['pcp_education']) ? $_POST['pcp_education'] : $contact['pcp_education']; $coursestart_date = isset($_POST['coursestart_date']) ? $_POST['coursestart_date'] : $contact['coursestart_date']; $education_provider_name = isset($_POST['education_provider_name']) ? $_POST['education_provider_name'] : $contact['education_provider_name']; $course_title = isset($_POST['course_title']) ? $_POST['course_title'] : $contact['course_title']; $course_level = isset($_POST['course_level']) ? $_POST['course_level'] : $contact['course_level']; $planned_glh = isset($_POST['planned_glh']) ? $_POST['planned_glh'] : $contact['planned_glh']; $in_paid_employment = isset($_POST['in_paid_employment']) ? $_POST['in_paid_employment'] : $contact['in_paid_employment']; $in_paid_employment_start_date = isset($_POST['in_paid_employment_start_date']) ? $_POST['in_paid_employment_start_date'] : $contact['in_paid_employment_start_date']; $in_paid_employer_name_address = isset($_POST['in_paid_employer_name_address']) ? $_POST['in_paid_employer_name_address'] : $contact['in_paid_employer_name_address']; $in_paid_job_title = isset($_POST['in_paid_job_title']) ? $_POST['in_paid_job_title'] : $contact['in_paid_job_title']; $in_paid_contracted_hour = isset($_POST['in_paid_contracted_hour']) ? $_POST['in_paid_contracted_hour'] : $contact['in_paid_contracted_hour']; $not_in_paid_employment = isset($_POST['not_in_paid_employment']) ? $_POST['not_in_paid_employment'] : $contact['not_in_paid_employment']; $pcp_gap_year = isset($_POST['pcp_gap_year']) ? $_POST['pcp_gap_year'] : $contact['pcp_gap_year']; $pcp_others = isset($_POST['pcp_others']) ? $_POST['pcp_others'] : $contact['pcp_others']; $pcp_voluntary_work = isset($_POST['pcp_voluntary_work']) ? $_POST['pcp_voluntary_work'] : $contact['pcp_voluntary_work']; $destination_progression_date = isset($_POST['destination_progression_date']) ? $_POST['destination_progression_date'] : $contact['destination_progression_date']; $destination_progression_collection_date = isset($_POST['destination_progression_collection_date']) ? $_POST['destination_progression_collection_date'] : $contact['destination_progression_collection_date']; $project_officer_name = isset($_POST['project_officer_name']) ? $_POST['project_officer_name'] : $contact['project_officer_name']; $project_officer_signature = isset($_POST['project_officer_signature']) ? $_POST['project_officer_signature'] : $contact['project_officer_signature']; $project_officer_date = isset($_POST['project_officer_date']) ? $_POST['project_officer_date'] : $contact['project_officer_date']; $participant__name = isset($_POST['participant__name']) ? $_POST['participant__name'] : $contact['participant__name']; $participant__signature = isset($_POST['participant__signature']) ? $_POST['participant__signature'] : $contact['participant__signature']; $participant__date = isset($_POST['participant__date']) ? $_POST['participant__date'] : $contact['participant__date']; $final_assessment_progress_you_made = isset($_POST['final_assessment_progress_you_made']) ? $_POST['final_assessment_progress_you_made'] : $contact['final_assessment_progress_you_made']; $final_assessment_progress_your_goal = isset($_POST['final_assessment_progress_your_goal']) ? $_POST['final_assessment_progress_your_goal'] : $contact['final_assessment_progress_your_goal']; $final_assessment_progress_your_reach_goal = isset($_POST['final_assessment_progress_your_reach_goal']) ? $_POST['final_assessment_progress_your_reach_goal'] : $contact['final_assessment_progress_your_reach_goal']; $final_assessment_progress_overall = isset($_POST['final_assessment_progress_overall']) ? $_POST['final_assessment_progress_overall'] : $contact['final_assessment_progress_overall']; $final_assessment_participat_name = isset($_POST['final_assessment_participat_name']) ? $_POST['final_assessment_participat_name'] : $contact['final_assessment_participat_name']; $final_assessment_participat_signature = isset($_POST['final_assessment_participat_signature']) ? $_POST['final_assessment_participat_signature'] : $contact['final_assessment_participat_signature']; $final_assessment_participat_date = isset($_POST['final_assessment_participat_date']) ? $_POST['final_assessment_participat_date'] : $contact['final_assessment_participat_date']; $final_assessment_project_worker_name = isset($_POST['final_assessment_project_worker_name']) ? $_POST['final_assessment_project_worker_name'] : $contact['final_assessment_project_worker_name']; $final_assessment_project_worker_signature = isset($_POST['final_assessment_project_worker_signature']) ? $_POST['final_assessment_project_worker_signature'] : $contact['final_assessment_project_worker_signature']; $final_assessment_project_worker_date = isset($_POST['final_assessment_project_worker_date']) ? $_POST['final_assessment_project_worker_date'] : $contact['final_assessment_project_worker_date']; // ----------- $contact['learning_opportunities'] = isset($_POST['learning_opportunities']) ? "yes" : "no"; $contact_for_other_purposes = isset($_POST['contact_for_other_purposes']); $empowering_communities = isset($_POST['empowering_communities']); $empowering_communities_name = isset($_POST['empowering_communities_name']) ? $_POST['empowering_communities_name'] : $contact['empowering_communities_name']; $empowering_communities_sign = isset($_POST['empowering_communities_sign']) ? $_POST['empowering_communities_sign'] : $contact['empowering_communities_sign']; $empowering_communities_date = isset($_POST['empowering_communities_date']) ? $_POST['empowering_communities_date'] : $contact['empowering_communities_date']; $participant_enrolled_onto = isset($_POST['participant_enrolled_onto']) ? $_POST['participant_enrolled_onto'] : $contact['participant_enrolled_onto']; $participant_moved_another_provider = isset($_POST['participant_moved_another_provider']) ? $_POST['participant_moved_another_provider'] : $contact['participant_moved_another_provider']; $participant_eligible_free_school = isset($_POST['participant_eligible_free_school']) ? $_POST['participant_eligible_free_school'] : $contact['participant_eligible_free_school']; $british_passport = isset($_POST['british_passport']) ? $_POST['british_passport'] : $contact['british_passport']; $eec_passport = isset($_POST['eec_passport']) ? $_POST['eec_passport'] : $contact['eec_passport']; $euss_via_home = isset($_POST['euss_via_home']) ? $_POST['euss_via_home'] : $contact['euss_via_home']; $preferred_evidence = isset($_POST['preferred_evidence']) ? $_POST['preferred_evidence'] : $contact['preferred_evidence']; $provide_preferred_evidence = isset($_POST['provide_preferred_evidence']) ? $_POST['provide_preferred_evidence'] : $contact['provide_preferred_evidence']; $option_adoption_vertificate = isset($_POST['option_adoption_vertificate']) ? $_POST['option_adoption_vertificate'] : $contact['option_adoption_vertificate']; $option_driving_licence = isset($_POST['option_driving_licence']) ? $_POST['option_driving_licence'] : $contact['option_driving_licence']; $option_non_eu_passport = isset($_POST['option_non_eu_passport']) ? $_POST['option_non_eu_passport'] : $contact['option_non_eu_passport']; $option_biometric_immigration = isset($_POST['option_biometric_immigration']) ? $_POST['option_biometric_immigration'] : $contact['option_biometric_immigration']; $option_current_immigration = isset($_POST['option_current_immigration']) ? $_POST['option_current_immigration'] : $contact['option_current_immigration']; $option_marriage_civil_partnership = isset($_POST['option_marriage_civil_partnership']) ? $_POST['option_marriage_civil_partnership'] : $contact['option_marriage_civil_partnership']; $option_other_evidence = isset($_POST['option_other_evidence']) ? $_POST['option_other_evidence'] : $contact['option_other_evidence']; $option_nine = isset($_POST['option_nine']) ? $_POST['option_nine'] : $contact['option_nine']; $details_evidence_provided = isset($_POST['details_evidence_provided']) ? $_POST['details_evidence_provided'] : $contact['details_evidence_provided']; $dwp_job_centre_letter = isset($_POST['dwp_job_centre_letter']) ? $_POST['dwp_job_centre_letter'] : $contact['dwp_job_centre_letter']; $confirmation_relevant_organisation = isset($_POST['confirmation_relevant_organisation']) ? $_POST['confirmation_relevant_organisation'] : $contact['confirmation_relevant_organisation']; $self_certification_evidence = isset($_POST['self_certification_evidence']) ? $_POST['self_certification_evidence'] : $contact['self_certification_evidence']; $partcipant_told_support = isset($_POST['partcipant_told_support']) ? $_POST['partcipant_told_support'] : $contact['partcipant_told_support']; $participant_file_completed_remotly = isset($_POST['participant_file_completed_remotly']) ? $_POST['participant_file_completed_remotly'] : $contact['participant_file_completed_remotly']; $declaration_name_please_print = isset($_POST['declaration_name_please_print']) ? $_POST['declaration_name_please_print'] : $contact['declaration_name_please_print']; $declaration_job_title = isset($_POST['declaration_job_title']) ? $_POST['declaration_job_title'] : $contact['declaration_job_title']; $declaration_organisation = isset($_POST['declaration_organisation']) ? $_POST['declaration_organisation'] : $contact['declaration_organisation']; $declaration_signature_date = isset($_POST['declaration_signature_date']) ? $_POST['declaration_signature_date'] : $contact['declaration_signature_date']; $declaration_signature = isset($_POST['declaration_signature']) ? $_POST['declaration_signature'] : $contact['declaration_signature']; // Update the record $stmt = $pdo->prepare('UPDATE contacts SET current_living_situation=?, personal_strengths=?, skills_training=?, currently_spend_time=?,personal_goals=?,housing_situation_transport_childcare=?, learning_actual_end_date=?, partcipant_complete_course=?, withdrawal_reason=?,participant_intended_learning=?,pcp_education=?, coursestart_date=?,education_provider_name=?,course_title=?,course_level=?,planned_glh=?,in_paid_employment=?,in_paid_employment_start_date=?, in_paid_employer_name_address=?,in_paid_job_title=?,in_paid_contracted_hour=?,not_in_paid_employment=?,pcp_gap_year=?, pcp_others=?,pcp_voluntary_work=?,destination_progression_date=?,destination_progression_collection_date=?,project_officer_name=?, project_officer_signature=?,project_officer_date=?,participant__name=?,participant__signature=?,participant__date=?, final_assessment_progress_you_made=?,final_assessment_progress_your_goal=?,final_assessment_progress_your_reach_goal=?, final_assessment_progress_overall=?,final_assessment_participat_name=?,final_assessment_participat_signature=?, final_assessment_participat_date=?,final_assessment_project_worker_name=?,final_assessment_project_worker_signature=?, final_assessment_project_worker_date=?,learning_opportunities=?,contact_for_other_purposes=?,empowering_communities=?,empowering_communities_name=?,empowering_communities_sign=?,empowering_communities_date=?, participant_enrolled_onto=?,participant_moved_another_provider=?,participant_eligible_free_school=?,british_passport=?, eec_passport=?,euss_via_home=?,preferred_evidence=?,provide_preferred_evidence=?,option_adoption_vertificate=?,option_driving_licence=?, option_non_eu_passport=?,option_biometric_immigration=?,option_current_immigration=?,option_marriage_civil_partnership=?, option_other_evidence=?,option_nine=?,details_evidence_provided=?,dwp_job_centre_letter=?,confirmation_relevant_organisation=?,self_certification_evidence=?, partcipant_told_support=?,participant_file_completed_remotly=?,declaration_name_please_print=?,declaration_job_title=?,declaration_organisation=?, declaration_signature_date=?,declaration_signature=? where id = ?'); $result = $stmt->execute([$current_living_situation,$personal_strengths,$skills_training,$currently_spend_time,$personal_goals, $housing_situation_transport_childcare,$learning_actual_end_date,$partcipant_complete_course,$withdrawal_reason,$participant_intended_learning,$pcp_education, $coursestart_date,$education_provider_name,$course_title,$course_level,$planned_glh,$in_paid_employment,$in_paid_employment_start_date, $in_paid_employer_name_address,$in_paid_job_title,$in_paid_contracted_hour,$not_in_paid_employment,$pcp_gap_year,$pcp_others, $pcp_voluntary_work,$destination_progression_date,$destination_progression_collection_date,$project_officer_name,$project_officer_signature, $project_officer_date,$participant__name,$participant__signature,$participant__date,$final_assessment_progress_you_made, $final_assessment_progress_your_goal,$final_assessment_progress_your_reach_goal,$final_assessment_progress_overall,$final_assessment_participat_name, $final_assessment_participat_signature,$final_assessment_participat_date,$final_assessment_project_worker_name,$final_assessment_project_worker_signature, $final_assessment_project_worker_date,$learning_opportunities,$contact_for_other_purposes,$empowering_communities,$empowering_communities_name,$empowering_communities_sign,$empowering_communities_date, $participant_enrolled_onto,$participant_moved_another_provider,$participant_eligible_free_school,$british_passport, $eec_passport,$euss_via_home,$preferred_evidence,$provide_preferred_evidence,$option_adoption_vertificate,$option_driving_licence, $option_non_eu_passport,$option_biometric_immigration,$option_current_immigration,$option_marriage_civil_partnership,$option_other_evidence,$option_nine, $details_evidence_provided,$dwp_job_centre_letter,$confirmation_relevant_organisation,$self_certification_evidence,$partcipant_told_support, $participant_file_completed_remotly,$declaration_name_please_print,$declaration_job_title,$declaration_organisation,$declaration_signature_date, $declaration_signature, $_POST['id']]); if($result == true){ $details = "<b>All Data Updated</b>"; // Insert new record into the contacts table $stmt = $pdo->prepare('INSERT IGNORE INTO client_activity (id,client_id,date,time,details,username) VALUES (?,?,?,?,?,?)'); $client_activity = $stmt->execute([ null,$_POST['id'],date("Y/m/d"),date("H:i:s"),$details,$_SESSION['name'] ]); if($client_activity == true){ $msg = 'Updated Successfully!'; }else{ $msg = "Something Missing with Client Activity."; } } else{ $msg = "Something Missing. Information is not updated!"; } } Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598047 Share on other sites More sharing options...
mac_gyver Posted July 7, 2022 Share Posted July 7, 2022 (edited) 12 minutes ago, wongle said: There is 30 in each after you find and fix the current problem, you need to switch to use a data-driven design, where you have a data structure (array, database table) that defines the fields, labels, types, validation, and processing (which fields are part of the set or where term in an update query) that you then loop over to get the computer to do the work for you, rather than doing assembly line typing out of code for every field. Edit: doing this will also eliminate the need to create and manage verbose variable names. Edit2: based on the wall of code you have, perhaps using a data-driven design might be the first step toward produce mistake free code, not the second step. Edited July 7, 2022 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598048 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 11 minutes ago, mac_gyver said: after you find and fix the current problem, you need to switch to use a data-driven design, where you have a data structure (array, database table) that defines the fields, labels, types, validation, and processing (which fields are part of the set or where term in an update query) that you then loop over to get the computer to do the work for you, rather than doing assembly line typing out of code for every field. Edit: doing this will also eliminate the need to create and manage verbose variable names. Edit2: based on the wall of code you have, perhaps using a data-driven design might be the first step toward produce mistake free code, not the second step. We are getting a new system next year and that's something that will get addressed - thanks for the advice. Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598050 Share on other sites More sharing options...
mac_gyver Posted July 7, 2022 Share Posted July 7, 2022 (edited) based on the posted code, the reason for that field not updating is clear. you are assigning the result of the isset() test to - $contact['learning_opportunities'], not $learning_opportunities, like you miss-posted at the top of this thread. this should have been producing an undefined variable error at the use of $learning_opportunities in the execute() call. is your php's error_reporting value set to E_ALL so that all php errors are being reported? edit: here's another issue. except for unchecked checkbox/radio buttons, all form fields will be set once the form has been submitted. by using isset() for these always set fields, you are hiding simple typing mistakes and adding to the wall of unmaintainable code. the only isset() statements should be for the cases of checkbox/radio fields. Edited July 7, 2022 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598051 Share on other sites More sharing options...
wongle Posted July 7, 2022 Author Share Posted July 7, 2022 (edited) 1 hour ago, mac_gyver said: based on the posted code, the reason for that field not updating is clear. you are assigning the result of the isset() test to - $contact['learning_opportunities'], not $learning_opportunities, like you miss-posted at the top of this thread. this should have been producing an undefined variable error at the use of $learning_opportunities in the execute() call. is your php's error_reporting value set to E_ALL so that all php errors are being reported? edit: here's another issue. except for unchecked checkbox/radio buttons, all form fields will be set once the form has been submitted. by using isset() for these always set fields, you are hiding simple typing mistakes and adding to the wall of unmaintainable code. the only isset() statements should be for the cases of checkbox/radio fields. Thanks for this, really helped me out a lot. I forgot to add the first part to the code; if (isset($_GET['id'])) { $stmt = $pdo->prepare('SELECT * FROM contacts WHERE id = ?'); $stmt->execute([$_GET['id']]); $contact = $stmt->fetch(PDO::FETCH_ASSOC); if (!$contact) { exit('Contact doesn\'t exist with that ID!'); and found that the below code works fine $learning_opportunities = isset($_POST['learning_opportunities']) ? "yes" : "no"; Having taken onboard your advise, I am going to rewrite this form over the weekend, have you got any good tutorial links please for what you mentioned above please? Edited July 7, 2022 by wongle Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598055 Share on other sites More sharing options...
Barand Posted July 7, 2022 Share Posted July 7, 2022 2 hours ago, wongle said: $stmt = $pdo->prepare('UPDATE contacts SET current_living_situation=?, personal_strengths=?, skills_training=?, currently_spend_time=?,personal_goals=?,housing_situation_transport_childcare=?, learning_actual_end_date=?, partcipant_complete_course=?, withdrawal_reason=?,participant_intended_learning=?,pcp_education=?, coursestart_date=?,education_provider_name=?,course_title=?,course_level=?,planned_glh=?,in_paid_employment=?,in_paid_employment_start_date=?, in_paid_employer_name_address=?,in_paid_job_title=?,in_paid_contracted_hour=?,not_in_paid_employment=?,pcp_gap_year=?, pcp_others=?,pcp_voluntary_work=?,destination_progression_date=?,destination_progression_collection_date=?,project_officer_name=?, project_officer_signature=?,project_officer_date=?,participant__name=?,participant__signature=?,participant__date=?, final_assessment_progress_you_made=?,final_assessment_progress_your_goal=?,final_assessment_progress_your_reach_goal=?, final_assessment_progress_overall=?,final_assessment_participat_name=?,final_assessment_participat_signature=?, final_assessment_participat_date=?,final_assessment_project_worker_name=?,final_assessment_project_worker_signature=?, final_assessment_project_worker_date=?,learning_opportunities=?,contact_for_other_purposes=?,empowering_communities=?,empowering_communities_name=?,empowering_communities_sign=?,empowering_communities_date=?, participant_enrolled_onto=?,participant_moved_another_provider=?,participant_eligible_free_school=?,british_passport=?, eec_passport=?,euss_via_home=?,preferred_evidence=?,provide_preferred_evidence=?,option_adoption_vertificate=?,option_driving_licence=?, option_non_eu_passport=?,option_biometric_immigration=?,option_current_immigration=?,option_marriage_civil_partnership=?, option_other_evidence=?,option_nine=?,details_evidence_provided=?,dwp_job_centre_letter=?,confirmation_relevant_organisation=?,self_certification_evidence=?, partcipant_told_support=?,participant_file_completed_remotly=?,declaration_name_please_print=?,declaration_job_title=?,declaration_organisation=?, declaration_signature_date=?,declaration_signature=? where id = ?'); Are most of those the checkbox values? Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598056 Share on other sites More sharing options...
mac_gyver Posted July 7, 2022 Share Posted July 7, 2022 (edited) 7 hours ago, wongle said: have you got any good tutorial links please for what you mentioned above please? here's a data driven example. tested with some fake data for the 1st form field and id field. requires coding for other field types. <?php // data-driven example // update table name $table = 'contacts'; // define update fields $fields = []; // notes: // the array index is the field name // label is the visible label for the field and is also used when building error messages // type is the form field type. this allows different processing code for the different field types, using a switch/case statement. // choices are for checkbox, radio, and select/option fields. these can be used to dynamically produce the form fields, and are used when validating the submitted data. can be entirely left out for other field types. // required is either true or false and determines the required (non-empty) validation // update_set and update_where being true or false determines where the field is used when building the query // elements that can be true or false can be entirely left out for the false case. // add elements to this definition and the corresponding code as needed for each different situation // i have guessed as to the type and required value for the first field for demonstration purposes $fields['current_living_situation'] = ['label'=>'Current Living Situation','type'=>'text','choices'=>[],'required'=>true,'update_set'=>true,'update_where'=>false]; // here are empty definitions for the rest, to get you started /* $fields['personal_strengths'] = []; $fields['skills_training'] = []; $fields['currently_spend_time'] = []; $fields['personal_goals'] = []; $fields['housing_situation_transport_childcare'] = []; $fields['learning_actual_end_date'] = []; $fields['partcipant_complete_course'] = []; $fields['withdrawal_reason'] = []; $fields['participant_intended_learning'] = []; $fields['pcp_education'] = []; $fields['coursestart_date'] = []; $fields['education_provider_name'] = []; $fields['course_title'] = []; $fields['course_level'] = []; $fields['planned_glh'] = []; $fields['in_paid_employment'] = []; $fields['in_paid_employment_start_date'] = []; $fields['in_paid_employer_name_address'] = []; $fields['in_paid_job_title'] = []; $fields['in_paid_contracted_hour'] = []; $fields['not_in_paid_employment'] = []; $fields['pcp_gap_year'] = []; $fields['pcp_others'] = []; $fields['pcp_voluntary_work'] = []; $fields['destination_progression_date'] = []; $fields['destination_progression_collection_date'] = []; $fields['project_officer_name'] = []; $fields['project_officer_signature'] = []; $fields['project_officer_date'] = []; $fields['participant__name'] = []; $fields['participant__signature'] = []; $fields['participant__date'] = []; $fields['final_assessment_progress_you_made'] = []; $fields['final_assessment_progress_your_goal'] = []; $fields['final_assessment_progress_your_reach_goal'] = []; $fields['final_assessment_progress_overall'] = []; $fields['final_assessment_participat_name'] = []; $fields['final_assessment_participat_signature'] = []; $fields['final_assessment_participat_date'] = []; $fields['final_assessment_project_worker_name'] = []; $fields['final_assessment_project_worker_signature'] = []; $fields['final_assessment_project_worker_date'] = []; $fields['learning_opportunities'] = []; $fields['contact_for_other_purposes'] = []; $fields['empowering_communities'] = []; $fields['empowering_communities_name'] = []; $fields['empowering_communities_sign'] = []; $fields['empowering_communities_date'] = []; $fields['participant_enrolled_onto'] = []; $fields['participant_moved_another_provider'] = []; $fields['participant_eligible_free_school'] = []; $fields['british_passport'] = []; $fields['eec_passport'] = []; $fields['euss_via_home'] = []; $fields['preferred_evidence'] = []; $fields['provide_preferred_evidence'] = []; $fields['option_adoption_vertificate'] = []; $fields['option_driving_licence'] = []; $fields['option_non_eu_passport'] = []; $fields['option_biometric_immigration'] = []; $fields['option_current_immigration'] = []; $fields['option_marriage_civil_partnership'] = []; $fields['option_other_evidence'] = []; $fields['option_nine'] = []; $fields['details_evidence_provided'] = []; $fields['dwp_job_centre_letter'] = []; $fields['confirmation_relevant_organisation'] = []; $fields['self_certification_evidence'] = []; $fields['partcipant_told_support'] = []; $fields['participant_file_completed_remotly'] = []; $fields['declaration_name_please_print'] = []; $fields['declaration_job_title'] = []; $fields['declaration_organisation'] = []; $fields['declaration_signature_date'] = []; $fields['declaration_signature'] = []; */ // note: if you get an error for this field, it's due to either a programming mistake or something submitting their own data w/o an id $fields['id'] = ['label'=>'Contact ID','type'=>'hidden','choices'=>[],'required'=>true,'update_set'=>false,'update_where'=>true]; // make database connection here... $post = []; // array to hold a trimmed working copy of the form data $errors = []; // array to hold user/validation errors // post method form processing code if($_SERVER['REQUEST_METHOD'] == 'POST') { // trim all the data at once $post = array_map('trim',$_POST); // if any of the fields are arrays, use a recursive trim call-back function here instead of php's trim function // validate inputs // required validation foreach($fields as $field=>$arr) { switch($arr['type']) { case 'text': case 'hidden': if(($arr['required'] ?? false) && $post[$field] === '') { $errors[$field] = "{$arr['label']} is required."; } break; } } // add other types of validation here, such as checkbox, radio, select/option choice... // if no errors, use the submitted data if(empty($errors)) { $set_terms = []; // array to hold each set term $set_params = []; // array to hold prepared query set inputs $where_terms = []; // array to hold each where term $where_params = []; // array to hold prepared query where inputs // build sql update query foreach($fields as $field=>$arr) { if($arr['update_set'] ?? false) { $set_terms[] = "`$field`=?"; $set_params[] = $post[$field]; } if($arr['update_where'] ?? false) { $where_terms[] = "`$field`=?"; $where_params[] = $post[$field]; } } $where = !empty($where_terms) ? " WHERE ".implode(' AND ',$where_terms) : ''; $sql = "UPDATE `$table` SET ".implode(',',$set_terms).$where; $stmt=$pdo->prepare($sql); $params = array_merge($set_params,$where_params); $stmt->execute($params); } // if no errors, success if(empty($errors)) { // redirect to the exact same url of the current page to cause a get request for the page // if you want to display a one-time success message, store it in a session variable here, // then test, display, and clear that session variable at the appropriate point in the html document die(header("Refresh:0")); } } // get method business logic - get/produce data needed to display the page // trim and validate the get id input $get_id = isset($_GET['id']) ? intval(trim($_GET['id'])) : 0; if($get_id < 1) { $errors['get_id'] = 'Input id is not valid.'; } // query for and fetch the initial data needed for the page, if there is a valid get_id and the form has never been submitted if(empty($errors['get_id']) && empty($post)) { $sql="SELECT * FROM contacts WHERE id = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([ $get_id ]); if(!$post = $stmt->fetch()) { $errors['get_id'] = "Contact doesn't exist for the submitted ID!"; } } // html document starts here... ?> <?php // display any errors if(!empty($errors)) { echo "<p>".implode('<br>',$errors)."</p>"; } ?> <?php // display form echo "<form method='post'>\n"; foreach($fields as $field=>$arr) { switch($arr['type']) { case 'text': $req = ($arr['required'] ?? false) ? ' required' : ''; echo "<label>{$arr['label']}: <input type='{$arr['type']}' name='$field'$req value='".htmlentities($post[$field] ?? '',ENT_QUOTES)."'></label><br>\n"; break; case 'hidden': echo "<input type='{$arr['type']}' name='$field' value='".htmlentities($post[$field] ?? '',ENT_QUOTES)."'>\n"; break; } } echo "<input type='submit'>\n</form>\n"; for the code needed for other field types, pick one field of a new type and get it working completely. once you have code for each different field type, fill in the $fields definitions. Edited July 7, 2022 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/#findComment-1598057 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.