I am setting up a list where I would like the group owner to be able to order the members of the group. I was planning to use 5 drop down menus with the names of each group member and allow the owner to select the name for spot1, spot2,etc.

My drop down code would be something like:

<select name="name1">
<option value="Fred">Fred</option>
<option value="Wilma">Wilma</option>
<option value="Barney">Barney</option>
<option value="Betty">Betty</option>
<option value="Dino">Dino</option>
</select>

My query would be:

$sql = "UPDATE `group` SET (spot1, spot2, spot3, spot4, spot5) VALUES ('$name1', '$name2', '$name3', '$name4', '$name5') WHERE group_number='$group'"

I'm wondering how to make sure each name is only used once.

    It sounds like you want two tables, e.g.,

    Table: group
    Columns: number, name

    Table: member
    Columns: name, group_number, ordering

    Then, you create a unique constraint for the member table on (group_number, ordering). The ordering field would be an arbitrary integer such that the members of a group can be ordered.

    Unless you have some very special reasons for doing so, you should not have numbered columns for a table in a relational database.

    EDIT:
    If there is a many to many relationship between members and groups, then you might move the member name to a third table specifically for members, then change my proposed member table into a join table, e.g., named membership or group_member. This way, you might also create a unique constraint on the name column for the new member table.

      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.

      1. Add 5 columns to "group", one for each position rank. Each column will contain the personal id of the person ranked at that position.
      2. Add 1 column to "People" for the position rank within the group.
      3. 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?

        By the way, my second set of queries would go with option 2.

          Is the answer to my question regarding unique ranks a giant series of if/else statements?

          if ($id1 == $id2) 
          {echo "You ranked the same person twice.";} 
          else 
          {$sql = "UPDATE `people` SET (rank) VALUES ('1') WHERE id='$id1'"
          $result = mysql_query($sql) or  die(mysql_error());
          
          $sql = "UPDATE `people` SET (rank) VALUES ('2') WHERE id='$id2'" 
           $result = mysql_query($sql) or  die(mysql_error());
          }

          For 2 ranks it would be easy but for more it would be complicated.

          if ($id1 == $id2) 
          {echo "You ranked the same person twice.";} 
          else 
          {if ($id1 == $id3) 
          {echo "You ranked the same person twice.";} 
          else 
          {if ($id2 == $id3) 
          {echo "You ranked the same person twice.";} 
          else {$sql = "UPDATE `people` SET (rank) VALUES ('1') WHERE id='$id1'"
          $result = mysql_query($sql) or  die(mysql_error());
          
          $sql = "UPDATE `people` SET (rank) VALUES ('2') WHERE id='$id2'" 
           $result = mysql_query($sql) or  die(mysql_error());
          
          $sql = "UPDATE `people` SET (rank) VALUES ('3') WHERE id='$id3'" 
           $result = mysql_query($sql) or  die(mysql_error());
          }}}

          Doing it with 5 would be very long and redundant.

            jollyollie1 wrote:

            1. Add 5 columns to "group", one for each position rank. Each column will contain the personal id of the person ranked at that position.

            Once again, that would be having numbered columns. It is almost certainly a wrong thing to do.

            jollyollie1 wrote:

            2. Add 1 column to "People" for the position rank within the group.

            Yes, that could work. It is what I described. However, this solution is good when a person belongs to at most one group, or you are okay with repeating rows when a person belongs to more than one group (e.g., the situation is sufficiently rare that further database normalisation is regarded as an unnecessary).

            jollyollie1 wrote:

            3. Add another table with the columns group number, rank and personal id.

            Yes, that could work. It is what I described in my edit. This is an apt solution when it is clear that a group has many members and a person can be the member of multiple groups.

            jollyollie1 wrote:

            Whatever the choice, my question is how do I make sure that each personal id has only 1 rank position?

            Enforce it at database level with a unique constraint. Also, enforce it in PHP by checking that each rank position is unique. Additionally, use clientside Javascript to check so that the user can be prompted on an error. One user friendly approach that comes to mind is to use a widget such that the user drags the member entry into place, and then the Javascript generates the rank position numbers behind the scenes.

            jollyollie1 wrote:

            The second question is can I make an update in options 2 or 3 in a single $sql query?

            Possible, but generally using multiple SQL statements for this is fine. With prepared statements, you prepare the same statement and then bind the differing values for each row. Wrap the series of statements in a single transaction.

              laserlight,

              Thanks for the explanation. I'm off to implement it now. I'm still slightly confused about making each rank UNIQUE. There are multiple groups and therefor there will be multiple users whose rank is "1" within their group.

              Thanks,
              JollyOllie1

                jollyollie1 wrote:

                Is the answer to my question regarding unique ranks a giant series of if/else statements?

                No. Make use of arrays instead of numbering your variables. For example, if you had an array of rankings, [man]array_unique/man could be used to eliminate duplicates.

                jollyollie1 wrote:

                I'm still slightly confused about making each rank UNIQUE. There are multiple groups and therefor there will be multiple users whose rank is "1" within their group.

                So, check for uniqueness within each group.

                  Write a Reply...