PHP, MYSQL Help with Creating Waitlist

[goosebriar]

Would anyone have any suggestions as to how to set up a waitlist?

 

Here is what I have:

A user goes on to my site and registers for workshop.  They are offered a list of open workshops or they can be waitlisted for workshops that are full.  What would be the best way to structure this?

 

I currently have 3 basic tables:

users

register (transaction info that connects users to workshop, has timestamp)

workshops (info about each workshop, date, time, cap, etc.)

 

My idea was to have an "order" field in the register table that would renumber itself every time someone registers or changes registration, but I am having difficulty with this renumbering. I was thinking I could use this order field to tell if someone's order has changed.  So for instance, I cap the workshop at 5 people, and 6 people are registered.  The 6th person is actually on a waiting list.  Then someone drops out of the workshp and now that 6th person is the 5th person and is no longer on the wait list.  I could create some php code that said, if the person's old order field is 6 and the new order field is 5, then send an email "you are registered!"  That is what I am trying, but I am having difficult storing the old value and then renumbering and finding the new value.

 

Any ideas?

 

[marmite]

this shouldn't be a problem.

 

you could either do this in your PHP or in your DB, i'd suggest in the DB.

 

(If you want a useful php function, array_splice sounds useful for you: http://uk.php.net/array_splice)

 

to do this in your DB you coudl do this in one table:

 

TABLE

workshop1  username1  statusyes timestamp

workshop2  username2  statusyes timestamp

workshop1  username3  statusyes timestamp

workshop2  username4  statusyes timestamp

workshop3  username5  statusyes timestamp

workshop2  username6  statusyes timestamp

workshop2  username7  statusyes timestamp

workshop2  username8  statusyes timestamp

 

so here there are five users for workshop2, all statusyes. when username7 cancels, that row is deleted. you check to see total number of users for that workshop, and if it's more than 5 (before you delete username 7) you run the delete script and then an update script where timestamp is minimum for all remaining "statuswait" users for workshop2.

 

Something like:

1) delete from table where username=x and workshop=workshop2 and status=statusyes

2) update table set status=statusyes where status=statuswait and workshop=workshop2 and timestamp=min(select timestamp from table where workshop=workshop2)

 

 

when username9 comes along, you use PHP IF statement to say if (count(distinct(username)) from table where workshop=workshop2)<5) { $query="insert row with statusyes" } else { $query="insert row with statuswait" }

 

sorry this is rushed, do you see what i mean?

 

If you get stuck, reply to this and I'll write out something properly!

[goosebriar]

Beautiful!  That is much more simple than trying to put an order in and it allows me to have a wider range of functions.  I will try it!