So I have an update form, the update part works just fine and within that update there is an insert/delete query as well. The insert/delete query are for the dates.
For example if the user updates the date details lets say from may-june to may-july.
I have tables representing the months that would have their new data entered (Name, Status). The code works but only if I click the update button for a second time.
This is my AJAX:
$(document).ready(function(){
$(document).on('click', '.edit_data', function(){
var cname = $(this).attr("id");
$.ajax({
url:"fetch.php",
method:"POST",
data:{cname:cname},
dataType:"json",
success:function(data){
$('#name').val(data.Name);
$('#livingcondition').val(data.LivingCondition);
$('#oattype').val(data.OatType);
$('#quantity').val(data.NoOfOats);
$('#start').val(data.StartMem);
$('#end').val(data.EndMem);
$('#insert').val("Update");
$('#add_data_Modal').modal('show');
}
});
});
$('#insert_form').on("submit", function(event){
event.preventDefault();
$.ajax({
url:"insert.php",
method:"POST",
data:$('#insert_form').serialize(),
beforeSend:function(){
$('#insert').val("Inserting");
},
success:function(data){
$('#insert_form')[0].reset();
$('#add_data_Modal').modal('hide');
$('#client_table').html(data);
}
});
});
This is my fetch.php:
<?php
$connect = mysqli_connect("localhost", "root", "", "oatdistribution");
if(isset($_POST["cname"]))
{
$query = "SELECT * FROM oatdis WHERE Name = '".$_POST["cname"]."'";
$result = mysqli_query($connect, $query);
$row = mysqli_fetch_array($result);
echo json_encode($row);
}
?>
this is my insert.php:
<?php
$connect = mysqli_connect("localhost", "root", "", "oatdistribution");
if(!empty($_POST))
{
$output = '';
$message = '';
$name = mysqli_real_escape_string($connect, $_POST["name"]);
$livingcondition = mysqli_real_escape_string($connect, $_POST["livingcondition"]);
$oattype = mysqli_real_escape_string($connect, $_POST["oattype"]);
$quantity = mysqli_real_escape_string($connect, $_POST["quantity"]);
$start = mysqli_real_escape_string($connect, $_POST["start"]);
$end = mysqli_real_escape_string($connect, $_POST["end"]);
$query = "
UPDATE oatdis
SET
LivingCondition='$livingcondition',
OatType='$oattype',
NoOfOats = '$quantity',
StartMem = '$start',
EndMem = '$end'
WHERE Name='$name'";
$mem = "SELECT MONTHNAME(DATE_ADD(StartMem,INTERVAL i MONTH)) AS Months FROM oatdis CROSS JOIN counter WHERE DATE_ADD(StartMem,INTERVAL i MONTH)<=EndMem AND Name = '$name'";
$memRes = mysqli_query($connect,$mem);
$data = array();
foreach ($memRes as $row) {
$data[] = $row['Months'];
}
if(in_array("January", $data)){
$mysql=mysqli_query($connect, "SELECT * FROM January WHERE Name = '.$name'");
if(mysqli_num_rows($mysql)>0){
$ins = mysqli_query($connect,"UPDATE january SET Name ='.$name.' WHERE Name = '.$name'");
}
else{
$ins = mysqli_query($connect,"INSERT INTO january VALUES('.$name.','Not Delivered')");
}
}
if(in_array("February", $data)){
$mysql=mysqli_query($connect, "SELECT * FROM february WHERE Name = '.$name'");
if(mysqli_num_rows($mysql)>0){
$ins = mysqli_query($connect,"UPDATE february SET Name ='.$name.' WHERE Name = '.$name'");
}
else{
$ins = mysqli_query($connect,"INSERT INTO february VALUES('.$name.','Not Delivered')");
}
}
$message = 'Data Updated';
if(mysqli_query($connect, $query))
{
echo "<script> alert('$message');</script>";
$select_query = "SELECT * FROM oatdis";
$result = mysqli_query($connect, $select_query);
$output .= '
<table class="table table-bordered">
<thead>
<tr style="background-color: black; color: white;">
<th width="70%" style="border-width: 2px; border-color: white;">Employee Name</th>
<th width="15%" style="border-width: 2px; border-color: white;">Edit</th>
<th width="15%" style="border-width: 2px; border-color: white;">View</th>
</tr>
</thead>
<tbody>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td style="font-weight: bold; background-color: #DAD2D2; border-color: white; border-width: 2px;">' . $row["Name"] . '</td>
<td style="text-align: center; background-color: #EEEEEE; border-color: white; border-width: 2px;"><input type="button" name="edit" value="Edit" id="'.$row["Name"] .'" class="btn btn-info btn-xs edit_data" style=" background-color: #FFF6AF; color: black; width: 50px; font-weight: bold; " /></td>
<td style="text-align: center; background-color: #DAD2D2; border-color: white; border-width: 2px;"><input type="button" name="view" value="view" id="' . $row["Name"] . '" class="btn btn-info btn-xs view_data" style=" background-color: #FFF6AF; color: black; width: 50px; font-weight: bold; "/></td>
</tr>
';
}
$output .= '</tbody></table>';
}
echo $output;
}
?>
What I have tried:
I'm not sure what makes it this way, no error pops up.