|
|
Featured White Papers
Sponsored Links
-
Slow Apps? Overloaded DBMS? - FREE Download
FREE Download Feb 14 - March 7th Eliminate bottlenecks & maximize performance ScaleOut StateServer®
Event Reservation Problem
Submitted by prasath_arjunan on Mon, 08/31/2009 - 06:27.
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 or suggest if anybody have any Idea.
Thanks in Advance,
Prasath
About Us | Advertise | Contact Us | Terms of Service/Privacy
Copyright, 2006-2010 Infoworld, Inc. All rights reserved.
getInt("Total")
1. Are you sure that ResultSet.getInt using "total" as the argument, is actually working correctly? Try changing that to a getInt(1) call.
2. Query-3 conditionals are incorrect/incomplete:
N1 <= R1 <= N2; OR
N1 <= R2 <= N2
where
N1: New booking, start time
N2: New booking, end time
R1: Reserved, start time
R2: Reserved, end time
(a) It does not account for the case:
R1 <= N1; AND
N2 <= R2
(e.g., an existing reservation from 0000 through 2359)
(b) Should your reservation system allow the case that a reservation starts at the same time that another reservation ends (e.g., accept 1000-1030 and 1030-1100), or not (e.g., if the first reservation is 1000-1030, the next one must start no earlier than 1031). If it's the second case, then query condition (R2 <= N2) must be changed to (R2 < N2).
Yes I am sure that
Yes I am sure that ResultSet.getInt using "total" as the argument, its working fine.
How can I get the R1 and R2?
My reservation system should allow both "1000-1030" and "1030-1100" on a particular date(2009-08-09).
design
I think you are complicating the problem. You can simplify your logic at several levels.
1) Clean up your if statements :
else if((queryCount1==0 && queryCount2==0 && queryCount3==0)) // why this check if the else of it is the exact same result?
{
isExists="Not Booked";
}
else
{
isExists="Not Booked";
}
(just as an example)
2) Why are you using 3 queries?
3) what is that?
if(queryCount1>=1 || queryCount2>=1 || queryCount3>1 )
// queryCount3 > 1, why ?
I also don't see any trace of city, state, country in your table.
I got the solution from the
I got the solution from the following query, i forgot to post the city, state, country in my table.
SELECT count(*) as total FROM events_tbl WHERE
(starttime < time_format('"+endTime+"','%T'))
and
(endtime > time_format('"+startTime+"','%T'))
and country_id='"+countryID+"' and state_id='"+stateID+"' and city_id='"+cityID+"' and edate='"+eventDate+"' and decide=1";
Thanks & Regards,
Prasath
Great Post
I like this article. This is called a great article. I am new here. I like your site too. This is pretty awesome. i found some useful info here. anyways thanks for sharing with us. I am looking foreword your next post. Thanks. I’m just going to shear this site all my friend’s and i hope they live this site.
Post new comment