Find matched data in multidimensional array

Joseph_Sliker

I started this tread in the DATABASE section but am now trying to solve the problem with some type of multi-dimensional array search/parsing.

I need to find kids (based on ID numbers) who have complete sets of test scores (SCORE) for each of n SEASONS.

I extract the SCORES data from a MySQL query into a multidimensional array called $SCORE_ARRAY

$SCORE_ARRAY[ID][SEASON]

When I fetch the array, I can also create single-dimension array to give me just the complete set of student IDs all the SEASON names.

ie.
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$SCORE_ARRAY[$line[SEASON]][$line[ID]] = $line[SCORE];
$SEASONS[$line[SEASON]] = $line[SEASON];
$KIDS[$line[ID]] = $line[ID];
}

All the data seems to go into $SCORE_ARRAY just fine, but I want to ... in a sense... query the array to end up with with a subset exracted from $SCORE_ARRAY (say, $MATCHED_SCORES) that has filtered out all the rows for kids who are missing scores in one or more of the SEASONS.

I feel like I'm getting close here, but am not quite sure how to proceed. It seems like array_intersect would help, but I'm not sure how to apply it here.

Thanks for your help.

bpat1434

You can do this with your query. Just do a join onto itself and search for those that DO have what you want.

Mark McAulay
You can refer to the same table more than once by using table aliases. A common example would be when you have a staff table that contains both staff and manager's details. If you wanted a list of staff, with their corresponding manager's details as well, you would refer to the same table twice.
eg.
SELECT s1.StaffId, s1.StaffName, s1.Position, s1.ManagerId, s2.StaffName AS ManagerName, s2.Position AS ManagerPosition
FROM staff AS s1 INNER JOIN staff AS s2 ON(s1.ManagerId=s2.StaffId)

This can work multiple times, referring back to the same table. You can also join with other tables as normal at the same time, although you need to specify which table alias (and thus which rows, eg. the manager's or the staff's) you would like to join on.

mySQL JOIN syntax

Basically what you'd be doing is writing a JOIN syntax and looking for all rows where there are XX scores or whatever, if not, make it NULL. Then you can loop through and get only those without NULL values.

~Brett

Joseph_Sliker

I think I'm on to something here:

//If the number of scores in the array for each kid is equal to the number of seasons, the dataset must be complete...if that is so, add that kid's ID to a new array called $MATCHED_KIDS, if there are fewer scores, don't add that ID to the new array.

$SEASON_COUNT = COUNT($SEASONS);

foreach ($SCORE_ARRAY AS $key=>$value) {
if (count ($SCORE_ARRAY[$key]) == $SEASON_COUNT)
{$MATCHED_KIDS[] = $key;}
ELSE {;}
}

//the $values in $MATCHED_KIDS could be used in a foreach loop to build the seasonal score arrays I need.

....

but first, lunch!

Joseph_Sliker

bpat...

Thanks for the reply. I was looking at the inner join thing from the very beginning but I didn't quite get it.

The original query is actually a little fussy, because it involves a left join that allows me to collapse the TESTPHASE variables (that I allow users to enter in various ways) into a smaller set of SEASON values.

I will have another look at this from the query end...that would seem to be the most efficient way to approach this.

Thanks