I have 2 tables. "Group" has the group name and group number, "People" has the person's name, personal id, group number and other personal data. As I see it, there are 3 ways to record the order information.
- Add 5 columns to "group", one for each position rank. Each column will contain the personal id of the person ranked at that position.
- Add 1 column to "People" for the position rank within the group.
- Add another table with the columns group number, rank and personal id.
Whatever the choice, my question is how do I make sure that each personal id has only 1 rank position?
The second question is can I make an update in options 2 or 3 in a single $sql query? Updating option 1 would look something like the query posted previously:
$sql = "UPDATE `group` SET (spot1, spot2, spot3, spot4, spot5) VALUES ('$name1', '$name2', '$name3', '$name4', '$name5') WHERE group_number='$group'"
I'm having trouble figuring out how to create a query that will update the rank number for multiple rows without running as many queries as I have rank positions. I would change my menu to this:
<select name="id1">
<option value="100">Fred</option>
<option value="101">Wilma</option>
<option value="102">Barney</option>
<option value="103">Betty</option>
<option value="104">Dino</option>
</select>
<select name="id2">
<option value="100">Fred</option>
<option value="101">Wilma</option>
<option value="102">Barney</option>
<option value="103">Betty</option>
<option value="104">Dino</option>
</select>
Then my queries would look something like this.
$sql = "UPDATE `people` SET (rank) VALUES ('1') WHERE id='$id1'"
$sql = "UPDATE `people` SET (rank) VALUES ('2') WHERE id='$id2'"
Am I over complicating this?