Opeyemi AbdulRasheed
asked on
Update Two MySql Tables with Modal
Hello Expert!
I need expert help. I'm completely lost in this. I've tried what I could.
Let me explain what I want:
Note: Two tables are involved: Users (no duplicate record) and UserClass (there are duplicate records);
When a user is registered, subjects and classes are assigned - a user can teach 1 or more subject in 1 or more classes, hence, reason for the duplicate records in the UserClass table
So,
1. The modal should be able to update the user data;
2. One or more rows in the table section of the modal can be added or removed. Meaning, Subject_Taught and Class_Taught are subject to change. Not only updated but completely removed or new added.
3. I want a row to be deleted from database (UserClass table) when the red button (delete icon) on the modal is clicked.
4. I want new row to be inserted if the green button (Add icon) on the modal is clicked as long as the two fields are filled.
getUpdateForm.php
staff_update.php
The Ajax Script
Please help.
I need expert help. I'm completely lost in this. I've tried what I could.
Let me explain what I want:
Note: Two tables are involved: Users (no duplicate record) and UserClass (there are duplicate records);
When a user is registered, subjects and classes are assigned - a user can teach 1 or more subject in 1 or more classes, hence, reason for the duplicate records in the UserClass table
So,
1. The modal should be able to update the user data;
2. One or more rows in the table section of the modal can be added or removed. Meaning, Subject_Taught and Class_Taught are subject to change. Not only updated but completely removed or new added.
3. I want a row to be deleted from database (UserClass table) when the red button (delete icon) on the modal is clicked.
4. I want new row to be inserted if the green button (Add icon) on the modal is clicked as long as the two fields are filled.
getUpdateForm.php
<?php
require_once '../includes/connection.php';
$query = $conn->prepare("SELECT * FROM Users WHERE UserId = ?");
$query->bind_param("i", $_POST['id']);
$query->execute();
$result = $query->get_result();
$staff = $result->fetch_object();
$stmt = $conn->prepare("SELECT UserClassId, UserId, SubjectTaught, ClassTaught FROM UserClass WHERE UserId = ?");
$stmt->bind_param("i", $_POST['id']);
$stmt->execute();
$SubClass = $stmt->get_result();
$staffRole = $conn->query("SELECT Role_Name FROM tbl_roles");
$classes = $conn->query("SELECT Class_ID, Class_Name FROM tbl_classes");
$subjects = $conn->query("SELECT Subject_Code, Subject_Name FROM tbl_subjects");
?>
<form class="form-horizontal" id="updateStaff-Form">
<div class="modal-body">
<div id="updateResponse" class="center"></div>
<div class="form-group">
<label class="control-label col-xs-12 col-sm-3 no-padding-right" for="initial">Initial<span style=color:red;> *</span>
</label>
<div class="col-xs-12 col-sm-9">
<div class="clearfix">
<input type="hidden" name="user_id" class="col-xs-12 col-sm-9" value="<?= $staff->UserId ?>">
<input type="text" name="initial" class="col-xs-12 col-sm-9" value="<?= $staff->Initials ?>" autofocus autocomplete="off">
</div>
</div>
</div>
<div class="form-group">
<label class="control-label col-xs-12 col-sm-3 no-padding-right" for="username">Username<span style=color:red;> *</span>
</label>
<div class="col-xs-12 col-sm-9">
<div class="clearfix">
<input type="text" name="username" class="col-xs-12 col-sm-9" value="<?= $staff->Username ?>" autocomplete="off">
</div>
</div>
</div>
<div class="form-group">
<label class="control-label col-xs-12 col-sm-3 no-padding-right" for="password">Password<span style=color:red;> *</span>
</label>
<div class="col-xs-12 col-sm-9">
<div class="clearfix">
<input type="text" name="password" class="col-xs-12 col-sm-9" value="<?= $staff->Password ?>" autocomplete="off">
</div>
</div>
</div>
<div class="form-group">
<label class="control-label col-xs-12 col-sm-3 no-padding-right" for="role">Role<span style=color:red;> *</span>
</label>
<div class="col-xs-12 col-sm-9">
<div class="clearfix">
<select name="role" id="role" class="col-xs-12 col-sm-9">
<?php foreach ($staffRole as $role): ?>
<option value="<?= $role['Role_Name'] ?>" <?= ($staff->Role == $role['Role_Name']) ? 'selected' : null ?>><?= $role['Role_Name'] ?></option>
<?php endforeach; ?>
</select>
</div>
</div>
</div>
<div class="form-group">
<label class="control-label col-xs-12 col-sm-3 no-padding-right" for="class">Class Assigned</label>
<div class="col-xs-12 col-sm-9">
<select name="class_assigned" id="class_assigned" class="col-xs-12 col-sm-9">
<option value="">Select Class...</option>
<?php foreach ($classes as $class_assigned): ?>
<option value="<?= $class_assigned['Class_ID'] ?>" <?= ($staff->ClassAssigned == $class_assigned['Class_ID']) ? 'selected' : null ?>><?= $class_assigned['Class_Name'] ?></option>
<?php endforeach; ?>
</select>
</div>
</div>
<div class="clearfix"></div>
<div class="col-xs-12 col-sm-12">
<table class="table table-bordered" id="staffUpdateTable">
<thead>
<tr>
<th>Subject Taught<span style=color:red;> *</span></th>
<th>Class Taught<span style=color:red;> *</span></th>
<th class="hidden"></th>
<th>
<button type="button" name="addRow" class="btn btn-success btn-sm addRow"><span class="glyphicon glyphicon-plus"></span></button>
</th>
</tr>
</thead>
<tbody>
<?php while ($SubClassTaught = $SubClass->fetch_object()): ?>
<tr>
<td>
<select name="subject_taught[]" id="subject_taught" class="form-control subject_taught">
<?php foreach ($subjects as $subject_taught): ?>
<option value="<?= $subject_taught['Subject_Code'] ?>" <?= ($SubClassTaught->SubjectTaught == $subject_taught['Subject_Code']) ? 'selected' : null ?>><?= $subject_taught['Subject_Name'] ?></option>
<?php endforeach; ?>
</select>
</td>
<td>
<select name="class_taught[]" id="class_taught" class="form-control class_taught">
<?php foreach ($classes as $class_taught): ?>
<option value="<?= $class_taught['Class_ID'] ?>" <?= ($SubClassTaught->ClassTaught == $class_taught['Class_ID']) ? 'selected' : null ?>><?= $class_taught['Class_Name'] ?></option>
<?php endforeach; ?>
</select>
</td>
<td class="hidden">
<input type="hidden" name="userClass_id" class="col-xs-12 col-sm-9" value="<?= $SubClassTaught->UserClassId ?>">
</td>
<td>
<button type="button" name="removeRow" class="btn btn-danger btn-sm removeRow" data-id='{$SubClassTaught->UserClassId}'><span class="glyphicon glyphicon-trash"></span></button>
</td>
</tr>
<?php endwhile; ?>
</table>
</div>
</div>
</form>staff_update.php
<?php
if ( ! empty($_POST) ):
include('../includes/connection.php');
// Let's see if we have values
$missingSubjects = count( array_filter($_POST['subject_taught'], function($i) { return $i == ''; }) );
$missingClasses = count( array_filter($_POST['class_taught'], function($i) { return $i == ''; }) );
if ( empty( $_POST["initial"] ) || empty( $_POST["username"] ) || empty( $_POST["password"] ) || empty( $_POST["role"] ) || $missingSubjects || $missingClasses ):
die("<div class='alert alert-danger'>
<span class='badge badge-danger'>Required</span> You must fill in all fields.
</div>");
endif;
$userSubClass = "UPDATE UserClass SET SubjectTaught = ?, ClassTaught = ? WHERE UserClassId = ?";
$result = $conn->prepare($userSubClass);
$result->bind_param("ssi", $subject, $class, $userClassId);
$userClassId = $_POST["userClass_id"];
$sqlStr = "UPDATE Users SET Initials = ?, Username = ?, Password = ?, Role = ?, ClassAssigned = ? WHERE UserId = ?";
$stmt = $conn->prepare($sqlStr);
$stmt->bind_param("sssssi", $initial, $username, $password, $role, $class_assigned, $id);
$initial = $_POST["initial"];
$username = $_POST["username"];
$password = $_POST["password"];
$role = $_POST["role"];
$class_assigned = $_POST['class_assigned'];
$id = $_POST["user_id"];
if ( $stmt->execute() ):
foreach ($_POST['subject_taught'] as $key => $subject):
$class = $_POST['class_taught'][$key];
$result->execute();
endforeach;
die("<div class='alert alert-success'>
<span class='badge badge-success'>".$_POST["initial"]."</span> was successfully updated.
</div>");
endif;
endif;
?>The Ajax Script
$('#update_staff').click(function() {
$.ajax({
url : 'staff_update.php',
method : 'post',
data : $('#updateStaff-Form').serialize(),
}).done(function(response) {
$('#updateResponse').html(response);
$('#dynamicTable').DataTable().ajax.reload();
});
});Please help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
UPDATE:
After little manipulation, the following echo "Record Deleted" and row removed but data remains in the database:
After little manipulation, the following echo "Record Deleted" and row removed but data remains in the database:
$(document).on('click', '.removeRow', function(e){
var currentRow = $(this).parents('tr');
$.post( "deleteSubClass.php", { id: $(this).data('id') }, function( response ) {
currentRow.remove();
alert(response);
});
});
Looking at your getUpdateForm, you're not echoing out the ID properly, so there's nothing to send to the script:
<button type="button" name="removeRow" class="btn btn-danger btn-sm removeRow" data-id='{$SubClassTaught->UserClas
Should be:
<button type="button" name="removeRow" class="btn btn-danger btn-sm removeRow" data-id='<?= $SubClassTaught->UserClass
<button type="button" name="removeRow" class="btn btn-danger btn-sm removeRow" data-id='{$SubClassTaught->UserClas
Should be:
<button type="button" name="removeRow" class="btn btn-danger btn-sm removeRow" data-id='<?= $SubClassTaught->UserClass
ASKER
Thank you.
But the Update Script is still not working well.
Out of two rows updated, only one (usually the last row) was saved other no changes.
But the Update Script is still not working well.
Out of two rows updated, only one (usually the last row) was saved other no changes.
Hey Opeyemi,
Not sure why you closed the question off if it's not yet resolved.
I think I've mentioned before that when coding your application, it makes sense to deal with it piece by piece, rather than trying to do everything at once. Currently, you're trying to do everything at once, which means it's not easy to track down where the problem lies.
What I normally do when coding this kind of thing is to forget about the AJAX stuff completely. AJAX calls will often hide any valuable error_reporting making it impossible to debug. You say you're only updating the last record, but while AJAX is in play, you have no idea what's being sent to your server-side script.
If you take a step back it will be easier out figure out what's going on. What I would do for testing purposes is change the signature of your <form> element to include a method and an action pointing to a test script:
<form class="form-horizontal" id="updateStaff-Form" method="post" action="test.php">
Also, add a standard submit button to the form:
<input type="submit" value="Submit Test">
And now create the test.php script containing just the following:
The idea then is to start building up that test script to do the database stuff. Build it up piece by piece. You can echo out messages, var_dump variables etc, and instead of everything being hidden behind an AJAX call, you'll see exactly what's going on.
Once you know it's all working correctly, you can then switch to making the request with AJAX - knowing that your scripts does what it's supposed to.
Not sure why you closed the question off if it's not yet resolved.
I think I've mentioned before that when coding your application, it makes sense to deal with it piece by piece, rather than trying to do everything at once. Currently, you're trying to do everything at once, which means it's not easy to track down where the problem lies.
What I normally do when coding this kind of thing is to forget about the AJAX stuff completely. AJAX calls will often hide any valuable error_reporting making it impossible to debug. You say you're only updating the last record, but while AJAX is in play, you have no idea what's being sent to your server-side script.
If you take a step back it will be easier out figure out what's going on. What I would do for testing purposes is change the signature of your <form> element to include a method and an action pointing to a test script:
<form class="form-horizontal" id="updateStaff-Form" method="post" action="test.php">
Also, add a standard submit button to the form:
<input type="submit" value="Submit Test">
And now create the test.php script containing just the following:
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
var_dump($_POST);
?>The idea then is to start building up that test script to do the database stuff. Build it up piece by piece. You can echo out messages, var_dump variables etc, and instead of everything being hidden behind an AJAX call, you'll see exactly what's going on.
Once you know it's all working correctly, you can then switch to making the request with AJAX - knowing that your scripts does what it's supposed to.
ASKER
Thank you sir. I'll do as instructed.
ASKER
This is the result:
Obviously, only 1 'userClass_id' is being sent. I checked the database table, '41' is the userClassId that was omitted.
Thanks for the advice.
array (size=9)
'user_id' => string '43' (length=2)
'initial' => string 'DEMO STAFF' (length=10)
'username' => string 'demo' (length=4)
'password' => string 'demo' (length=4)
'role' => string 'Form Master' (length=11)
'class_assigned' => string 'SS1A' (length=4)
'subject_taught' =>
array (size=2)
0 => string 'AGR' (length=3)
1 => string 'OFF' (length=3)
'class_taught' =>
array (size=2)
0 => string 'SS2A' (length=4)
1 => string 'SS1D' (length=4)
'userClass_id' => string '42' (length=2)Obviously, only 1 'userClass_id' is being sent. I checked the database table, '41' is the userClassId that was omitted.
Thanks for the advice.
Ahh. OK. I can see what's happening. You'll need to change your getUpdateForm slightly.
You can then remove the hidden input:
<input type="hidden" name="userClass_id" class="col-xs-12 col-sm-9" value="<?= $SubClassTaught->UserClass
Make those changes and run your test script again. Post up the response so I can check it - we'll then just need to make a slight change to your staff_update script, but we need to make sure the data is being sent correctly first.
<td>
<select name="subject_taught[<?= $SubClassTaught->UserClassId ?>]" id="subject_taught" class="form-control subject_taught">
<?php foreach ($subjects as $subject_taught): ?>
<option value="<?= $subject_taught['Subject_Code'] ?>" <?= ($SubClassTaught->SubjectTaught == $subject_taught['Subject_Code']) ? 'selected' : null ?>><?= $subject_taught['Subject_Name'] ?></option>
<?php endforeach; ?>
</select>
</td>
<td>
<select name="class_taught[<?= $SubClassTaught->UserClassId ?>]" id="class_taught" class="form-control class_taught">
<?php foreach ($classes as $class_taught): ?>
<option value="<?= $class_taught['Class_ID'] ?>" <?= ($SubClassTaught->ClassTaught == $class_taught['Class_ID']) ? 'selected' : null ?>><?= $class_taught['Class_Name'] ?></option>
<?php endforeach; ?>
</select>
</td>You can then remove the hidden input:
<input type="hidden" name="userClass_id" class="col-xs-12 col-sm-9" value="<?= $SubClassTaught->UserClass
Make those changes and run your test script again. Post up the response so I can check it - we'll then just need to make a slight change to your staff_update script, but we need to make sure the data is being sent correctly first.
ASKER
Here:
array (size=8)
'user_id' => string '45' (length=2)
'initial' => string 'DEMO 3' (length=6)
'username' => string 'demo3' (length=5)
'password' => string 'demo3' (length=5)
'role' => string 'Subject Teacher' (length=15)
'class_assigned' => string '' (length=0)
'subject_taught' =>
array (size=3)
45 => string 'ARA' (length=3)
46 => string 'ANH' (length=3)
47 => string 'AEW' (length=3)
'class_taught' =>
array (size=3)
45 => string 'SS1A' (length=4)
46 => string 'SS1B' (length=4)
47 => string 'SS1C' (length=4)
Perfect. Now you need to change the staff_update script (around lines 35-38):
foreach ($_POST['subject_taught'] as $userClassId => $subject):
$class = $_POST['class_taught'][$userClassId];
$result->execute();
endforeach;$userClassId = $_POST["userClass_id"];ASKER
Wow! Incredibly awesome. I tested it and all updated once.
Sir, you know this stuff to the core.
Words cannot express my gratitude.
Sir, you know this stuff to the core.
Words cannot express my gratitude.
ASKER
So, what about INSERT new row for subject_taught and class_taught? Will that be possible?
Yeah sure. You'd need 2 scripts - one jQuery script that sends the data to the server (class, subject and userId), and one server-side to insert into the DB.
The jQuery would look like something like:
The jQuery would look like something like:
$('#insertRow').click(function(e) {
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});require_once 'db.php';
$newClass = $db->prepare( "INSERT INTO UserClass (SubjectTaught, ClassTaught, UserId) VALUES (?, ?, ?)" );
$newClass->bind_param( "ssi", $_POST['subject'], $_POST['class'], $_POST['id'] );
$newClass->execute();
echo "Record Inserted";ASKER
Please look at the curly brackets:
$('#insertRow').click(func
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});
Should I close it this way ?:
$('#insertRow').click(func
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});
});
$('#insertRow').click(func
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});
Should I close it this way ?:
$('#insertRow').click(func
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});
});
ASKER
No. Wrong
ASKER
I've seen the omission.
Missing single quote
$('#insertRow').click(function(e) {
$.post( "insertSubClass.php", { subject: $('#newSubject').val(), class: $('#newClass').val(), id: $('[name=user_id]).val() }, function( response ) {
alert(response);
});
});
Missing single quote
Haha - well spotted ... I'm typing the code straight into the EE comments box so it will always need to be 'reviewed' as I'm not testing any of it :)
ASKER
Sir, I'm confused here:
How do I create these: newSubject and newClass? And where should they be placed?
{ subject: $('#newSubject').val(), class: $('#newClass').val()How do I create these: newSubject and newClass? And where should they be placed?
They would need to be the inputs that your user will type into when they add a new Row. I'm assuming that to add a new row, the user has to enter the details somehow - either with <input> or <select> elements. It'll be up to you to show these when your user clicks to add a new Row.
<input type="text" id="newSubject">
<input type="text" id="newClass">
or
<select id="newSibject">
<option value="...">...</option>
</select>
<input type="text" id="newSubject">
<input type="text" id="newClass">
or
<select id="newSibject">
<option value="...">...</option>
</select>
ASKER
When I use the following, I'm able to remove row (tr) but not the data in database.
Open in new window
This doesn't remove row at all.
Open in new window
Help me take a look at getUpdateForm.php above. Do I really need the hidden button userClass_id ?
More so, is removeRow data-id properly setup?
Because, staff_update.php (above) is not working well. When I try to update multiple rows in the table section of the modal, only the last row gets updated, others remain the same.
Thank you