View Single Post
Old 03-20-10, 12:52 AM   #17
t3hl33td4rg0n
Dethklok Returns!
 
t3hl33td4rg0n's Avatar
 
Join Date: Dec 2004
Location: Kent, OH
Posts: 2,142
Send a message via AIM to t3hl33td4rg0n Send a message via MSN to t3hl33td4rg0n Send a message via Yahoo to t3hl33td4rg0n
Default Re: Timetable in MySQL

Double purpose post here....

What about using time units/points? Cable companies generally have a quota for field technicians.

Example:

Code:

instructorPool

uid   instructorName     slotStart    slotEnd    totalSeats
1     Smith, Jane        08:15        10:00      5
2     Smith, Jane        10:15        12:00      12
3     Smith, Jane        13:00        15:00      6
4     Smith, Jane        15:15        17:00      9
5     Dover, Ben         08:15        10:00      5
6     Dover, Ben         10:15        12:00      10
7     Dover, Ben         13:00        15:00      4
8     Dover, Ben         15:15        17:00      5
9     Barbaz, Foo        08:15        10:00      12
10    Barbaz, Foo        10:15        12:00      11
11    Barbaz, Foo        13:00        15:00      9
12    Barbaz, Foo        15:15        17:00      7



studentPool 

uid   studentName           status       creditHours       totalUnits
1     poopiepants, paul     active       16                48
2     rocket, johnny        active       12                36
3     jenkins, leeroy       active       6                 18
4     ventura, ace          active       8                 24
5     mark, marky           active       14                42
6     gates, bill           active       16                48


registeredSessions

uid  slotRegd    studentRegd         regDate      regTime
1    2           jenkins, leeroy     2010-02-08   06:00
2    4           rocket, johnny      2010-02-08   08:30
3    2           poopiepants, paul   2010-02-08   08:30
4    2           ventura, ace        2010-02-09   13:00
5    4           gates, bill         2010-02-08   06:00
6    4           poopiepants, paul   2010-02-09   13:00
7    2           gates, bill         2010-02-10   09:15


So lets say you want to see how many slots are open for Jane Smiths 10:15 - 12:00


myQuery = SELECT * FROM `instructorPool` WHERE `instructorName` = "Smith,Jane" AND `slotStart` = "10:15" LIMIT 0,1

You get:

uid   instructorName     slotStart    slotEnd    totalSeats
2     Smith, Jane        10:15        12:00      12

Then:

studentsRegistered = SELECT * FROM `studentPool` WHERE `slotRegd` = myQuery			# Get people registered for the class

You get:

uid  slotRegd    studentRegd        regDate      regTime
1    2           jenkins, leeroy    2010-02-08   06:00
3    2           poopiepants, paul  2010-02-08   08:30
4    2           ventura, ace       2010-02-09   13:00
7    2           gates, bill        2010-02-10   09:15


Then:

howMany = mysql_num_rows (studentsRegistered)										# How many people registered for this slot? (4)

slotsOpen = myQuery[totalSeats] - howMany											# How many seats open (8)

if (slots < 1) {
	
    echo 'Sorry, you cant register for this slot <br />';
    
    echo 'Here are the people who are registered.';

    echo 'blah blah list of names blah blah';

} else {

Now lets find out if the student has the available units to reg (We will say bill gates is logged in)


getStudentInfo = SELECT `totalUnits` FROM `studentPool` WHERE `studentName` = NameofLoggedInStudent
totalSlotsQuery = SELECT `slotRegd` FROM `registeredSessions` WHERE `studentRegd` = NameofLoggedInStudent


You get: 


totalUnits
48

slotRegd
4
2


Now: 

studentUsedUnits = mysql_num_rows (totalSlotsQuery) * 3							# Has used 6 of 48 credits

freeUnits = totalSlotsQuery[totalUnits] - studentUsedUnits


if (freeUnits > (slotsOpen * 3)) {
	
    echo "Would you like to register for this class?";
    
} else {
	echo "You dont have enough credit hours to register for this class"
    
}
I think you probably get the idea at this point.
__________________


Intel QX9650 @ 3.83GHzXFX 780ieVGA GTX 560Ti8GB GSkill DDR800Creative Sound Blaster X-Fi Titanium PCI-ESeagate 1.5TB . 4x Samsung 2TB

Quote:
Originally Posted by MowTin View Post
....it's so clear how government makes things worse not better...
t3hl33td4rg0n is offline   Reply With Quote