Dear Users.

I am doing a online Conference Hall booking system with JSP and MySql. I have the following problem when I (as Admin) am going to approve the some event on Same date(2009-08-29).

My application is to reserve a room by entering start time, end time and date before reserving I need to check the availability in the database.
When i'm requesting reserving by entering(selecting) startime as 17.30 and end time as 20.00 on a particular date(2009-08-29),
if they had already reserved in Database(where decide=1 is already reserved and 0 is not yet reserved) with 17.30 to 20.00 on the same date it should not allow me to reserve this event and i'm requesting reserving another event with startime as 07.30 and end time as 10.00 on the same date(2009-08-29) it should allow me to reserve the event as reserved(i.e decide=1) and so on.

I dont know how to solve this problem but i tried with some way it doesn't give the exact solution,


Mysql Table Structure:

event_id(int) edate(Date) starttime(Time) endtime(Ttime) decide(Char)

38 2009-08-29 12:30:00 15:00:00 1
30 2009-08-29 10:00:00 12:30:00 1
39 2009-08-29 11:30:00 15:30:00 1
40 2009-08-29 15:00:00 20:00:00 1
41 2009-08-29 10:30:00 15:30:00 1
42 2009-08-29 20:00:00 22:00:00 0
43 2009-08-29 12:30:00 15:00:00 0
44 2009-08-29 13:00:00 16:30:00 0

JSP Code:

<%@ page language="java" contentType="text/html; charset=utf-8" import="java.sql.Connection,java.sql.DriverManager ,java.sql.PreparedStatement,java.sql.ResultSet" pageEncoding="ISO-8859-1"%>
<%
/***** check Starttime and Endtime before Approving the event whether already booked or not *****/

Class.forName("com.mysql.jdbc.Driver");
Connection con=null;
String serverName = "localhost";
String username = "root";
String password = "";
String mydatabase = "conference";
String url =
(new StringBuilder()).append("jdbc:mysql://").append(serverName).append("/").append(mydatabase).toString();
con = DriverManager.getConnection(url, username, password);

int queryCount1=0, queryCount2=0;
String isExists="";
ResultSet rs1=null, rs2=null, rs3=null;
PreparedStatement pst1=null, pst2=null,pst3=null;
String startTime="17:30:00", endTime="20:00:00", eventDate="2009-08-29", query1="", query2="", query3="";

query1="SELECT count(*) as total FROM events_tbl WHERE (((starttime > time_format('"+startTime+"','%T')) AND (starttime < time_format('"+endTime+"','%T'))) OR ((endtime > time_format('"+startTime+"','%T')) AND (endtime < time_format('"+endTime+"','%T')))) and country_id='"+countryID+"' and state_id='"+stateID+"' and city_id='"+cityID+"' and edate='"+eventDate+"' and decide=1";*

*query2="SELECT count(*) as total FROM events_tbl WHERE (((starttime = time_format('"+startTime+"','%T')) AND (endtime = time_format('"+endTime+"','%T')))) and country_id='"+countryID+"' and state_id='"+stateID+"' and city_id='"+cityID+"' and edate='"+eventDate+"' and decide=1";

query3="SELECT count(*) as total FROM events_tbl WHERE (((starttime >= time_format('"+startTime+"','%T')) AND (starttime <= time_format('"+endTime+"','%T'))) OR ((endtime >= time_format('"+startTime+"','%T')) AND (endtime <= time_format('"+endTime+"','%T')))) and country_id='"+countryID+"' and state_id='"+stateID+"' and city_id='"+cityID+"' and edate='"+eventDate+"' and decide=1";

pst1=con.prepareStatement(query1);

rs1=pst1.executeQuery();
if(rs1.next())
{
queryCount1=rs1.getInt("total");
}
pst1.close();
rs1.close();
out.println("queryCount1:-"+queryCount1+"<br><br>");

pst2=con.prepareStatement(query2);
rs2=pst2.executeQuery();
if(rs2.next())
{
queryCount2=rs2.getInt("total");
}
pst2.close();
rs2.close();
out.println("queryCount2:-"+queryCount2+"<br><br>");

pst3=con.prepareStatement(query3);
rs3=pst3.executeQuery();
if(rs3.next())
{
queryCount3=rs3.getInt("total");
}
pst3.close();
rs3.close();
out.println("queryCount3:="+queryCount3+"<br><br>" );

if(queryCount1>=1 || queryCount2>=1 || queryCount3>1 )
{
isExists="Already Booked";
}
else if((queryCount1==0 && queryCount2==0 && queryCount3>1))//here is the problem i am facing
{
isExists="Already Booked";
}
else if((queryCount1==0 && queryCount2==0 && queryCount3==0))
{
isExists="Not Booked";
}
else
{
isExists="Not Booked";
}
out.println("isExists:="+isExists);

// here goes to update query...
%>



when i execute the above code with startime as 17.30 and end time as 20.00 on a particular date(2009-08-29) it gives me "Not Booked" Output, but when I execute the above code with startime as 07.30 and end time as 10.00 on the same date(2009-08-29) it give me "Not Booked" Output.

I don't know where I did mistake, please let me know if any error or suggest if anybody have any Idea.


Thanks in Advance,

Prasath