Timetable in MySQL

[Tuork]

Well, we're still waiting on the client to make up his mind. It came down to us, and another company. No word just yet

[ViN86]

Do I get a consulting fee?

[Tuork]

You sure do!

Here you go:

[ViN86]

Sweet, W's the man.

[t3hl33td4rg0n]

Double purpose post here....

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

Example:

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.

[Tuork]

Holy thread revivals, batman!

[t3hl33td4rg0n]

Originally Posted by Tuork

Holy thread revivals, batman!

It was more practice than anything... Started working on a new CMS.

But hey, someone might find it useful!

[Tuork]

Thanks for the input.

I was just taken aback by this thread's random revival.

[seozest]

I think you need simplified db for this for which you can use error checking code.
As suggested above.Try to make it less complicated.

----------------------------------------
Outsourced product development
www.e-zest.net

[t3hl33td4rg0n]

lol wut?