Multi-Dimensional Array Problem

Need to display results from several tables, iterating through arrays

[PianoGuy]

Hi, all --

I'm new to this, and I'm not sure that I'm thinking about this particular problem correctly. This is compounded by the fact that I'm tired and I think my brain shut down sometime last week. But...

I'm trying to create a report on a database that draws from several different tables. The way that I'm thinking this should go is as follows:

I need to set an array of:

Array(
[PROG_ID] => [STUD_ID] => LAST_FIRST_MI, Array(STUDENT_ATTENDANCE data} and Array(STUDENT_PERFORMANCE data)
)
and display it by generating a new table for each Array[PROG_ID] within which I generate a new <TR> for each change in Array[STUD_ID], where I display the data associated with each student.

... and I need to create an array of
Array(
[PROG_ID] => Array[BENCHMARKS]
)

I'm just having trouble seeing how to get there, and the approach I took was pretty far from working. Here's the overview:

Tables (only the info needed from the queries):

PROGRAMS (PROG_ID, PROG_NAME)
STUDENT_CONTACT (LAST_FIRST_MI, ELIGIBILITY)
STUDENT_PROGRAM (STUD_ID, PROG_ID)
STUDENT_ATTENDANCE (STUD_ID, PROG_ID, DATE, ATTEND)
BENCHMARKS (PROG_ID, BM1_NAME, BM1_DESC, etc. thru BM5)
STUDENT_PERFORMANCE (STUD_ID, PROG_ID, BM1_PASS, etc.)
(there are 5 benchmarks in the above table)

I need to create a page that displays the following:

Program Name 1
STUDENT ATTENDANCE BM1 BM2 BM3
Student1 x% Y N N
Student2 y% Y Y Y

Program Name 2 (same as above)

...where percentage attended =
total ATTEND="TRUE"/total rows for student

AND

Total Attendance for each program
(total # of classes, percentage of total, total # of students attending)

AND

A key to the page that states the following:

BENCHMARKS FOR [PROGRAM_NAME]:
BM1 BM1_DESCRIPTION
BM2 BM2_DESCRIPTION
BM3 BM3_DESCRIPTION
BM4 BM4_DESCRIPTION
BM5 BM5_DESCRIPTION

As I said, I'm not sure I'm even thinking about the problem correctly, but this would seem to be the most efficient way to solve it. I'm just unsure of how to iterate through these arrays using while() or foreach() to grab the applicable information from the database queries (PROG_ID = $Current_PROG_ID and STUD_ID = $Current_STUD_ID). I've been searching for examples of stuff that addresses this problem, but haven't seen it. I hope this wasn't too long...

PianoGuy

[ergophobe]

There are a number of ways to approach this. We could, for example, get all the info we need using a GROU BY clause and having two rows per student (one would give the total attended and one the total missed).

Conceptually simpler, though, I think is just to get all the info about student, program and benchmarks and then use a simple function to get the attendance rate. This makes for a lot more queries, but presuambly these reports won't be getting run many times per second.

So let's write a function to get attendance

function getAttendance($sid, $pid) 
{ 
 $total = 0; 
 $attend = 0; 
 // get total classes 
 $query = "SELECT COUNT(*) FROM STUDENT_ATTENDANCE WHERE STUD_ID LIKE '$sid' AND PROG_ID LIKE $pid"; 
 $result = mysql_query($query); 
 $total = myslq_result($result, 0, 0); 
 // now get count for those attended 
 $query = "SELECT COUNT(*) FROM STUDENT_ATTENDANCE WHERE STUD_ID LIKE '$sid' AND PROG_ID LIKE $pid 
  AND ATTEND LIKE '1'"; 
 $result = mysql_query($query); 
 $attended = myslq_result($result, 0, 0); 
 if ($total)  
 { 
 return ($attend/$total) * 100; 
 } 
 else 
 { 
 return 0; 
 } 
} 

Now we just need to grab the program name and ID, student name and ID and the benchmarks and use our function to get the attendance rate (fill in the ... with your BM_1 through BM_5)

$query = " 
SELECT p.PROG_ID, p.PROG_NAME, s.STUD_ID, s.LAST_FIRST_MI, sp.BM1_PASS, ... sp.BM5_PASS 
FROM PROGRAMS p, STUDENT_CONTACT s, STUDENT_PERFORMANCE sp 
WHERE p.pid = sp.pid AND s.sid=sp.sid 
ORDER BY p.pid, s.LAST_FIRST_MI"; 
$result = mysql_query($query); 
$table = "<table>"; 
$colspan = 7; // number of cols in student output row 
$program = ""; 
while ($row = mysql_fetch_assoc($result)) 
{ 
 if ($program!= $row['PROG_NAME']) 
 { 
  $program = $row['PROG_NAME'] 
  $table .= '<tr class="program"><td colspan="' . $colspan . '">'. $program . '</td></tr>'; 
 } 
 $table .= '<tr class="student"> 
    <td>' . $row['LAST_FIRST_MI'] . '</td> 
    <td>' . getAttendance($row['STUD_ID'], $row['PROG_ID']). '%</td> 
    <td>' . $row['BM1_PASS'] . '</td> 
// MORE BENCHMARKS 
    </tr>'; 
} 
$table .= '</table>'; 

That should at least get you started. That is not tested, probably contains some typos, perhaps some logic errors and no error checking, so it's a skeletal script, but get it running to your specs and then we can move on to other aspects if needed.