pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I'm a total beginner with this but I'm getting somewhere. I have adapted a room booking system for use at my school but now want to use a page to show what is on in which room.
I'm using the query:
SELECT name, displayname, notes FROM bookings INNER JOIN users ON (bookings.user_id = users.user_id) INNER JOIN periods ON (bookings.period_id = periods.period_id) WHERE (room_id="3" AND (date=CURDATE() or day_num=DAYOFWEEK(CURDATE()) -1)) ORDER BY bookings.period_id asc;
and it's returning 99% what I want:
+---------------+-------------------+--------+
| name | displayname | notes |
+---------------+-------------------+--------+
| 9:00 - 9:30 | Timetabled Lesson | Year 6 |
| 9:30 - 10:00 | Timetabled Lesson | Year 6 |
| 13:00 - 13:30 | Timetabled Lesson | Year 4 |
| 13:30 - 14:00 | Timetabled Lesson | Year 4 |
| 14:00 - 14:30 | Timetabled Lesson | Year 4 |
| 14:30 - 15:00 | Timetabled Lesson | Year 4 |
| 15:00 - 15:30 | Timetabled Lesson | Year 4 |
+---------------+-------------------+--------+
7 rows in set (0.00 sec)
but what I want is the NULL values for the name (which is the name of the period, each half an hour slot so it looks like this:
+---------------+-------------------+--------+
| name | displayname | notes |
+---------------+-------------------+--------+
| 9:00 - 9:30 | Timetabled Lesson | Year 6 |
| 9:30 - 10:00 | Timetabled Lesson | Year 6 |
| 10:00 - 10:30 | NULL | NULL |
| 10:30 - 11:00 | NULL | NULL |
| 11:00 - 11:30 | NULL | NULL |
| 11:30 - 12:00 | NULL | NULL |
| 12:00 - 12:30 | NULL | NULL |
| 12:30 - 13:00 | NULL | NULL |
| 13:00 - 13:30 | Timetabled Lesson | Year 4 |
| 13:30 - 14:00 | Timetabled Lesson | Year 4 |
| 14:00 - 14:30 | Timetabled Lesson | Year 4 |
| 14:30 - 15:00 | Timetabled Lesson | Year 4 |
| 15:00 - 15:30 | Timetabled Lesson | Year 4 |
+---------------+-------------------+--------+
How do I do this?
[Edited on 02-06-2014 by pow]
[Edited on 02-06-2014 by pow]
|
gooner_47
Member
Registered: 20th Jul 04
Location: Bexhill/Croydon
User status: Offline
|
I struggle to work these things out without looking at data and table structure etc, but try a left join or right join from bookings to periods instead of an inner join, i.e.
LEFT JOIN periods ON (bookings.period_id = periods.period_id)
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Yeah I was hoping that would help but it doesn't 
Basically, the bookings table has a period_id in it, which relates to a period displayname, so if the room isn't booked there isn't any data
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I've even tried reordering the JOINs and changing the order of the two JOINs
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I've rewritten that query like 50 times in different ways and my output looks EXACTLY the same 
[Edited on 02-06-2014 by pow]
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
Select a top 10 * from each of the tables and post them up so i can see what the tables look like.
If we can see your tables we could fix it very quickly !
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Struggling without the data in front of me but joining the users and booking tables on to the period table, essentially filling in the 'blanks', should work.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Okay here is the periods table:
+-----------+-----------+------------+----------+---------------+------+----------+
| period_id | school_id | time_start | time_end | name | days | bookable |
+-----------+-----------+------------+----------+---------------+------+----------+
| 1 | 1 | 07:30:00 | 08:30:00 | Before School | 62 |1 |
| 2 | 1 | 08:30:00 | 09:00:00 | 8:30 - 9:00 | 62 |1 |
| 3 | 1 | 09:00:00 | 09:30:00 | 9:00 - 9:30 | 62 |1 |
| 4 | 1 | 09:30:00 | 10:00:00 | 9:30 - 10:00 | 62 |1 |
| 5 | 1 | 10:00:00 | 10:30:00 | 10:00 - 10:30 | 62 |1 |
| 6 | 1 | 10:30:00 | 11:00:00 | 10:30 - 11:00 | 62 |1 |
| 7 | 1 | 11:00:00 | 11:30:00 | 11:00 - 11:30 | 62 |1 |
| 8 | 1 | 11:30:00 | 12:00:00 | 11:30 - 12:00 | 62 |1 |
| 9 | 1 | 12:00:00 | 12:30:00 | 12:00 - 12:30 | 62 |1 |
| 10 | 1 | 12:30:00 | 13:00:00 | 12:30 - 13:00 | 62 |1 |
+-----------+-----------+------------+----------+---------------+------+----------+
and the bookings
+------------+-----------+-----------+---------+---------+---------+---------+------+--------+-----------+
| booking_id | school_id | period_id | week_id | day_num | room_id | user_id | date | notes | cancelled |
+------------+-----------+-----------+---------+---------+---------+---------+------+--------+-----------+
| 24 | 1 | 3 | 1 | 1 | 3 | 1 | NULL | Year 6 | 0 |
| 25 | 1 | 4 | 1 | 1 | 3 | 1 | NULL | Year 6 | 0 |
| 31 | 1 | 11 | 1 | 1 | 3 | 1 | NULL | Year 4 | 0 |
| 32 | 1 | 12 | 1 | 1 | 3 | 1 | NULL | Year 4 | 0 |
| 33 | 1 | 13 | 1 | 1 | 3 | 1 | NULL | Year 4 | 0 |
| 34 | 1 | 15 | 1 | 1 | 3 | 1 | NULL | Year 4 | 0 |
| 35 | 1 | 10 | 1 | 2 | 3 | 1 | NULL | Year 2 | 0 |
| 36 | 1 | 11 | 1 | 2 | 3 | 1 | NULL | Year 2 | 0 |
| 37 | 1 | 12 | 1 | 2 | 3 | 1 | NULL | Year 2 | 0 |
| 38 | 1 | 13 | 1 | 2 | 3 | 1 | NULL | Year 2 | 0 |
+------------+-----------+-----------+---------+---------+---------+---------+------+--------+-----------+
The users just maps the user_id to a displayname (which is their fullname)
[Edited on 02-06-2014 by pow]
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
Ok start with the periods table as the first table in your statement then left outer join the other tables
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
So
periods
Left outer Join to bookings
Left outer joing to users
In that order
[Edited on 02-06-2014 by Baskey]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
SELECT name, displayname, notes FROM periods LEFT OUTER JOIN bookings ON (periods.period_id = bookings.period_id) LEFT OUTER JOIN users ON (bookings.user_id = users.user_id) WHERE (room_id="3" AND (date=CURDATE() or day_num=DAYOFWEEK(CURDATE()) -1)) ORDER BY bookings.period_id asc;
produces:
+---------------+-------------------+--------+
| name | displayname | notes |
+---------------+-------------------+--------+
| 9:00 - 9:30 | Timetabled Lesson | Year 6 |
| 9:30 - 10:00 | Timetabled Lesson | Year 6 |
| 13:00 - 13:30 | Timetabled Lesson | Year 4 |
| 13:30 - 14:00 | Timetabled Lesson | Year 4 |
| 14:00 - 14:30 | Timetabled Lesson | Year 4 |
| 14:30 - 15:00 | Timetabled Lesson | Year 4 |
| 15:00 - 15:30 | Timetabled Lesson | Year 4 |
+---------------+-------------------+--------+
:(
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Baskey
Ok start with the periods table as the first table in your statement then left outer join the other tables
As already mentioned; although it does restrict you to a single day 
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Chnaged the ON about as well:
SELECT name, displayname, notes FROM periods LEFT OUTER JOIN bookings ON (bookings.period_id = periods.period_id) LEFT OUTER JOIN users ON (bookings.user_id = users.user_id) WHERE (room_id="3" AND (date=CURDATE() or day_num=DAYOFWEEK(CURDATE()) -1)) ORDER BY bookings.period_id asc;
Same thing
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Need to LEFT JOIN the bookings and format your queries properly  -
code:
SELECT `periods`.`name`, `bookings`.`notes` FROM `periods` LEFT JOIN `bookings` ON `periods`.`period_id` = `bookings`.`period_id` WHERE 1
Results in -
+---------------+--------+
| name | notes |
+---------------+--------+
| Before School | NULL |
| 8:30 - 9 0 | NULL |
| 90 - 9:30 | Year 6 |
| 9:30 - 100 | Year 6 |
| 100 - 10:30 | NULL |
| 10:30 - 110 | NULL |
| 110 - 11:30 | NULL |
| 11:30 - 120 | NULL |
| 120 - 12:30 | NULL |
| 12:30 - 130 | NULL |
| 130 - 13:30 | Year 6 |
| 13:30 - 140 | Year 6 |
| 140 - 14:30 | Year 6 |
| 14:30 - 150 | NULL |
| 150 - 13:30 | Year 6 |
+---------------+--------+
Edit - As said though, this will only work for a single day; if you needed to pull bookings out across multiple days then i'd look at doing the logic in your scripting rather than within the query.
Edit 2 - My bad; the above fails as soon as you applying conditions 
[Edited on 02-06-2014 by Dom]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
No I only need a single day as it's to go on a screen outside the room to show what's on today so that's fine 
And go easy on me, this is my first ever dive into both PHP and MYSQL
[Edited on 02-06-2014 by pow]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I do appreciate the help though 
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
A subquery on the FROM clause might work; give me a minute....
It's not pretty and i suspect it could be a lot better but try something like this (changed formatting just to make it easier to read) -
code:
SELECT `periods`.`name`, `xb`.`notes`
FROM
(
SELECT `bookings`.`period_id`, `bookings`.`notes`, `bookings`.`user_id`
FROM `bookings`
WHERE `bookings`.`room_id` = '3'
) AS xb
RIGHT JOIN `periods`
ON `periods`.`period_id` = `xb`.`period_id`
WHERE 1
Essentially the subquery formats the booking data outside of the 'main' query, prevents the 'main' query conditions affecting the booking data. Obviously tack on the users table as another join and similarly add date/day/time conditions to the subquery.
Edit - Added users etc -
code:
SELECT `periods`.`name` AS `period_name`, `xb`.`notes`, `users`.`name` AS `user_name`
FROM
(
SELECT `bookings`.`period_id`, `bookings`.`notes`, `bookings`.`user_id`
FROM `bookings`
WHERE `bookings`.`date` = '2014-01-01' AND `bookings`.`room_id` = '3'
) AS xb
RIGHT JOIN `periods`
ON `periods`.`period_id` = `xb`.`period_id`
LEFT JOIN `users`
ON `users`.`user_id` = `xb`.`user_id`
WHERE 1
Which should give you something like -
+----------------+--------+-------------+
| period_name | notes | user_name |
+----------------+--------+-------------+
| Before School | NULL | NULL |
| 8:30 - 9:00 | NULL | NULL |
| 9:00 - 9:30 | Year 6 | Bob |
| 9:30 - 10:00 | Year 1 | Dave |
| 10:00 - 10:30 | NULL | NULL |
| 10:30 - 11:00 | NULL | NULL |
| 11:00 - 11:30 | NULL | NULL |
| 11:30 - 12:00 | NULL | NULL |
| 12:00 - 12:30 | NULL | NULL |
| 12:30 - 13:00 | NULL | NULL |
| 13:00 - 13:30 | Year 6 | Bob |
| 13:30 - 14:00 | Year 4 | Hannah |
| 14:00 - 14:30 | Year 6 | Bob |
| 14:30 - 15:00 | NULL | NULL |
| 15:00 - 13:30 | NULL | NULL |
+---------------+--------+--------------+
[Edited on 02-06-2014 by Dom]
[Edited on 02-06-2014 by Dom]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Dom you are nothing short of a legend, I'm just messing around with it to get it as I want it!
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Just tweaked it:
code:
SELECT `periods`.`name` AS `period_name`, `xb`.`notes`, `users`.`displayname` AS `displayname`
FROM
(
SELECT `bookings`.`period_id`, `bookings`.`notes`, `bookings`.`user_id`
FROM `bookings`
WHERE `bookings`.`room_id` = '3'
AND ((`bookings`.`date` = CURDATE()) OR (`bookings`.`day_num` = DAYOFWEEK(CURDATE()) -1))
) AS xb
RIGHT JOIN `periods`
ON `periods`.`period_id` = `xb`.`period_id`
LEFT JOIN `users`
ON `users`.`user_id` = `xb`.`user_id`
WHERE 1
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by pow
Dom you are nothing short of a legend, I'm just messing around with it to get it as I want it!
There's probably a better/more efficient query to do it but no worries, glad it worked
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Meh, the page loads in less than a second so that's fine for me!
|
