< Back to Code Samples
<?php
/*****************************************************************
* Queries.php
* Purpose: This file contains definitions of all queries used in
* this application. There are two advantages to doing it this
* way. 1) Cleaner looking code. and 2) Feedback is simultaneously
* more informative (on an error) and more secure. Instead of a
* failed query saying something like "Query failed: SELECT * FROM
* blah blah blah WHERE blah blah blah" It says "Query failed: GET_ALL_RES".
* Thus, it's meaningful to the programmer but tells a would-be
* hax0r nothing.
*
* [ I realize that these are probably more suited for Stored Procedures, but I'm
* still learning how to do SP's and didn't want to use new knowledge on production-level
* applications]
*
* Written: October 27th 2007 by Aaron Hill (AMH)
*
*****************************************************************/
define('MY_RES',"
SELECT
r.reservation_id AS Reservation_Number,
CONVERT(varchar(5),s.start_time,108) + ' - ' + convert(varchar(5),s.end_time,108) AS Slot_time,
CONVERT(char(10),s.start_time,101) AS date_of_slot,
r.course_name + ' : ' + r.test_name AS test_name,
(
SELECT
COUNT(*)
FROM
reservations AS sub_r
WHERE
slot_id=s.slot_id AND
date_registered <= r.date_registered
) AS My_seat,
s.seats_available AS Total_Seats,
r.date_registered AS Time_Registered,
b.long_name + ' ' + s.room_number AS Location
FROM
reservations AS r
LEFT JOIN slots as s ON s.slot_id=r.slot_id
LEFT JOIN [pub].[dbo].[buildings] AS b ON b.building_id=s.building_id WHERE username='%s'
");
define('REMAINING_RES',"
SELECT
s.slot_id,
s.seats_available AS total_seats,
(
SELECT
COUNT(*)
FROM
reservations as sub_r
WHERE
sub_r.slot_id=s.slot_id) AS seats_occupied,
CONVERT(char(10),s.start_time,101) AS date_of_slot,
CONVERT(varchar(5),s.start_time,108) + ' - ' + convert(varchar(5),
s.end_time,108
) AS timeslot,
b.abbreviation + ' ' + s.room_number + ' (' + b.long_name + ')' AS room
FROM
slots AS s
LEFT JOIN
[pub].[dbo].[buildings] AS b
ON
b.building_id = s.building_id
WHERE
s.slot_id NOT IN (SELECT slot_id FROM reservations WHERE username='%s') AND
DATEDIFF(hh,GETDATE(),s.start_time) > 24 AND
((s.seats_available) - (SELECT COUNT(*) FROM reservations as sub_r WHERE sub_r.slot_id=s.slot_id)) > 0
ORDER BY
s.start_time DESC
");
define('CURRENT_RES',"
SELECT
r.reservation_id AS reservation_id,
r.username AS username,
CONVERT(varchar(5),s.start_time,108) + ' - ' + convert(varchar(5),s.end_time,108) AS Slot_time,
CONVERT(char(10),s.start_time,101) AS date_of_slot,
r.course_name + ' : ' + r.test_name AS test_name,
(
SELECT
COUNT(*)
FROM
reservations AS sub_r
WHERE
slot_id=s.slot_id AND date_registered <= r.date_registered
) AS Place_in_line,
s.seats_available AS Total_Seats,
r.date_registered AS Time_Registered,
r.phone_number
FROM
reservations AS r
LEFT JOIN
slots as s
ON
s.slot_id=r.slot_id
WHERE
DATEDIFF(hh,GETDATE(),s.start_time) > -1
ORDER BY
s.start_time DESC
");
define('OLD_RES',"
SELECT
r.reservation_id AS reservation_id,
r.username AS username,
CONVERT(varchar(5),s.start_time,108) + ' - ' + convert(varchar(5),s.end_time,108) AS Slot_time,
CONVERT(char(10),s.start_time,101) AS date_of_slot,
r.course_name + ' : ' + r.test_name AS test_name,
(
SELECT
COUNT(*)
FROM
reservations AS sub_r
WHERE
slot_id=s.slot_id AND date_registered <= r.date_registered
) AS Place_in_line,
s.seats_available AS Total_Seats,
r.date_registered AS Time_Registered,
r.phone_number
FROM
reservations AS r
LEFT JOIN
slots as s
ON
s.slot_id=r.slot_id
WHERE
DATEDIFF(hh,GETDATE(),s.start_time) < 0 OR
s.start_time is NULL
");
define('DEL_OLD_RES',"
DELETE FROM
reservations
WHERE
EXISTS (
SELECT r.reservation_id
FROM reservations AS r
LEFT JOIN slots AS s ON s.slot_id=r.slot_id
WHERE s.start_time is NULL
)
");
define('DEL_OLD_SLOTS',"
DELETE FROM
slots
WHERE
DATEDIFF(hh,GETDATE(), start_time) < 0");
define('DEL_RES',"DELETE FROM reservations WHERE reservation_id='%s' AND username='%s'");
define('DEL_SLOT',"DELETE FROM slots WHERE slot_id='%s'");
define('ADD_RES',"INSERT INTO reservations (username, slot_id, course_name, test_name, date_registered, phone_number) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')");
define('ADD_SLOT',"INSERT INTO slots (seats_available, start_time, end_time, building_id, room_number) VALUES ('%s', '%s', '%s', '%s', '%s')");
define('UPDATE_SLOT', "UPDATE slots SET seats_available='%s', start_time='%s', end_time='%s', building_id='%s', room_number='%s' WHERE slot_id='%s'");
define('GET_SLOT',"
SELECT
slot_id,
seats_available,
DATEPART(HH,start_time) AS start_time_h,
DATEPART(MM,start_time) AS start_time_m,
DATEPART(HH,end_time) AS end_time_h,
DATEPART(MM,end_time) AS end_time_m,
CONVERT(VARCHAR,start_time,101) AS date,
building_id,
room_number
FROM
slots
WHERE
slot_id='%s';
");
define('OLD_SLOT', "
SELECT
slot_id,
seats_available,
start_time,
end_time,
b.long_name AS building,
room_number
FROM
slots AS s
LEFT JOIN
[pub].[dbo].[buildings] AS b
ON
b.building_id = s.building_id
WHERE
DATEDIFF(hh,GETDATE(),s.start_time) < 0 ORDER BY start_time DESC
");
define('CURRENT_SLOT', "
SELECT
slot_id,
seats_available,
start_time,
end_time,
b.long_name AS building,
room_number
FROM
slots AS s
LEFT JOIN
[pub].[dbo].[buildings] AS b
ON
b.building_id = s.building_id
WHERE
DATEDIFF(hh,GETDATE(),s.start_time) > 0 ORDER BY start_time DESC
");
define('REPORT_BY_STUDENT_DDL',"
SELECT
res.username,
res.username + ' (' + CONVERT(VARCHAR,(SELECT COUNT(*) FROM reservations AS r WHERE r.username=res.username)) + ')' AS text
FROM
reservations AS res GROUP BY res.username
");
define('REPORT_BY_SLOT_DDL',"
SELECT
slots.slot_id,
CONVERT(VARCHAR,slots.start_time) + ' (' + CONVERT(VARCHAR, (SELECT COUNT(*) FROM reservations AS r WHERE r.slot_id=slots.slot_id)) + ')' AS count
FROM
slots
");
define('REP_ALLSLOTS',"
SELECT
slot_id,
(
SELECT COUNT(*) FROM reservations AS r WHERE r.slot_id=s.slot_id
) AS seats_used,
seats_available,
start_time,
end_time,
b.long_name AS building,
room_number FROM slots AS s
LEFT JOIN
[pub].[dbo].[buildings] AS b
ON
b.building_id = s.building_id
ORDER BY
start_time DESC
");
define('REP_ALLRES',"
SELECT
r.reservation_id AS reservation_id,
r.username AS username,
CONVERT(varchar(5),s.start_time,108) + ' - ' + convert(varchar(5),s.end_time,108) AS Slot_time,
CONVERT(char(10),s.start_time,101) AS date_of_slot,
r.course_name + ' : ' + r.test_name AS test_name,
(
SELECT COUNT(*) FROM reservations AS sub_r WHERE slot_id=s.slot_id AND date_registered <= r.date_registered
) AS Place_in_line,
s.seats_available AS Total_Seats,
r.date_registered AS Time_Registered,
r.phone_number
FROM
reservations AS r
LEFT JOIN
slots as s
ON
s.slot_id=r.slot_id
WHERE
s.start_time != NULL
ORDER BY
s.start_time DESC");
define('REP_SLOT',"SELECT
reservation_id,
username,
date_registered AS date,
course_name,
test_name,
phone_number
FROM
reservations
WHERE
slot_id='%s'
ORDER BY
date
");
define('REP_STUDENT',"
SELECT
reservation_id,
CONVERT(VARCHAR,start_time,101) AS date,
SUBSTRING(CONVERT(VARCHAR,start_time,8),0,3) AS start_h,
SUBSTRING(CONVERT(VARCHAR,start_time,8),3,3) AS start_m,
SUBSTRING(CONVERT(VARCHAR,end_time,8),0,3) AS end_h,
SUBSTRING(CONVERT(VARCHAR,end_time,8),3,3) AS end_m,
course_name + ' : ' + test_name AS test,
b.long_name + ' ' + s.room_number AS location,
phone_number
FROM
reservations AS r
LEFT JOIN
slots AS s ON s.slot_id = r.slot_id
LEFT JOIN
[pub].[dbo].[buildings] AS b ON b.building_id=s.building_id
WHERE
username='%s'
ORDER BY
date_registered
");
define('ADD_USER',"INSERT INTO admin_privs (username, date_added, granted_by) VALUES ('%s', GETDATE(), '%s')");
define('DEL_USER',"DELETE FROM admin_privs WHERE priv_id='%s'");
define('GET_USERS',"SELECT * FROM admin_privs");
// no peeking!
?>