PHP-MySQL: Need Help to Create Multidimensional HTML Table

[Bogey]

July 9th, 2008, 10:26 am

righteous_trespasser wrote:

Yeah, but if you don't show the empty <td>'s you'll end up showing the wrong data for the wrong columns ... for example

Code: [ Download ] [ Select ]

<tr><td>Math</td><td>Biol</td></tr>
<tr><td>0090</td><td>0050</td></tr>
<tr><td>0021</td></tr>

1. <tr><td>Math</td><td>Biol</td></tr>
2. <tr><td>0090</td><td>0050</td></tr>
3. <tr><td>0021</td></tr>

The second person only has a mark for Biol (Biology) but it will show under math because the code didn't add the empty TD ... see where the problem comes in, and then also that code won't validate because the second user's row doesn't contain enough TDs ... get what I'm saying ... ?

Not unless you specify a <tr></tr> the tds won't go to the empty <td> and that is what your first attempt did and my works almost the same way... if I add 0's it makes everything correct...

Even though, phplover doesn't want that solution... no hard feelings R_T

Here is the array generator Named the function because I used R_T's sqls (I actually edited his function to do this... just cleaned out the source a little bit )

PHP Code: [ Download ] [ Select ]

<?php
 function r_t_arrays() {
   $con = mysql_connect('localhost','root','40477810093');
   mysql_select_db('test_exams');
   $sql = mysql_query("SELECT students.student_id, students.student_name, exam.exam_id, exam.exam_name, exam_results.result FROM students LEFT JOIN exam_results ON students.student_id = exam_results.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY student_id, exam_id");
   $sql2 = mysql_query("SELECT DISTINCT exam_name FROM exam ORDER BY exam_id");
   $sql3 = mysql_query("SELECT student_id FROM students");
   $temp1 = "";
   $temp2 = "";
   $count = 0;
   $exams = array();
   // Exam names
   while($row = mysql_fetch_array($sql2))
   {
    $e_val .= $row['exam_name'] .", ";
   }
   // All of students' ids
   while($row = mysql_fetch_array($sql3))
   {
    $s_val .= $row['student_id'] .", ";
   }
   // Scores
   while($row = mysql_fetch_array($sql))
   {
    $sc_val .= $row['result'] .", ";
   }
   $sc_val2 = $sc_val .'"';
   $s_val2 = $s_val .'"';
   $e_val2 = $e_val .'"';
   $exams = array(
             "exam_names" => array(
                            trim($e_val2,', "')
                            ),
             "all_students" => array(
                            trim($s_val2,', "')
                            ),
             "scores" => array(
                            trim($sc_val2,', "')
                            )
             );
   echo "<xmp>";
   print_r($exams);
   echo "</xmp>";
   mysql_close($con);
 }
?>

1. <?php
2.  function r_t_arrays() {
3.    $con = mysql_connect('localhost','root','40477810093');
4.    mysql_select_db('test_exams');
5.    $sql = mysql_query("SELECT students.student_id, students.student_name, exam.exam_id, exam.exam_name, exam_results.result FROM students LEFT JOIN exam_results ON students.student_id = exam_results.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY student_id, exam_id");
6.    $sql2 = mysql_query("SELECT DISTINCT exam_name FROM exam ORDER BY exam_id");
7.    $sql3 = mysql_query("SELECT student_id FROM students");
8.    $temp1 = "";
9.    $temp2 = "";
10.    $count = 0;
11.    $exams = array();
12.    // Exam names
13.    while($row = mysql_fetch_array($sql2))
14.    {
15.     $e_val .= $row['exam_name'] .", ";
16.    }
17.    // All of students' ids
18.    while($row = mysql_fetch_array($sql3))
19.    {
20.     $s_val .= $row['student_id'] .", ";
21.    }
22.    // Scores
23.    while($row = mysql_fetch_array($sql))
24.    {
25.     $sc_val .= $row['result'] .", ";
26.    }
27.    $sc_val2 = $sc_val .'"';
28.    $s_val2 = $s_val .'"';
29.    $e_val2 = $e_val .'"';
30.    $exams = array(
31.              "exam_names" => array(
32.                             trim($e_val2,', "')
33.                             ),
34.              "all_students" => array(
35.                             trim($s_val2,', "')
36.                             ),
37.              "scores" => array(
38.                             trim($sc_val2,', "')
39.                             )
40.              );
41.    echo "<xmp>";
42.    print_r($exams);
43.    echo "</xmp>";
44.    mysql_close($con);
45.  }
46. ?>

The only thing is... I'm not sure how you want the array to look like... there are two different types of info I can store into the "students" sub-array...

July 10th, 2008, 2:43 am

I found it, but it still uses some of the Bogey's first method. (while in while)

Code: [ Download ] [ Select ]

 
<?php
$host="localhost";
$myuser="root";
$mypass="caroline";
$mydb="student_exam";
mysql_connect($host,$myuser,$mypass);
mysql_select_db($mydb);
// this work only in win32, win64, or depending on php.ini setting:
// (mysql functions converted into a variable)
$sql=mysql_query;
$ros=mysql_fetch_array;
//------------------
 
//create headers:
echo "<table border=1 width=100%>
<tr valign=top bgcolor=#eeeeee>
<td>Student</td>";
$sq=$sql("SELECT * FROM exam ORDER by exam_id")or die(mysql_error());
while($headers=$ros($sq))
{
    echo "<td><div align=center>$headers[exam_name]</td></div>";
}
echo "</tr>";
$sq2=$sql("SELECT * FROM students ORDER BY student_id");
// This uses while(s) in while(s), not prefered :=((
while($students=$ros($sq2))
{
    echo "<tr valign=top><td bgcolor=#eeeeee>$students[student_name]</td>";
    // displaying exam result of selected student
    $meetresult=$sql("SELECT s.student_name AS name,e.exam_name AS exam,r.result FROM students s JOIN  exam e LEFT JOIN exam_results r ON r.student_id=s.student_id AND r.exam_id=e.exam_id WHERE s.student_id='$students[student_id]' ORDER BY e.exam_id");
    while($result=$ros($meetresult))
    {
        if(!$result[result])
        {
        // no need insert 0 into database:
            $result[result]=0;
        }
        else
        {
            $result[result]="<b>$result[result]</b>";
        }
        echo "<td><div align=right>$result[result]</div></td>";
    }
    echo "</tr>";
}
echo "</table>";
mysql_close();
?>
 

1.  
2. <?php
3. $host="localhost";
4. $myuser="root";
5. $mypass="caroline";
6. $mydb="student_exam";
7. mysql_connect($host,$myuser,$mypass);
8. mysql_select_db($mydb);
9. // this work only in win32, win64, or depending on php.ini setting:
10. // (mysql functions converted into a variable)
11. $sql=mysql_query;
12. $ros=mysql_fetch_array;
13. //------------------
14.  
15. //create headers:
16. echo "<table border=1 width=100%>
17. <tr valign=top bgcolor=#eeeeee>
18. <td>Student</td>";
19. $sq=$sql("SELECT * FROM exam ORDER by exam_id")or die(mysql_error());
20. while($headers=$ros($sq))
21. {
22.     echo "<td><div align=center>$headers[exam_name]</td></div>";
23. }
24. echo "</tr>";
25. $sq2=$sql("SELECT * FROM students ORDER BY student_id");
26. // This uses while(s) in while(s), not prefered :=((
27. while($students=$ros($sq2))
28. {
29.     echo "<tr valign=top><td bgcolor=#eeeeee>$students[student_name]</td>";
30.     // displaying exam result of selected student
31.     $meetresult=$sql("SELECT s.student_name AS name,e.exam_name AS exam,r.result FROM students s JOIN  exam e LEFT JOIN exam_results r ON r.student_id=s.student_id AND r.exam_id=e.exam_id WHERE s.student_id='$students[student_id]' ORDER BY e.exam_id");
32.     while($result=$ros($meetresult))
33.     {
34.         if(!$result[result])
35.         {
36.         // no need insert 0 into database:
37.             $result[result]=0;
38.         }
39.         else
40.         {
41.             $result[result]="<b>$result[result]</b>";
42.         }
43.         echo "<td><div align=right>$result[result]</div></td>";
44.     }
45.     echo "</tr>";
46. }
47. echo "</table>";
48. mysql_close();
49. ?>
50.  

I hope this could help anybody else (if any).
I still try the multidimensional array
Result:

Code: [ Download ] [ Select ]

Student Biology  Math  Physics  Chemist  Sociology  
John 8 7 0 6 9
Mark 5 8 8 6 7
Donny 7 9 7 0 9
 

1. Student Biology  Math  Physics  Chemist  Sociology  
2. John 8 7 0 6 9
3. Mark 5 8 8 6 7
4. Donny 7 9 7 0 9
5.  

August 13th, 2008, 11:35 pm

Hello,
I have learned all the code.

But I have some problem to print the table if there are only two table like this :

Code: [ Download ] [ Select ]

CREATE TABLE `students` (
`student_id` int(5) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=MyISAM;


INSERT INTO `students` VALUES ('1', 'John');
INSERT INTO `students` VALUES ('2', 'Mark');
INSERT INTO `students` VALUES ('3', 'Donny');

CREATE TABLE `exam` (
`exam_id` int(5) NOT NULL auto_increment,
`student_id` int(5) NOT NULL,
`exam_name` varchar(10) NOT NULL,
`result` int(5) NOT NULL,
PRIMARY KEY (`id2`)
) ENGINE=MyISAM;

INSERT INTO `exam` VALUES ('', '1', 'Bio', '7');
INSERT INTO `exam` VALUES ('', '1', 'Phys', '8');
INSERT INTO `exam` VALUES ('', '1', 'Math', '8');
INSERT INTO `exam` VALUES ('', '2', 'Bio', '6');
INSERT INTO `exam` VALUES ('', '2', 'Phys', '7');
INSERT INTO `exam` VALUES ('', '2', 'Math', '8');
INSERT INTO `exam` VALUES ('', '3', 'Bio', '8');
INSERT INTO `exam` VALUES ('', '3', 'Phys', '6');
INSERT INTO `exam` VALUES ('', '3', 'Math', '7');

1. CREATE TABLE `students` (
2. `student_id` int(5) NOT NULL auto_increment,
3. `name` varchar(50) NOT NULL,
4. PRIMARY KEY (`student_id`)
5. ) ENGINE=MyISAM;
8. INSERT INTO `students` VALUES ('1', 'John');
9. INSERT INTO `students` VALUES ('2', 'Mark');
10. INSERT INTO `students` VALUES ('3', 'Donny');
12. CREATE TABLE `exam` (
13. `exam_id` int(5) NOT NULL auto_increment,
14. `student_id` int(5) NOT NULL,
15. `exam_name` varchar(10) NOT NULL,
16. `result` int(5) NOT NULL,
17. PRIMARY KEY (`id2`)
18. ) ENGINE=MyISAM;
20. INSERT INTO `exam` VALUES ('', '1', 'Bio', '7');
21. INSERT INTO `exam` VALUES ('', '1', 'Phys', '8');
22. INSERT INTO `exam` VALUES ('', '1', 'Math', '8');
23. INSERT INTO `exam` VALUES ('', '2', 'Bio', '6');
24. INSERT INTO `exam` VALUES ('', '2', 'Phys', '7');
25. INSERT INTO `exam` VALUES ('', '2', 'Math', '8');
26. INSERT INTO `exam` VALUES ('', '3', 'Bio', '8');
27. INSERT INTO `exam` VALUES ('', '3', 'Phys', '6');
28. INSERT INTO `exam` VALUES ('', '3', 'Math', '7');

I want to print the table like phplover want.
please help me...
Sorry if my question is too basic...i'm a newbie.

Thx before

August 14th, 2008, 12:37 am

In addition,
Format of the table :
name as column and exam_name as row.

[righteous_trespasser]

August 14th, 2008, 1:06 am

Code: [ Download ] [ Select ]

$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
$old_user = 0;
$new_user = 0;
while ($row = mysql_fetch_array($sql))
{
$new_user = $row['student_id'];
if ($old_user == $new_user)
{
echo $row['result'];
}
else
{
echo $row['name'] . $row['result'];
}
$old_user = $row['student_id'];
}

1. $con = mysql_connect("localhost","root","mypass");
2. mysql_select_db("mydb");
3. $sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
4. $old_user = 0;
5. $new_user = 0;
6. while ($row = mysql_fetch_array($sql))
7. {
8. $new_user = $row['student_id'];
9. if ($old_user == $new_user)
10. {
11. echo $row['result'];
12. }
13. else
14. {
15. echo $row['name'] . $row['result'];
16. }
17. $old_user = $row['student_id'];
18. }

August 14th, 2008, 2:42 am

Thx for reply.

I have added table tag in your code, R_T. But the result is like this :

Donny 8 6 7
John 7 8 8
Mark 6 7 8

What I mean how if the result like this :

Exam Donny John Mark
Bio 8 7 6
Phys 6 8 7
Math 7 8 8

This is your code that I have added table tag :

Code: [ Download ] [ Select ]

<?
$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
$old_user = 0;
$new_user = 0;
echo "<table><tr><td>Exam</td>";
while ($row = mysql_fetch_array($sql))
{
$new_user = $row['student_id'];
if ($old_user == $new_user)
{
echo "<td>" .$row['result']. "</td>";
}
else
{
echo "</tr><tr><td>" .$row['name']. "</td><td>" .$row['result']. "</td>";
}
$old_user = $row['student_id'];
}
echo "</table>";
?>

1. <?
2. $con = mysql_connect("localhost","root","mypass");
3. mysql_select_db("mydb");
4. $sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
5. $old_user = 0;
6. $new_user = 0;
7. echo "<table><tr><td>Exam</td>";
8. while ($row = mysql_fetch_array($sql))
9. {
10. $new_user = $row['student_id'];
11. if ($old_user == $new_user)
12. {
13. echo "<td>" .$row['result']. "</td>";
14. }
15. else
16. {
17. echo "</tr><tr><td>" .$row['name']. "</td><td>" .$row['result']. "</td>";
18. }
19. $old_user = $row['student_id'];
20. }
21. echo "</table>";
22. ?>

please help. Thx.

[righteous_trespasser]

August 14th, 2008, 3:04 am

Code: [ Download ] [ Select ]

$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$sql = mysql_query("SELECT DISTICT name FROM users");
echo "<table><tr><td></td>";
while ($row = mysql_fetch_array($sql))
{
echo "<td>" . $row['name'] . "</td>";
}
$sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
$old_user = 0;
$new_user = 0;
echo "</tr><tr>";
while ($row = mysql_fetch_array($sql))
{
$new_user = $row['student_id'];
if ($old_user == $new_user)
{
echo "<td>" . $row['result'] . "</td>";
}
else
{
echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
}
$old_user = $row['student_id'];
}
echo "</tr></table>";

1. $con = mysql_connect("localhost","root","mypass");
2. mysql_select_db("mydb");
3. $sql = mysql_query("SELECT DISTICT name FROM users");
4. echo "<table><tr><td></td>";
5. while ($row = mysql_fetch_array($sql))
6. {
7. echo "<td>" . $row['name'] . "</td>";
8. }
9. $sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY student.name, exam.exam_name");
10. $old_user = 0;
11. $new_user = 0;
12. echo "</tr><tr>";
13. while ($row = mysql_fetch_array($sql))
14. {
15. $new_user = $row['student_id'];
16. if ($old_user == $new_user)
17. {
18. echo "<td>" . $row['result'] . "</td>";
19. }
20. else
21. {
22. echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
23. }
24. $old_user = $row['student_id'];
25. }
26. echo "</tr></table>";

That should work, now please note that this is written off the top of my head and there could be an error somewhere ... maybe ...

August 14th, 2008, 6:44 am

This is the result :

Exam John Mark Donny
bio 8 6 7
bio 7 8 8
bio 6 7 8

Exam name are all bio. And location of 'result' not correct.
I've tried to modify the code, but still dont get the correct result.
Or maybe should use another method for two table?

[righteous_trespasser]

August 14th, 2008, 6:58 am

Code: [ Download ] [ Select ]

$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$sql = mysql_query("SELECT DISTICT name FROM users ORDER BY student.name");
echo "<table><tr><td></td>";
while ($row = mysql_fetch_array($sql))
{
echo "<td>" . $row['name'] . "</td>";
}
$sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY exam.exam_name, student.name");
$old_user = 0;
$new_user = 0;
echo "</tr><tr>";
while ($row = mysql_fetch_array($sql))
{
$new_user = $row['student_id'];
if ($old_user == $new_user)
{
echo "<td>" .  $row['result'] . "</td>";
}
else
{
echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
}
$old_user = $row['student_id'];
}
echo "</tr></table>";

1. $con = mysql_connect("localhost","root","mypass");
2. mysql_select_db("mydb");
3. $sql = mysql_query("SELECT DISTICT name FROM users ORDER BY student.name");
4. echo "<table><tr><td></td>";
5. while ($row = mysql_fetch_array($sql))
6. {
7. echo "<td>" . $row['name'] . "</td>";
8. }
9. $sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY exam.exam_name, student.name");
10. $old_user = 0;
11. $new_user = 0;
12. echo "</tr><tr>";
13. while ($row = mysql_fetch_array($sql))
14. {
15. $new_user = $row['student_id'];
16. if ($old_user == $new_user)
17. {
18. echo "<td>" .  $row['result'] . "</td>";
19. }
20. else
21. {
22. echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
23. }
24. $old_user = $row['student_id'];
25. }
26. echo "</tr></table>";

Try now ...

August 14th, 2008, 7:14 am

Sorry to bother you, but still dont work.

Donny John Mark
bio 8
bio 7
bio 6
phys 6
phys 8
phys 7
math 7
math 8
math 8

[righteous_trespasser]

August 14th, 2008, 7:22 am

Code: [ Download ] [ Select ]

$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$sql = mysql_query("SELECT DISTICT name FROM users ORDER BY student.name");
echo "<table><tr><td></td>";
while ($row = mysql_fetch_array($sql))
{
echo "<td>" . $row['name'] . "</td>";
}
$sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY exam.exam_name, student.name");
$old_exam = 0;
$new_exam = 0;
echo "</tr><tr>";
while ($row = mysql_fetch_array($sql))
{
$new_exam = $row['exam_name'];
if ($old_exam == $new_exam)
{
echo "<td>" .  $row['result'] . "</td>";
}
else
{
echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
}
$old_exam = $row['exam_name'];
}
echo "</tr></table>";

1. $con = mysql_connect("localhost","root","mypass");
2. mysql_select_db("mydb");
3. $sql = mysql_query("SELECT DISTICT name FROM users ORDER BY student.name");
4. echo "<table><tr><td></td>";
5. while ($row = mysql_fetch_array($sql))
6. {
7. echo "<td>" . $row['name'] . "</td>";
8. }
9. $sql = mysql_query("SELECT * FROM exam LEFT JOIN students ON exam.student_id = students.student_id ORDER BY exam.exam_name, student.name");
10. $old_exam = 0;
11. $new_exam = 0;
12. echo "</tr><tr>";
13. while ($row = mysql_fetch_array($sql))
14. {
15. $new_exam = $row['exam_name'];
16. if ($old_exam == $new_exam)
17. {
18. echo "<td>" .  $row['result'] . "</td>";
19. }
20. else
21. {
22. echo "</tr><tr><td>" $row['exam_name'] . "</td><td>" . $row['result'] . "</td>";
23. }
24. $old_exam = $row['exam_name'];
25. }
26. echo "</tr></table>";

There

August 14th, 2008, 8:00 am

Not yet.

[righteous_trespasser]

August 14th, 2008, 9:54 am

Okay but what's wrong ... ? I don't have this live and running somewhere so I can't test it ...

August 14th, 2008, 8:13 pm

This is the result :

Exam John Mark Donny
8 7 6
Phys 6 8 7
Math 7 8 8

Is the problem in the table tag or in the mysql join and array?

How to print the data with this sequences :

Exam | John | Mark | Donny
Bio | 1 | 4 | 7
Phys | 2 | 5 | 8
Math | 3 | 6 | 9

1, 2, 3, ... = print sequences

Thx for help.

[righteous_trespasser]

August 14th, 2008, 10:26 pm

I don't know what you're talking about print sequences, but I'll have to try it my self later on, I just don't have time now ...