|
|
pow
|
posted on 2nd Jun 14 at 18:17 |
Meh, the page loads in less than a second so that's fine for me!
|
Dom
|
posted on 2nd Jun 14 at 17:56 |
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
|
posted on 2nd Jun 14 at 17:43 |
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
|
pow
|
posted on 2nd Jun 14 at 17:35 |
Dom you are nothing short of a legend, I'm just messing around with it to get it as I want it!
|
Dom
|
posted on 2nd Jun 14 at 15:04 |
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
|
posted on 2nd Jun 14 at 14:54 |
I do appreciate the help though :)
|
pow
|
posted on 2nd Jun 14 at 14:45 |
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 :D
And go easy on me, this is my first ever dive into both PHP and MYSQL :lol:
[Edited on 02-06-2014 by pow]
|
Dom
|
posted on 2nd Jun 14 at 14:35 |
Need to LEFT JOIN the bookings and format your queries properly :mad::lol: -
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:00 | NULL |
| 9:00 - 9:30 | Year 6 |
| 9:30 - 10:00 | Year 6 |
| 10:00 - 10:30 | NULL |
| 10:30 - 11:00 | NULL |
| 11:00 - 11:30 | NULL |
| 11:30 - 12:00 | NULL |
| 12:00 - 12:30 | NULL |
| 12:30 - 13:00 | NULL |
| 13:00 - 13:30 | Year 6 |
| 13:30 - 14:00 | Year 6 |
| 14:00 - 14:30 | Year 6 |
| 14:30 - 15:00 | NULL |
| 15:00 - 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 :facepalm:
[Edited on 02-06-2014 by Dom]
|
pow
|
posted on 2nd Jun 14 at 12:04 |
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
|
posted on 2nd Jun 14 at 12:04 |
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 :nod:
|
pow
|
posted on 2nd Jun 14 at 12:03 |
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 |
+---------------+-------------------+--------+
:(
|
Baskey
|
posted on 2nd Jun 14 at 12:00 |
So
periods
Left outer Join to bookings
Left outer joing to users
In that order
[Edited on 02-06-2014 by Baskey]
|
Baskey
|
posted on 2nd Jun 14 at 11:58 |
Ok start with the periods table as the first table in your statement then left outer join the other tables
|
pow
|
posted on 2nd Jun 14 at 11:48 |
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]
|
Dom
|
posted on 2nd Jun 14 at 11:42 |
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.
|
Baskey
|
posted on 2nd Jun 14 at 11:41 |
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 !
|
pow
|
posted on 2nd Jun 14 at 11:14 |
I've rewritten that query like 50 times in different ways and my output looks EXACTLY the same :lol:
[Edited on 02-06-2014 by pow]
|
pow
|
posted on 2nd Jun 14 at 11:10 |
I've even tried reordering the JOINs and changing the order of the two JOINs
|
pow
|
posted on 2nd Jun 14 at 11:07 |
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 :(
|
gooner_47
|
posted on 2nd Jun 14 at 11:02 |
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
|
posted on 2nd Jun 14 at 10:40 |
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]
|
