php and mysql array sorting and more

[aramilmoonmist]

so last week my boss decided we need to add functionality to our scheduling system. currently, our boss creates our schedules using a php/mysql system created by my predecessors. if we cant make it in we put up a sub request for that shift, or can put in a sub request for parts of a shift too (sub requests only affect the current week), and someone else will pick it up. the new thing she wants put in is the ability to have someone whos taken a sub shift be able to put some or all of that back up for subbing.

im having issues with php's array sorting for an array of mysql records, and in some places, the concept in general.

heres some sample code that im just trying to get to display a single shift:
//this puts out a bunch of cells with times in half hour increments
$NumCells = 38;
$StartTime = "7:00:00";
echo"<table><tr>";
for($i = 0; $i < $NumCells; $i++)
{
echo("<td class='time' colspan=2 width='20px' align=center>" .LongTimeToShortTime($StartTime). "</td>");
$time = explode(":", $StartTime);
if($time[1] == "00")
{
$time[1] = "30";
}
else
{
$time[1] = "00";
$time[0]++;
}
$StartTime = implode(":",$time);
if($StartTime == "25:00:00")
$StartTime = "01:00:00";
}
echo "</tr>";

//create arrays
$subArray = array();
$tempSubArray = array();
$nextTime = '24:00:00';

//sql statement
//table subs has all the subshifts in it and has the elements shiftID,
//subID(index), beginTime, stopTime, date
//table shifts has the shift information including; shiftID(index), day(0-6),
//startTime, endTime
$sql2 = "SELECT * FROM `subs` WHERE `shiftID` = '".$_GET['shiftID']."' ORDER BY 'beginTime' DESC";
$result2 = mysql_query($sql2);

//this if statement determines if a blank cell needs to be put before the beginning of the shift
if(!($record['startTime'] == '07:00:00'))
{
echo"<td class='cell' bgcolor='' ColSpan=";
echo(echoTimeDiff('07:00:00',$record['startTime'])*4);
echo"></td>";
}

//stores all the mysql records into an array
while($record2 = mysql_fetch_assoc($result2))
{
array_push($tempSubArray, $record2);
}

//this is the part im having troubles with. im using the fact that records with
//higher subID are newer and thus higher priority
//after some are blanked out, id want to resort the array by different mysql
//keys, or if anyone can suggest a better method
$countI = 0;
foreach($tempSubArray as $i)
{
$countJ = 0;
if($i != '')
foreach($tempSubArray as $j)
{
if($j != '')
{
//these 2 if statements take out subs that are overlapped
//by a higher priority sub
if(($i['subID'] > $j['subID']) && ($i['beginTime'] <= $j['beginTime']) && ($i['stopTime'] >= $j['stopTime']))
$tempSubArray[$countj] = '';
elseif(($i['subID'] < $j['subID']) && ($i['beginTime'] >= $j['beginTime']) && ($i['stopTime'] <= $j['stopTime']))
$tempSubArray[$counti] = '';
}
$countJ++;
}
$countI++;
}

//this is an example cell
echo"<td class='cell' ColSpan=";
echo(echoTimeDiff('07:00:00','08:00:00')*4);
echo" style=\"cursor: pointer;\" bgcolor='white' >(name of employee)</td>";
foreach($subArray as $i)
{
if($i['beginTime'] < $nextTime)
$nextTime = $i['beginTime'];
}

ive only been working with php and mysql for a few months now, so any help would be greatly appreciated

[Fou-Lu]

My guess is that the MySQL is not outputting the sorted order as you would like it right? I'm not surprised if that is the case, since the times are stored in a string format, it would be trying to sort, but not know exactly what to do with it.
In PHP, the easiest way to do this is to use a usort or uksort method. This allows you to give it a delegate or 'callback' method if you will, that will compare two items on how you want to compare them. I don't want to do this for you as there is no learning in that, but I'll give a quick example instead, just something easy:

function compareTo($a, $b)
{
return $a - $b;
}

$array = array("This", "is", "my", "array");
usort($array, 'compareTo');

Now, to be quite honest since I didn't actually test this I'm not sure what the results are going to be. The standard sort on a primitive data like the strings above will likely sort them for you, case sensitively of course, but the compareTo will be unknown. This is just a simple delegate. Assumably, you would be looking more at comparing two times together, so more along this lines:

function compareTo($a, $b)
{
$aT1 = explode(':', $a);
$aT2 = explode(':', $b);
$result = 0;

$result = $aT1[0] - $aT2[0];
if ($result == 0)
{
$result = $aT1[1] - $aT2[1];
}
if ($result == 0)
{
$result = $aT1[2] - $aT2[2];
}

return $result;

}

How this is handled is of course however your business rules are applied. It considers any timeline less than the current to be lesser. This should allow a uksort to be run on it.
Since you have a result set, you may want to modify the compareTo signature to force the use of arrays. Just stack each result from your query into an array, and access the 'time' option (whatever its called) within the compareTo. To reverse, just swap the $aT1 and $aT2 values.