I have developed one web application by PHP & MySQL. I got an issue part of room allocation module.
In Room Allocation module, User can choose multiple check box (Time slot) depends on their need. In back end, I Collect those time and save into database.
Some times, same data inserted into database twice. It's only occurred some times. Mostly I got proper Output.
My Questions are :
- Why It's happening only in some times?
- Where duplicate value created?
- how it happen?
- How to rectify this problem!!
Please some one help me to avoid this problem.!!!
My Insert Coding:
$admin = $_POST['admin'];
$roomno = $_POST['str_roomno']; //unique No
$currentdate = $_POST['str_currentDate'];
$employeeID = $_POST['str_empID']; //current user ID
$list_id = $_POST['list']; //collect list of time slot from previous page
$total_checkedRows = count($list_id); //counting the total slot from previous page
for($i = 0; $i < $total_checkedRows; $i++)
{
$fromTime = $_POST['time_fromTime'.$list_id[$i]];
$endTime = $_POST['time_endTime'.$list_id[$i]];
$status = $_POST['str_status'.$list_id[$i]];
$reason = $_POST['str_comment'] ;
$statusCheck = "occupied";
if(isset($_POST['id_bookedID'.$list_id[$i]])){
$id_lineID = $_POST['id_bookedID'.$list_id[$i]];
}
if($status == 1)
{
$insertSQL = sprintf("INSERT INTO hrms_room_calendar (room_id, booking_date, from_time, to_time, employee_id , booking_status, reason) VALUES (%s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($roomno, "int"),
GetSQLValueString($currentdate, "date"),
GetSQLValueString($fromTime, "text"),
GetSQLValueString($endTime, "text"),
GetSQLValueString($employeeID, "int"),
GetSQLValueString($statusCheck, "text"),
GetSQLValueString($reason, "text"));
$Result1 = mysql_query($insertSQL) or die(mysql_error());
}
else if ($status == 2)
{
$updateQuery = sprintf("update hrms_room_calendar set booking_status = %s , employee_id = %s, reason = %s where booking_id = %s",
GetSQLValueString($statusCheck, "text"),
GetSQLValueString($employeeID, "int"),
GetSQLValueString($reason , "text"),
GetSQLValueString($id_lineID, "int"));
$Result1 = mysql_query($updateQuery) or die(mysql_error());
}
Check Box Coding:
<form action="BookOrCancel_room.php" method="POST" name="from_roomdetailsfillter" id="from_roomdetailsfillter" onsubmit="return mycommendfunction(1);" >
<input type="hidden" value="<?php echo $_GET['roomid'] ; ?>" name="str_roomno" />
<input type="hidden" value="<?php echo $_GET['calender'] ; ?>" name="str_currentDate" />
<input type="hidden" value="<?php echo $LoginEmpID ; ?>" name="str_empID" />
<input type="hidden" name="str_comment" id="id_commend" /> <!--value coming from js -->
<input type="hidden" value="<?php echo $admin; ?>" name="admin" />
<?php
foreach($range as $timeRange)
{
$count ++;
$fromTime = $timeRange;
$add_time=strtotime($fromTime)+60;
$fromTime = date("h:i A", $add_time); //Now starting time + 1 Min
$Endtime_in_db_24_hour_format = $dbendTime; //Detractly retireve from database
echo "<input type='text' name='time_fromTime$count' value='$Fromtime_in_24_hour_format' style = 'display: none;'/>
<input type='text' name='time_endTime$count' value='$Endtime_in_24_hour_format' style = 'display: none;'/> ";
$link = "BookOrCancel_room.php?roomno=".$roomid."&Currentdate=". $date_Entered. "&employeeID=" . $LoginEmpID . "&fromTime=". $Fromtime_in_24_hour_format."&endTime=".$Endtime_in_24_hour_format;
if($total_roomdetails > 0)
{ //check initially whether current date atleast once present in database
//check room already booked or not
$query_EmpName = sprintf("SELECT * FROM hrms_room_calendar WHERE booking_date = %s and room_id = %s", GetSQLValueString($date_Entered, "date"), GetSQLValueString($roomid, "int"));
$roomdetails = mysql_query($query_EmpName) or die(mysql_error());
while($retriveBookedStatus = mysql_fetch_array($roomdetails))
{
$db_from_time = strtotime($retriveBookedStatus['from_time']);
$local_from_time = strtotime($fromTime);
if($db_from_time == $local_from_time)
{
$cancelLink = $link."&id=".$retriveBookedStatus['booking_id']."&admin=".$admin;
echo "<input type='hidden' name='id_bookedID$count' value=" . $retriveBookedStatus['booking_id']. ">";
$str_reason = $retriveBookedStatus['reason'];
$str_cancel_reason = $retriveBookedStatus['cancelled_remarks'];
//Display Reason {.............}
//Display Employee Name {.............}
if(($status == 2) and ($total_accessdetails_Recordset > 0) and ($date_exited == 0))
{ //canceled it can book by others
$status = 0;
//echo "<td style = 'display: none;'><a href=".$cancelLink."&status=2" . " onclick='return confirmCancel();'><input type='button' name='Booked' value='Book'></a></td>";
echo "<input type='hidden' value=2 name='str_status$count' />";
echo "<td><input type='checkbox' name='list[]' value=$count id='checkboxbook' /> <td>";
}
} //if($db_from_time == $local_from_time)
}//while
if($flag != 1)
{
$flag = 0;
echo "<tr height='25' bgcolor='#FFFFFF'>
<td>";
echo $fromTime . " - ".$endTime ;
//Check Whether ending time exited dbEnd Time
echo TimeExited($Endtime_in_24_hour_format, $Endtime_in_db_24_hour_format );
echo "</td>";
echo "<td title='Free'> Available </td>";
echo "<td align='center'> - </td>";
echo "<td align='center'> - </td>";
if(($total_accessdetails_Recordset > 0) and ($date_exited == 0)){
//That particular time and date didnt present in db that is also should not booked
echo "<td style = 'display: none;'><a href=".$link."&status=1" . " target='_blank'><input type='button' name='Booked' value='Book'></a></td>";
echo "<input type='hidden' value=1 name='str_status$count' />";
echo "<td><input type='checkbox' name='list[]' value=$count id='checkboxbook' disabled/> <td>";
} // if($total_accessdetails_Recordset > 0)
} //if($flag != 1)
else
{
$flag = 0;
}
} // if($total_roomdetails > 0)
else
{
echo "<tr height='25' bgcolor='#FFFFFF'>
<td>";
echo $fromTime . " - ".$endTime ;
echo "</td>";
echo "<td title='Free'>Available </td>";
echo "<td align='center'> - </td>";
echo "<td align='center'> - </td>";
if(($total_accessdetails_Recordset > 0) and ($date_exited == 0)){ //while loading time slot should not booked
echo "<td style = 'display: none;'><a href=".$link."&status=1" . " target='_blank'><input type='button' name='Booked' value='Book' ></a></td>";
echo "<input type='hidden' value=1 name='str_status$count' />";
echo "<td><input type='checkbox' name='list[]' value=$count id='checkboxbook' /> <td>";
}//($total_accessdetails_Recordset > 0)
} //else
echo "</tr>";
} // foreach
?>
</form>
