Need Advice on a waiting list in php

f50bodykit

Im writing a php script that will function as a waiting list. I have created a database in mysql that stores a student id number and place in line. The place in line is the primary key and starts at 1. So it looks something like.

1 9883
2 9833
3 9831
4 9332
5 9334

Administrators will be able to remove people from the list by going to a page and placing a check on each person they want to take off and then finally hitting a submit button.

The solution i have come up with is that if an id is not checked. the script will put it into an array. For example lets say 2 and 4 are checked, that means i will put 3 items in the array and I will leave out record numbers 2 and 4. so

$array[1] = 9883
$array[2]= 9831
$array[3]= 9334

I then plan on having a loop that will go through each element of the array and update

sid = student id
pid = place in line id

"UPDATE sid WHERE pid = [$count] SET sid=$array[$count]"

and then
erase the remaining elements that need to be removed.

This solution seems inefficient to me so im wondering how would other people do it. I have a problem of not being able to see a better solution when I am on to something that might work.

Any help is greatly appreciated, thank you.

sarahk

Do you need to update their position. can it not be calculated each time it runs.

I would have thought your waiting list table would look like

waitinglistid
sid (student id)
status (open, closed)
dateadded
dateclosed

the last 2 allow for stats on processing.

then you just query where the status is open order by waitinglistid. People need never be "removed" from the list, just change their status to closed.
As you loop through the results a simple counter can apply their actual position. No need to save it.

Hope this helps.

f50bodykit

Yes that helps very much, I did my solution already and it works, but it is really not a good solution because i erase all the data and then re "INSERT" it from the array. Thank you for taking the time to reply to my post. I wish i could of thought of that on my own.

Jason