Ragavi Posted March 4, 2020 Share Posted March 4, 2020 (edited) Hello Friends, I am trying to execute Time slot booking for an application. - When user selects a date - In a calendar I have monthly calendar dates and (5) time slot for booking. (9-10, 10-11, 11-12, 12-1, 1-2) Each time slot can be booked by 2 users User 1 selected a date (2nd March)and selected a time slot (10:00 - 11:00 AM). User 2 selected a date (2nd March)and selected a time slot (10:00 - 11:00 AM). When User 3 try to book a slot (10:00 - 11:00 AM) on 2nd March - I would like to disable or display "Slot Not Available" for that Time slot. Please find the script below. Your help is really appreciated. <?php include('database.php'); $fname=""; $lname=""; $email=""; $phone=""; $date=""; if(isset($_POST['fname'])){ $fname=$_POST['fname'];} if(isset($_POST['lname'])){$lname=$_POST['lname'];} if(isset($_POST['email'])){$email=$_POST['email'];} if(isset($_POST['phone'])){$phone=$_POST['phone'];} if(isset($_POST['date'])){$date=$_POST['date'];} else $date=date("m-d-Y") $result=mysqli_query($conn,"SELECT exam_time,count()from test_booking_confirm where DATEDIFF('exam_date',DATE_FORMAT('"+$date+"','%m-%d-%Y'))=0 group by exam_date,exam_time having count()>1"); $slots=array(); $i=0; if (mysqli_num_rows($result) != 0) { while($row = mysqli_fetch_assoc($result)) { $slots[$i]=$row["exam_time"]; $i++; } } ?> <!doctype html> <html> <head> <title></title> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <link rel="stylesheet" href="css/style.css" /> <link rel="stylesheet" href="css/bootstrap.min.css" /> <!-------date picker-------> <link rel="stylesheet" href="css/jquery-ui.css" /> <script src="js/jquery.js"></script> <script src="js/jquery-ui.js"></script> <script> $(document).ready(function(){ $("#datepicker").datepicker({ beforeShowDay: function(date) { var day=date.getDay(); if(day==2) { return [false]; } else { return [true]; } } }); }); </script> <script> $(document).ready(function() { $('#datepicker').datepicker(); $('#datepicker').datepicker("show"); }); </script> <script> function setToday() { var n = new Date(); y = n.getFullYear(); m = n.getMonth() + 1; d = n.getDate(); var x=m + "/" + d + "/" + y; document.getElementById("datepicker").value = x; } function datechange(vv) { document.myform.action="test_booking.php"; document.myform.submit(); } </script> </head> <body OnLoad="document.myform.fname.focus();setToday()"> <div class="container register"> <div class="row"> <div class="col-md-3 training_bk register-left"> <img src="images/ESStechlogo.png" alt=""/> <h3>Welcome</h3> <p>Lorem Ipsum is simply dummy text of the printing and typesetting industry.</p> </div> <div class="col-md-9 register-right"> <form method="post" action="test_booking_review.php" name="myform"> <div class="tab-content" id="myTabContent"> <div class="tab-pane fade show active" id="home" role="tabpanel" aria-labelledby="home-tab"> <h3 class="register-heading">Book your Training</h3> <div class="row register-form"> <div class="col-md-6"> <div class="form-group"> <input type="text" autofocus name="fname" required class="form-control" placeholder="First Name*" /> </div> <div class="form-group"> <input type="text" name="lname" required class="form-control" placeholder="Last Name*" /> </div> <h5>Your Preferred Slot</h5> <div class="form-group"> <!--<div id="datepicker" required name="date" ></div>--> <input type="text" id="datepicker" required name="date" placeholder="mm/dd/yyyy" class="form-control" onChange ="datechange(this.value)"> </div> </div> <div class="col-md-6"> <div class="form-group"> <input type="email" name="email" required class="form-control" placeholder="Email*" /> </div> <div class="form-group"> <input type="number" name="phone" required class="form-control" placeholder="Phone*" /> </div> <!--<div class="form-group"> <select class="form-control"> <option class="hidden" selected disabled>City</option> <option>option</option> <option>option</option> <option>option</option> </select> </div>--> <div class="form-group"> <div class="maxl"> <?php if (!in_array("08:00AM TO 09:00AM", $slots)) { ?> <label class="radio inline"> <input type="radio" name="time" value="08:00AM TO 09:00AM" checked> <span>08:00AM TO 09:00AM (Available)</span> </label> <?php } if (!in_array("09:00AM TO 10:00AM", $slots)) { ?> <label class="radio inline"> <input type="radio" name="time" value="09:00AM TO 10:00AM"> <span>09:00AM TO 10:00AM (Available)</span> </label> <?php } if (!in_array("10:00AM TO 11:00AM", $slots)) { ?> <label class="radio inline"> <input type="radio" name="time" value="10:00AM TO 11:00AM"> <span>10:00AM TO 11:00AM (Available)</span> </label> <?php } if (!in_array("11:00AM TO 12:00PM", $slots)) { ?> <label class="radio inline"> <input type="radio" name="time" value="11:00AM TO 12:00PM"> <span>11:00AM TO 12:00PM (Available)</span> </label> <?php } if (!in_array("12:00PM TO 01:00PM", $slots)) { ?> <label class="radio inline"> <input type="radio" name="time" value="12:00PM TO 01:00PM"> <span>12:00PM TO 01:00PM (Available)</span> </label> <?php } ?> </div> </div> <input type="submit" class="btnRegister" value="Continue"/> </div> </div> </div> </div> </form> </div> </div> </div> </body> Edited March 4, 2020 by Barand Added code tags Quote Link to comment Share on other sites More sharing options...
Barand Posted March 4, 2020 Share Posted March 4, 2020 (edited) In future, use the <> button in the toolbar when posting code. (I've done it for you this time) In your query SELECT exam_time ,count() FROM test_booking_confirm WHERE DATEDIFF('exam_date',DATE_FORMAT('"+$date+"','%m-%d-%Y'))=0 GROUP BY exam_date,exam_time HAVING count()>1 the WHERE clause is FUBAR. when storing or working with dates in a db the format should be yyyy-mm-dd (DATE type) in PHP, the concatenation operator is a "." and not a "+" column names should not be in single quotes user-provided data should not be put directly into the query, used prepared statements Assuming the exam_date is the correct DATE type and format, make sure $date is the correct format EG $date = date('Y-m-d'); $stmt = $conn->prepare("SELECT exam_time FROM test_booking_confirm WHERE exam_date = ? GROUP BY exam_date,exam_time HAVING count(*)>1 "); $stmt->bind_param('s', $date); $stmt->execute(); $stmt->bind_result($exam_time); $slots=array(); while($stmt->fetch { $slots[] = $exam_time; } But, personally, I'd look for slots with a count < 2 and just display those available. Edited March 4, 2020 by Barand Quote Link to comment 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.