SQL query help

[mattastic]

Hi Folks,

I have this query:

SELECT * FROM tblevent, tblroombooking WHERE (tblevent.startdate >= '2006-07-18 09:00:00' AND tblevent.enddate <= '2006-07-18 17:00:00') AND tblroombooking.eventID=tblevent.eventID ORDER BY startdate

It selects events fine that only last for the given day, but events with startdates and endates on different days, it doesnt work, can anyone help?

Could i use the BETWEEN sql function?

thankyou

[cbj4074]

Why don't you try using BETWEEN and let us know how it works. If it doesn't work, tell us why it doesn't, and then we'll explore some alternatives...

[bpat1434]

Between will work just fine..... 😉

SELECT *
FROM tblevent, tblroombooking
WHERE (tblevent.startdate BETWEEN '2006-07-18 09:00:00' AND '2006-07-18 17:00:00')
    AND tblroombooking.eventID = tblevent.eventID
ORDER BY tblevent.startdate

[mattastic]

Thanks for your replies.

The problem I have is when event start dates and endates do not occur on the day, but span across the day, the query doesnt seem to pick them up. Can anyone help?

Thankyou

[bogu]

If u want to select the events that start that day or ended that day u should write it this way ...

SELECT * 
FROM tblevent, tblroombooking 
WHERE 
	(
		tblevent.startdate BETWEEN '2006-07-18 09:00:00' AND '2006-07-18 17:00:00'
		 OR 
		tblevent.enddate BETWEEN '2006-07-18 09:00:00' AND '2006-07-18 17:00:00'
	)
	AND
	tblroombooking.eventID=tblevent.eventID 
ORDER BY startdate

[tomhath]

What bogu said, except make sure you provide the proper end time (probably on 2006-07-19 rather than 2006-07-18)

[mattastic]

Thanks for your replies.

That works but not quite right. For example if I have a 2 events with the followiing startdates:

Event 1: start date = 2006-07-16 09:00:00 enddate = 2006-07-26 13:30:00
Event 1: start date = 2006-07-16 09:00:00 enddate = 2006-07-16 13:45:00

If the date was 2006-07-16, both events would need to show, can anyone tell me how I can do this?

Thankyou

[bpat1434]

SELECT * FROM table
WHERE startdate LIKE ('2006-07-16%');

Something like that?

[tsinka]

Hi,

what about this query:

WHERE (tblevent.startdate <= '2006-07-18 17:00:00' AND tblevent.enddate >= '2006-07-18 09:00:00') 
AND tblroombooking.eventID=tblevent.eventID ORDER BY startdate

That should select all events that start before or at 17:00 of the selected date and end after or at 09:00 of the selected date.

Regards,
Thomas

[mattastic]

Hi Folks,

Im still having real trouble understanding this. My sql is poor. Can someone please help?

Here are my tables:

tblroombooking
BookingID = 48
eventID = 98
roomID = 9

BookingID = 50
eventID = 99
roomID = 8

tblevent
eventID = 99

startdate = 2006-07-17 09:00:00

enddate =2006-07-17 13:45:00
eventtitle = lll
eventdescription = lll

eventID = 98

startdate = 2006-07-17 09:00:00

enddate = 2006-07-26 13:30:00
eventtitle = rrr
eventdescription = rrr

This is my query:

SELECT *
FROM tblevent, tblroombooking
WHERE (
tblevent.startdate <= '2006-07-17 09:00:00'
AND tblevent.enddate >= '2006-07-17 17:00:00'
)
OR (
tblevent.startdate
BETWEEN '2006-07-17 09:00:00'
AND '2006-07-17 17:00:00'
OR tblevent.enddate
BETWEEN '2006-07-17 09:00:00'
AND '2006-07-17 17:00:00'
)
AND tblroombooking.eventID = tblevent.eventID
ORDER BY startdate

But it repeats the event that spans over a few days:

eventID = 98
startdate =2006-07-17 09:00:00

enddate =2006-07-26 13:30:00
eventtitle = rr
eventdescription = rrr
BookingID = 49
eventID = 98
roomID = 9

eventID = 99
startdate =2006-07-17 09:00:00
enddate =2006-07-17 13:45:00
eventtitle =lll
eventdescription = lll

BookingID 50
eventID 99
roomID 8

eventID = 98
startdate =2006-07-17 09:00:00

enddate =2006-07-26 13:30:00
eventtitle = rr
eventdescription = rrr
BookingID = 50
eventID = 99
roomID = 8

Can anyone help me please?

thankyou

[tsinka]

Hi,

compare the where clause in my last post with the first part of the where clause in your last post. There's a major difference.

     SELECT *
      FROM tblevent e
INNER JOIN tblroombooking r
        ON e.eventID = r.eventID
     WHERE e.startdate <= '2006-07-17 17:00:00'
       AND e.enddate >= '2006-07-17 09:00:00'

That should get all events that "touch" the given date in any way.

Is it possible to book more than one room for one event? In that case you may get an event more than once. A solution is to add

GROUP BY e.eventID

to the query.

Thomas

[tsinka]

I reread your last post. The event with ID 98 exists twice but with different rooms. So someone booked two rooms for that event.

Edit: No, there's an error because the event ids don't match in one of the records.

Thomas

[mattastic]

Hi thomas,

Thanks for your reply.

I think I understand now, I dont want users to book multiple events in the same room at the same time.

I have an addevent page, do you know where would be best to check to see if the room is booked up?

What would the query need to look like?

Tankyou again

[tsinka]

Look at the third output block you posted:

eventID = 98
startdate =2006-07-17 09:00:00
enddate =2006-07-26 13:30:00
eventtitle = rr
eventdescription = rrr
BookingID = 50
eventID = 99
roomID = 8

You'll see that the second eventID line differs from the first eventID line. This is something that shouldn't have happened because you had tblroombooking.eventID=tblevent.eventID . So something was wrong with the SQL query that returned that data (on the first look I thought that someone booked to different rooms for one event).

To check if a room has already been booked you can just use the exact same query as above with one additional condition:

Example (check if room with ID 10 is free 2006-07-17 between 10:00 and 13:00):

     SELECT *
      FROM tblevent e
INNER JOIN tblroombooking r
        ON e.eventID = r.eventID
     WHERE e.startdate <= '2006-07-17 13:00:00'
       AND e.enddate >= '2006-07-17 10:00:00'
       AND r.roomID=10

If that query returns any row then the room has already been booked. The query returns the info about the event that is already booked for the given date/time.

If the query returns more than one row then the room has been booked more than one time.

Thomas

[mattastic]

Thats great thanks

I'm displaying the events details in a table where I loop through the day at 15 minute intervals.

I can display the events for the day easy enough by using this code below.

Do you know how I can alter the code, to display details for events that run across several days.

I have an example here, nothing is populating the table cells at the moment, but you can get a better idea of what I'm trying to do:

http://w00t.cutpage.com/index.php?page=viewday&date=1153119600

if (

	(date('H:i:s', $event['startdate']) <= $todaysdate 
	&& date('H:i:s', $event['enddate']) >= $todaysdate)

	&& $room['roomID'] == $event['roomID']){
	echo $event['eventID'];
	echo $room['roomID'];
	echo $event['title'];

	$linkit = "<a href='?page=viewevent&eventid=" . $event['eventID'] . "'>" . $event['title'] . "</a><br>";
$event['title'] . "</a><br>";
	}
		else{
		$linkit = "&nbsp;";
		}

[tsinka]

Ok,

just to understand:

The lines

startdate2006-07-17 00:00:00
enddate2006-07-17 00:00:00
eventid78
roomid7

are displaying the data that you got from the query for the room 7 (the music room?). That example event looks like it is not the best example event because it seems to start and end at the same time of the same day and not within the range 09:00 to 17:00. So it won't be displayed at all.

Do you want to display just one room a page or do you want to display an overview over all rooms on one page for a given day ?

The url contains the get parameter page=viewday so do you want to have several different views (like based on day, week, month, year ...) ?

Depending on the type of view it might be better to display the days/times horizontically and the events vertically or something like that.

$event['startdate'] contains the date as 'yyyy-mm-dd' so you can't use it directly as second parameter to the date function which needs to be a unix timestamp.

Regarding the table on the example page: I'd suggest to display the events using colspans so that each event gets displayed in one cell that spans the rows as neccessary (maybe with different background colors or something like that).

The logic to do the rowspan shouldn't be too complicated. I think you can do that with arrays and a little bit of code.

Hope I don't sound too complicated.

Thomas

[mattastic]

Hi Thomas,

Thankyou for that, I would just like to stick to an overview page for a set day at the moment. I have that page working now thanks.

Regarding your sql to check to see if the room is free, I have only used while loops with queries so far, could you please tell me how I could code this part?

Thankyou