Normalization of multiple associative arrays

[ducc]

OK, I could use some help please.

I have a mysql table that I use for scheduling employees with the structure:

schedule_id employee_id request_date approved slot_id flags phase_added

This structure is great for almost all of my needs, except creating a easily readable weekly schedule. The data gets displayed on the screen very well, but printing the display is ‘messy’. I need to create a file than can be opened in excel and easily printed.

I have code to create the schedule items for a particular day:
[php]
//Get the data for putting into the new table
$query = “SELECT employee_id, slot_id, flags FROM schedule WHERE request_date = ‘$day_two’ ORDER BY FIELD (slot_id, 1, 2, 3, 4, 5, 6, 7, 8, 57 ,58 ,10 ,9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 59, 61, 41, 42, 43, 44, 47, 49, 45, 46)”;
$result = mysql_query($query);
$result = mysql_query($query) or die(mysql_error());
for($i = 0; $array[$i] = mysql_fetch_assoc($result); $i++) ;
array_pop($array);
//Let’s find out what $i equaled
$array_rows = array_values($array);
$array_rows_number = count($array_rows);
echo $array_rows_number."
";
[/php]

Which give me an assoc array that looks like this (but with many more entries):
[0] => Array
(
[employee_id] => Carla Carter
[slot_id] => 1
[flags] =>
)

Here’s the problem, slot_id are not unique. For example [slot_id] => 45 denotes someone on vacation. There can 0 to 25 employees on vacation for given day. Which mean that as I loop through my selection: WHERE request_date = 'each day in a week’; my value for $array_rows_number changes.
I think what I want to do is place space holders in each array with no values for [employee_id], or [flags] based on the max number of [slot_id] entries I find. That way I can just insert the array into a table and down load a .csv file. And I know that only 3 [slot_id]s will have multiple (10, 45, and 46).

Question1: Am I correct about my plan of action? If so, how do I proceed?

Additionally in my $query I use ORDER BY FIELD. But I already have this order stored in a separate table with the structure:

slot_id slot_name

I put these values into an array so I can change number to names in another part of my code:
[php]
$shift_order = array();
// $shift_order2 = array();
$query = “SELECT slot_id, slot_name FROM shift_order”;
// $query = “SELECT slot_id FROM shift_order”;
$result = mysql_query($query);
while(list($k, $v) = mysql_fetch_array($result)) $shift_order[$k] = $v;
$num_results = mysql_num_rows($result);
// for($i=0; $i<$num_results; $i++)
// while($shift_order2 = mysql_fetch_row($result))
echo ‘

’;

print_r($shift_order);

echo ‘

’;
// echo $shift_order2."
";
// echo ‘

’;

//    print_r($shift_order2);

//    echo ‘

’;
[/php]

I had intend to use an array (note the commented out lines) to sort the ORDER BY FIELD, instead of typing out everything manually; but I had a bunch of trouble. I was trying variations on:
$query = "SELECT employee_id, slot_id, flags FROM schedule WHERE request_date = ‘$day_two’ ORDER BY FIELD (slot_id, $shift_order2)”
Question 2: Can somebody suggest a reason why I was unable to sort by the array ?

[ducc]

OK…I agree that was confusing.

But I have made the issue much simpler.

I can create the assoc array: $array. But in 46 3 item arrays I have
duplicates of the second entry [slot_id]. Here’s an example:

[pre][42] => Array
(
[employee_id] => Adam
[slot_id] => 43
[flags] =>
)
[43] => Array
(
[employee_id] => Kelley
[slot_id] => 45
[flags] =>
)
[44] => Array
(
[employee_id] => Todd
[slot_id] => 45
[flags] =>
)
[45] => Array
(
[employee_id] => Brad
[slot_id] => 45
[flags] =>
[56] => Array
(
[employee_id] => Pat
[slot_id] => 46
[flags] =>
)

[/pre]
Where [slot id] = 45 represents several people on vacation
I want to remove and store all but the first unique entry per Array.

Using:

[php]
$new = array();
$exclude = array("");
for ($i = 0; $i<=count($array)-1; $i++) {
if (!in_array(trim($array[$i][“slot_id”]) ,$exclude)) { $new[] = $array[$i]; $exclude[] = trim($array[$i][“slot_id”]); }
}[/php]

I can get the array $new, which gives me:

[pre][41] => Array
(
[employee_id] => Adam
[slot_id] => 43
[flags] =>
)
[42] => Array
(
[employee_id] => Kelley
[slot_id] => 45
[flags] =>
)
[43] => Array
(
[employee_id] => Pat
[flags] =>
)
[/pre]
Which is great, and I was hoping that exclude would give me all the rest in the same format; but all I get is:

Array ( [0] => [1] => 1 [2] => 2 [3] => 3 [4] => 4 [5] => 5 [6] => 6 [7] => 7 [8] => 8 [9] => 57 [10] => 58 [11] => 10 [12] => 9 [13] => 11 [14] => 12 [15] => 13 [16] => 15 [17] => 16 [18] => 17 [19] => 18 [20] => 19 [21] => 20 [22] => 21 [23] => 22 [24] => 23 [25] => 24 [26] => 25 [27] => 26 [28] => 27 [29] => 28 [30] => 29 [31] => 30 [32] => 31 [33] => 32 [34] => 33 [35] => 34 [36] => 35 [37] => 36 [38] => 37 [39] => 38 [40] => 41 [41] => 42 [42] => 43 [43] => 45 [44] => 46 )

And if I can get $exclude to look like (note that I am showing an addition dupe not in the slice of example :
[pre][11] => Array
(
[employee_id] => Justin
[slot_id] => 10
[flags] => HEARTS
[44] => Array
(
[employee_id] => Todd
[slot_id] => 45
[flags] =>
)
[45] => Array
(
[employee_id] => Brad
[slot_id] => 45
[flags] => [/pre]
I would then like to get these:

$slot10 = (‘useradded$’, Justin.HEARTS)

$slot45 = ('useradded$, Todd., Brad.)

Where I have Concatenated the employee_id and Flag keys.

Any help now?