displaying html table help needed

[thatsme2]

Hi,

 

I am trying for the past few days in various ways to solve the below problem.  Please give me a solution.

 

I have a the following db tables

member_table

test_results

 

member_table structure

member_id (pk) auto inc

member_fname

member_lname

member_test_taken_flag

 

test_results structure

test_results_id(pk) auto inc

member_id(fk)

test_id

test_score

 

I have to display the above data in the format,

 

<table border='1'>

<tr>

<td>Members</td>

<td>Member1</td>

<td>Member2</td>

<td>Member3</td>

<td>Member4</td>

<td>Membern</td>

</tr>

<tr>

  <td>Tests</td>

  <td> </td>

  <td> </td>

  <td> </td>

  <td> </td>

  <td> </td>

</tr>

<tr>

  <td>test1</td>

  <td>member1score</td>

  <td>member2score</td>

  <td>member3score</td>

  <td>member4score</td>

  <td>membernscore</td>

</tr>

 

<tr>

  <td>test2</td>

  <td>member1score</td>

  <td>member2score</td>

  <td>member3score</td>

  <td>member4score</td>

  <td>membernscore</td>

</tr>

 

 

<tr>

  <td>test3</td>

  <td>member1score</td>

  <td>member2score</td>

  <td>member3score</td>

  <td>Not taken</td>

  <td>membernscore</td>

</tr>

 

<tr>

  <td>test4</td>

  <td>member1score</td>

  <td>Not taken</td>

  <td>member3score</td>

  <td>member4score</td>

  <td>membernscore</td>

</tr>

 

<tr>

  <td>testn</td>

  <td>Not taken</td>

  <td>member2score</td>

  <td>member3score</td>

  <td>member4score</td>

  <td>membernscore</td>

</tr>

</table>

 

 

Thanks

[benjaminbeazy]

<?php
$sql = "SELECT * FROM `test_results`";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)){
  $test_results[$row['member_id']][$row['test_id']][] = $row;
}
$sql = "SELECT * FROM `member_table` SORT BY `member_lname` ASC, `member_fname` ASC";
$result = mysql_query($sql) or die(mysql_error());
?>
<table>
<tr>
<td>Member</td>
<?php
while($row = mysql_fetch_array($result)){
  $members[] = $row;
  echo "<td>$row['member_lname'], $row['member_fname']</td>";
}
?>
</tr>
<tr><td>test1</td>
<?
foreach($members as $val){
  echo "<td>$test_results[$members[$val]['member_id']][1]['test_score']</td>
<td>$test_results[$members[$val]['member_id']][2]['test_score']</td>
<td>$test_results[$members[$val]['member_id']][3]['test_score']</td>
<td>$test_results[$members[$val]['member_id']][4]['test_score']</td>
<td>$test_results[$members[$val]['member_id']][5]['test_score']</td>";
}

 

thats really ugly but it should work.. im tired..

[thatsme2]

i am getting error,

 

Parse error: parse error, unexpected '[', expecting ']' in D:\Practice\member_details.php on line 106

 

 

line 106 is,

echo "<td>$test_results[$members[$val]['member_id']][1]['test_score']</td>

 

[redarrow]

guessing here not at home sorry...

<?php
foreach($members as $val){
  echo "<td>".$test_results[$members[$val]['member_id']][1]['test_score']."</td>
<td>".$test_results[$members[$val]['member_id']][2]['test_score']."</td>
<td>".$test_results[$members[$val]['member_id']][3]['test_score']."</td>
<td>".$test_results[$members[$val]['member_id']][4]['test_score']."</td>
<td>".$test_results[$members[$val]['member_id']][5]['test_score']."</td>";
}
?>

[Barand]

Any number of members, any number of tests. Assumes if a test id not taken then there is no record in the results table for that member/test

<?php
mysql_connect('localhost');
mysql_select_db('test3');

$sql = "SELECT member_id, member_fname 
        FROM member_table
        ORDER BY member_id";
$res = mysql_query($sql);
    /**
    * set up arrays
    */
$inital = array();
$members = array();                              
while (list($id, $fn) = mysql_fetch_row($res))
{
    $members[$id] = $fn;
    $initial[$id] = '--';                        // array of "not taken" for each member 
}
    /**
    * headings
    */
echo "<table border='1'>\n";
echo '<tr><th> </th><th>' . join ('</th><th>', $members) . '</th></tr>';
    /**
    * results
    */

$sql = "SELECT member_id, test_id, test_score
        FROM test_results
        ORDER BY test_id";
$res = mysql_query($sql);
$prevt = 0;
$scores = $initial;
while (list($mid, $tid, $score) = mysql_fetch_row($res))
{
    if ($prevt != $tid)
    {
        if ($prevt != 0)
        {
            echo "<tr><th>TEST $prevt</th><td>" . join ('</td><td>', $scores) . '</td></tr>';
        }
        $prevt = $tid;
        $scores = $initial;
    }
    // store results
    $scores[$mid] = $score;
}
echo "<tr><th>TEST $prevt</th><td>" . join ('</td><td>', $scores) . '</td></tr>';

?>

[thatsme2]

Thanks Barand,

 

  Your code is working perfectly.  I want to display test_names along with test_id

modified code,

 

//added test_name
$sql = "SELECT tr.member_id, tr.test_id, tr.test_score, t.test_name 
        FROM test_results tr, tests t WHERE tr.test_id=t.test_id
        ORDER BY test_id";
$res = mysql_query($sql);
$prevt = 0;
$scores = $initial;
while (list($mid, $tid, $score, $test_name) = mysql_fetch_row($res))//Added $test_name
{
    if ($prevt != $tid)
    {
        if ($prevt != 0)
        {
            echo "<tr><th>TEST $prevt $test_name</th><td>" . join ('</td><td>', $scores) . '</td></tr>';//added test_name
        }
        $prevt = $tid;
        $scores = $initial;
    }
    // store results
    $scores[$mid] = $score;
}
echo "<tr><th>TEST $prevt $test_name</th><td>" . join ('</td><td>', $scores) . '</td></tr>'; //added test_name

?>

 

I am getting only one test_name displayed.

 

 

Thanks

[Barand]

When reading the results and we get to new test, the array of scores is for the previous test_id, which is why we print "$prevt". We need to do the same with testname, store it then print it with scores.

<?php
mysql_connect('localhost');
mysql_select_db('test3');

$sql = "SELECT member_id, member_fname 
        FROM member_table
        ORDER BY member_id";
$res = mysql_query($sql);
    /**
    * set up arrays
    */
$inital = array();
$members = array();                              
while (list($id, $fn) = mysql_fetch_row($res))
{
    $members[$id] = $fn;
    $initial[$id] = '--';                        // array of "not taken" for each member 
}
    /**
    * headings
    */
echo "<table border='1'>\n";
echo '<tr><th> </th><th>' . join ('</th><th>', $members) . '</th></tr>';
    /**
    * results
    */

$sql = "SELECT tr.member_id, tr.test_id, tr.test_score, t.test_name
        FROM test_results tr
            JOIN tests t ON tr.test_id = t.test_id
        ORDER BY test_id";
$res = mysql_query($sql);
$prevt = 0;
$prevname = '';
$scores = $initial;
while (list($mid, $tid, $score, $tname) = mysql_fetch_row($res))
{
    if ($prevt != $tid)
    {
        if ($prevt != 0)
        {
            echo "<tr><th>TEST $prevt $prevname</th><td>" . join ('</td><td>', $scores) . '</td></tr>';
        }
        $prevt = $tid;
        $prevname = $tname;
        $scores = $initial;
    }
    // store results
    $scores[$mid] = $score;
}
echo "<tr><th>TEST $prevt $prevname</th><td>" . join ('</td><td>', $scores) . '</td></tr>';

?>