Fetch array from SQL database, sort, then add autoincrement column.

[delambo]

I am producing a php site to take care of the administration of a charity raft race.  Currently the entrants enter their details into a form which populates a database table including the raft name and the category they wish to enter into (letter A-J).  As the details are entered it generates an auto incrimenting unique key which has until now been used as the raft number and the rafts are launched in order of number.  This year however it has been decided to launch the rafts in order of category and the raft numbers to be arranged accordingly, so I need to produce some code to sort by category then update with an auto incrimenting column.

 

SO...

if the table looks like this on the morning of the race

 

 

I want to write the code to update the table to look something like this by running a php script

 

 

BUT I have no idea where to start with the code and that is where I need a little help.

 

The race is in a few days time and since they raise thousands for my favorite charity I do not want to let them down so any help would be greatly appreciated.

 

Many thanks for your help.

[Psycho]

An auto-incrementing id is used for referential integrity of the database - especially when a foreign key is needed. You should not use the primary index for a sorting value.

 

Now, I can provide a solution, but I see a problem with the requirements. As I understand the requirements the boat numbers will be ordered by category then by the order they registered. So, if you have four boats in category A they will be 1, 2, 3, 4. Then the boats in category B would start at 5. But, what happens when you need to add another boat in group A? Are there no "last minute" entries? If so, you would either have to assign that entry a number at the end of all the previously assigned numbers OR you would have to assign them a number in the order defined above and reassign numbers for all the ones that follow.

 

If you can provide some information on how "last minute" entries should be handled I can help, but that decision will affect the solution I build.

[delambo]

Wow that was quick!!

 

You have understood me correctly.  The organisers have stated this year that there are to be no last minute entries, however I do not want to be revisiting the code on the morning of the race when several last minute entries arrive and the organisers decide to let them race so...

 

It would not be practical to reassign all the rafts numbers but would be more pratical to add them at the end of the previosly assigned numbers.

 

Thank you so much for your offer of help and I look forward to seeing your code.

 

 

[Psycho]

OK, here you go. This is a completely functional script. The output is without any style, but I trust you can work on that. You can load this page at any time. It will give you a list of all the entrants and their currently assigned raft numbers (if they have been assigned one). If they have not been assigned a raft number it will show "UNASSIGNED". If there are any unassigned rafts there will be a button to assign raft numbers. You should only click that button when you are ready to generate those numbers (i.e. race day) otherwise if new entrants are added they will not be in the order by category. But, if you do get last minute entrants you can load this page and see that there are entrants that have not been given a number. The button will be available again and you can then assign a number to those last minute entrants that will start after the previous last number.

 

Make sure you either change the variables at the top of the script of include your own code for database connection (and don't forget the table name).

 

EDIT: For testing purposes just do a single update query to reset all the raft_number values to 0 to start over.

 

<?php

$server = 'localhost';
$uname = 'root';
$pword = '';
$dbName = 'test';
$tableName = 'entrants';

$link = mysql_connect($server, $uname, $pword);
if (!$link) { die('Could not connect: ' . mysql_error()); }
if (!mysql_select_db($dbName)) { die('Could not select database: ' . mysql_error()); }

//Check if user selected to assign raft numbers
if(isset($_POST['assign']))
{
    //Create and run query to get the highest currently assigned raft number
    $query = "SELECT MAX(raft_number) FROM {$tableName}";
    $result = mysql_query($query) or die(mysql_error() . " <br>\n{$query}");
    //Define last assigned raft number
    $raftNo = mysql_result($result, 0);

    //Create run query to get all unassigned rafts
    $query = "SELECT id
              FROM {$tableName}
              WHERE raft_number = 0
              ORDER BY category, id";
    $result = mysql_query($query) or die(mysql_error() . " <br>\n{$query}");

    //Process results to generate list of "when"
    //clauses to be used in a single update query
    $WHEN_CLAUSES = '';
    while($row = mysql_fetch_assoc($result))
    {
        $raftNo++;
        $WHEN_CLAUSES .= "  WHEN {$row['id']} THEN {$raftNo} \n";
    }

    //Create and run query to assign raft numbers
    $query = "UPDATE {$tableName}
              SET raft_number = CASE id
                {$WHEN_CLAUSES}
                ELSE raft_number
              END";
    $result = mysql_query($query) or die(mysql_error() . " <br>\n{$query}");
}

//Display the current list of entrants
$query = "SELECT raftname, category, raft_number
          FROM {$tableName}
          ORDER BY category, id";
$result = mysql_query($query) or die(mysql_error() . " <br>\n{$query}");
//Create output of all entrants
$output = '';
while($row = mysql_fetch_assoc($result))
{
    $raftNo = ($row['raft_number']==0) ? 'UNASSIGNED' : $row['raft_number'];
    $output .= "<tr>\n";
    $output .= "<td>{$row['raftname']}</td>\n";
    $output .= "<td>{$row['category']}</td>\n";
    $output .= "<td>{$raftNo}</td>\n";
    $output .= "</tr>";
}

//Run query for ONLY the rafts that have not been
//assigned numbers and order the results by catory/id
$query = "SELECT id
          FROM {$tableName}
          WHERE raft_number = 0
          ORDER BY category, id";
$result = mysql_query($query) or die(mysql_error() . " <br>\n{$query}");
$unassigned_count = mysql_num_rows($result);

//Create button to assign raft numbers (if needed)
if($unassigned_count==0)
{
    $assignButton = '';
}
else
{
    $assignButton  = "<form method='post' action=''>\n";
    $assignButton .= "<input type='hidden' name='assign' value='1'>\n";
    $assignButton .= "<button type='submit'>Assign Raft Numbers</button>\n";
    $assignButton .= "</form>\n";
}

?>
<html>
<head></head>
<body>

<table border="1">
  <tr>
    <th>Raft Name</th>
    <th>Category</th>
    <th>Raft Number</th>
  </tr>
  <?php echo $output; ?>
</table>

<?php echo $assignButton; ?>

</body>
</html>

[delambo]

Wow that is awesome - thanks mjdamato.

 

I really did not expect someone to write the code for me, more suggestions as to how I could go about it myself.  I have not tested the code yet but reading through it it looks like it is exactly what I need.