Well I want to subtract the value in database and just try any method but it still fail.I don't know if the codes in query in below is working. Please explain it to me fluently because I'm hard to understanding the logic of codes. I really appreciate and I'll try to do my best Thank you!
borrow.php
<form method="post" action="borrow_save.php">
<div class="span8">
<div class="alert alert-success"><strong>Select Book</strong></div>
<table cellpadding="0" cellspacing="0" border="0" class="table" id="example">
<thead>
<tr>
<th>Acc No.</th>
<th>Book title</th>
<th>Category</th>
<th>Author</th>
<th>Publisher name</th>
<th>status</th>
<th>Add</th>
</tr>
</thead>
<tbody>
<?php $user_query=mysqli_query($dbcon,"select * from book where status != 'Archive' ")or die(mysqli_error());
while($row=mysqli_fetch_array($user_query)){
$id=$row['book_id'];
$copy=$row['book_copies'];
$cn=count($id);
for($i=0; $i < $cn; $i++)
if($copy > 0){ ?>
<tr class="del<?php echo $id ?>">
<td><?php echo $row['book_id']; ?></td>
<td><?php echo $row['book_title']; ?></td>
<td><?php echo $row ['catalog']; ?> </td>
<td><?php echo $row['author']; ?> </td>
<td><?php echo $row['publisher_name']; ?></td>
<td width=""><?php echo $row['status']; ?></td>
<?php include('toolttip_edit_delete.php'); ?>
<td width="20">
<input id="" class="uniform_on" name="selector[]" type="checkbox" value="<?php echo $id; ?>">
</td>
</tr>
<?php }
?>
<?php } ?>
</tbody>
</table>
</form>
<script>
$(".uniform_on").change(function(){
var max= 3;
if( $(".uniform_on:checked").length == max ){
$(".uniform_on").attr('disabled', 'disabled');
alert('3 Books are allowed per borrow');
$(".uniform_on:checked").removeAttr('disabled');
}else{
$(".uniform_on").removeAttr('disabled');
}
});
borrow_save.php
<?php
$id=$_POST['selector'];
$member_id = $_POST['member_id'];
$due_date = $_POST['due_date'];
if ($id == '' ){
header("location: borrow.php");
}else{
mysqli_query($dbcon,"insert into borrow (member_id,date_borrow,due_date) values ('$member_id',NOW(),'$due_date')")or die(mysqli_error($dbcon));
$borrow_id = $row['borrow_id'];
$borrow_id=$dbcon->insert_id;
mysqli_query($dbcon,"UPDATE book SET book_copies = book_copies - 1 where book_id='$book_id'"); <!- this is the query that I don't know if it's work -->
$N = count($id);
for($i=0; $i < $N; $i++)
{
mysqli_query($dbcon,"insert borrowdetails (book_id,borrow_id,borrow_status) values('$id[$i]','$borrow_id','pending')")or die(mysqli_error($dbcon));
}
}
?>
You are already inserting in borrowdetails on a per book_id basis within your for-loop, so the easiest solution would be to move that update statement to that loop too:
for($i=0; $i<$N; $i++) {
$stmt = mysqli_prepare($dbcon,"UPDATE book SET book_copies = book_copies - 1 where book_id=?") or die(mysqli_error($dbcon));
mysqli_stmt_bind_param($stmt, "i", $id[$i]);
mysqli_stmt_execute($stmt) or die(mysqli_error($dbcon));
// Your insert-query (still NEEDS protection against SQL-injection!!!)
mysqli_query($dbcon,"insert borrowdetails (book_id,borrow_id,borrow_status) values('$id[$i]','$borrow_id','pending')")or die(mysqli_error($dbcon));
}