PHP-MySQL: Need Help to Create Multidimensional HTML Table
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
I edited my code a tiny bit and fixed that error, and came up with this ... it's not quite right yet, as you can see, John doesn't have Physics but it doesn't skip that row, and the same goes for Donny that doesn't have Chemist, so that row doesn't get skipped ... the only way to get around this without adding heaps of code is to actually just insert a 0 for each exam that wasn't taken ... then that data would display correctly ...
Let's leave all our *plum* where it is and go live in the jungle ...
- Anonymous
- Bot


- Joined: 25 Feb 2008
- Posts: ?
- Loc: Ozzuland
- Status: Online
July 9th, 2008, 12:14 am
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
Congrat Bogey! You got my double-smile

Your latest code is work, and ALMOST reach my need.
Thanks to Bogey, thanks to righteous_trespasser for your interests on helping me.
for righteous_trespasser, your latest code (in this forum, not in http://ohdesignx.co.za/exam/temp.php) displayed nothing in my browser, although i have modified again.
I didn't know what's my wrong, i have modified your code to be match with my database.
for Bogey:
I used Your script:
<?php
function exams() {
$con = mysql_connect('localhost','root','myrootpassword') or die(mysql_error());
$select = mysql_select_db('dtbs') or die(mysql_error());
if($con && $select)
{
$sql = "SELECT `exam_name` FROM exam ORDER BY exam_id";
$exams = mysql_query($sql);
// Get the table started
$table = "<table border=\"1px\" style=\"width: 100%;\">\n\t<tr>\n\t\t<td>\n\t\t\t<p>Student</p>\n\t\t</td>\n";
while($exam = mysql_fetch_assoc($exams))
{
$e_name = $exam['exam_name'];
$table .= "\t\t<td>\n\t\t\t<p>{$e_name}</p>\n\t\t</td>\n";
}
$table .= "\t</tr>\n";
$students = mysql_query("SELECT * FROM students");
while($student = mysql_fetch_assoc($students))
{
$st_id = $student['student_id'];
$st_name = $student['student_name'];
$process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
$table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
while($row = mysql_fetch_assoc($process))
{
$score = $row['result'];
$table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
}
$table .= "\t</tr>\n";
}
$table .= "</table>";
echo $table;
}
}
// Call the function you have made above :)
exams();
?>
and calling my database:
-- ----------------------------
-- Table structure for exam
-- ----------------------------
CREATE TABLE `exam` (
`exam_id` int(10) NOT NULL auto_increment,
`exam_name` varchar(50) NOT NULL,
PRIMARY KEY (`exam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `exam` VALUES ('1', 'Biology');
INSERT INTO `exam` VALUES ('2', 'Math');
INSERT INTO `exam` VALUES ('3', 'Physics');
INSERT INTO `exam` VALUES ('4', 'Chemist');
INSERT INTO `exam` VALUES ('5', 'Sociology');
-- ----------------------------
-- Table structure for exam_results
-- ----------------------------
CREATE TABLE `exam_results` (
`id` int(10) NOT NULL auto_increment,
`student_id` int(10) NOT NULL,
`exam_id` int(10) NOT NULL,
`result` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `exam_results` VALUES ('1', '1', '1', '8');
INSERT INTO `exam_results` VALUES ('2', '1', '2', '7');
INSERT INTO `exam_results` VALUES ('3', '1', '4', '6');
INSERT INTO `exam_results` VALUES ('4', '1', '5', '9');
INSERT INTO `exam_results` VALUES ('5', '2', '1', '5');
INSERT INTO `exam_results` VALUES ('6', '2', '2', '8');
INSERT INTO `exam_results` VALUES ('7', '2', '3', '8');
INSERT INTO `exam_results` VALUES ('8', '2', '4', '6');
INSERT INTO `exam_results` VALUES ('9', '2', '5', '7');
INSERT INTO `exam_results` VALUES ('10', '3', '1', '7');
INSERT INTO `exam_results` VALUES ('11', '3', '2', '9');
INSERT INTO `exam_results` VALUES ('12', '3', '3', '7');
INSERT INTO `exam_results` VALUES ('13', '3', '5', '9');
-- ----------------------------
-- Table structure for students
-- ----------------------------
CREATE TABLE `students` (
`student_id` int(10) NOT NULL auto_increment,
`student_name` varchar(50) NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `students` VALUES ('1', 'John');
INSERT INTO `students` VALUES ('2', 'Mark');
INSERT INTO `students` VALUES ('3', 'Donny');
1. Some data was stored in wrong columns:
John should have no "Physics" exam result, not "Sociology".
So do Donny, he has no "Chemist" exam result, not "Sociology"
2. Your code needs too much MySQL queries. If we work w/ big-size data, our server will be "timed out || not responding || data will be loaded uncompletely".
For example; we have 1,000,000,000 students, and 50 exams.
we need to request exam_result.result for 50,000,000,000 times
(but I wont to try this
)
You use looping inside a looping (while in while).
$students = mysql_query("SELECT * FROM students");
while($student = mysql_fetch_assoc($students))
{
...
$process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
$table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
while($row = mysql_fetch_assoc($process))
{
...
Would you write the data in array to make it simple?
$line[1]="$row[John] </td><td>$row[John][Biology]</td><td>$row[John][Math]...";
I mean, combining (w/ some modification, offcourse) MySQL Queries w/ script like this:
<?
$array = array(
array("App Name 1", "App Name 2", "App Name 3", "App Name 4", "App Name 5", "App Name 6"),
array("department 1", "App Name 2", "App Name 5", "App Name 6"),
array("department 2", "App Name 1", "App Name 2", "App Name 4"),
array("department 3", "App Name 1", "App Name 2", "App Name 3", "App Name 5", "App Name 6"),
);
//headers:
$headers = $array[0];
echo "<table border=1>
<tr>
<td>Department</td>\n";
foreach ( $headers AS $header ) {
echo "\t\t<td>$header</td>\n";
}
echo "\t</tr>\n";
//loop through the remainder of the array
$end = count($array);
for ( $p = 1; $p < $end; $p++ ) {
//echo the department:
echo "\t<tr>
<td>" . $array[$p][0] . "</td>\n";
//loop through the headers, print an X in the td if present in $array[$p]
foreach ( $headers AS $header ) {
echo "\t\t<td>";
if ( in_array($header, $array[$p]) ) {
echo "X";
} else {
echo " ";
}
echo "</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>";
?>
Thanks brothers!
Your latest code is work, and ALMOST reach my need.
Thanks to Bogey, thanks to righteous_trespasser for your interests on helping me.
for righteous_trespasser, your latest code (in this forum, not in http://ohdesignx.co.za/exam/temp.php) displayed nothing in my browser, although i have modified again.
I didn't know what's my wrong, i have modified your code to be match with my database.
for Bogey:
I used Your script:
Code: [ Select ]
<?php
function exams() {
$con = mysql_connect('localhost','root','myrootpassword') or die(mysql_error());
$select = mysql_select_db('dtbs') or die(mysql_error());
if($con && $select)
{
$sql = "SELECT `exam_name` FROM exam ORDER BY exam_id";
$exams = mysql_query($sql);
// Get the table started
$table = "<table border=\"1px\" style=\"width: 100%;\">\n\t<tr>\n\t\t<td>\n\t\t\t<p>Student</p>\n\t\t</td>\n";
while($exam = mysql_fetch_assoc($exams))
{
$e_name = $exam['exam_name'];
$table .= "\t\t<td>\n\t\t\t<p>{$e_name}</p>\n\t\t</td>\n";
}
$table .= "\t</tr>\n";
$students = mysql_query("SELECT * FROM students");
while($student = mysql_fetch_assoc($students))
{
$st_id = $student['student_id'];
$st_name = $student['student_name'];
$process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
$table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
while($row = mysql_fetch_assoc($process))
{
$score = $row['result'];
$table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
}
$table .= "\t</tr>\n";
}
$table .= "</table>";
echo $table;
}
}
// Call the function you have made above :)
exams();
?>
- <?php
- function exams() {
- $con = mysql_connect('localhost','root','myrootpassword') or die(mysql_error());
- $select = mysql_select_db('dtbs') or die(mysql_error());
- if($con && $select)
- {
- $sql = "SELECT `exam_name` FROM exam ORDER BY exam_id";
- $exams = mysql_query($sql);
- // Get the table started
- $table = "<table border=\"1px\" style=\"width: 100%;\">\n\t<tr>\n\t\t<td>\n\t\t\t<p>Student</p>\n\t\t</td>\n";
- while($exam = mysql_fetch_assoc($exams))
- {
- $e_name = $exam['exam_name'];
- $table .= "\t\t<td>\n\t\t\t<p>{$e_name}</p>\n\t\t</td>\n";
- }
- $table .= "\t</tr>\n";
- $students = mysql_query("SELECT * FROM students");
- while($student = mysql_fetch_assoc($students))
- {
- $st_id = $student['student_id'];
- $st_name = $student['student_name'];
- $process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
- $table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
- while($row = mysql_fetch_assoc($process))
- {
- $score = $row['result'];
- $table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
- }
- $table .= "\t</tr>\n";
- }
- $table .= "</table>";
- echo $table;
- }
- }
- // Call the function you have made above :)
- exams();
- ?>
and calling my database:
Code: [ Select ]
-- ----------------------------
-- Table structure for exam
-- ----------------------------
CREATE TABLE `exam` (
`exam_id` int(10) NOT NULL auto_increment,
`exam_name` varchar(50) NOT NULL,
PRIMARY KEY (`exam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `exam` VALUES ('1', 'Biology');
INSERT INTO `exam` VALUES ('2', 'Math');
INSERT INTO `exam` VALUES ('3', 'Physics');
INSERT INTO `exam` VALUES ('4', 'Chemist');
INSERT INTO `exam` VALUES ('5', 'Sociology');
-- ----------------------------
-- Table structure for exam_results
-- ----------------------------
CREATE TABLE `exam_results` (
`id` int(10) NOT NULL auto_increment,
`student_id` int(10) NOT NULL,
`exam_id` int(10) NOT NULL,
`result` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `exam_results` VALUES ('1', '1', '1', '8');
INSERT INTO `exam_results` VALUES ('2', '1', '2', '7');
INSERT INTO `exam_results` VALUES ('3', '1', '4', '6');
INSERT INTO `exam_results` VALUES ('4', '1', '5', '9');
INSERT INTO `exam_results` VALUES ('5', '2', '1', '5');
INSERT INTO `exam_results` VALUES ('6', '2', '2', '8');
INSERT INTO `exam_results` VALUES ('7', '2', '3', '8');
INSERT INTO `exam_results` VALUES ('8', '2', '4', '6');
INSERT INTO `exam_results` VALUES ('9', '2', '5', '7');
INSERT INTO `exam_results` VALUES ('10', '3', '1', '7');
INSERT INTO `exam_results` VALUES ('11', '3', '2', '9');
INSERT INTO `exam_results` VALUES ('12', '3', '3', '7');
INSERT INTO `exam_results` VALUES ('13', '3', '5', '9');
-- ----------------------------
-- Table structure for students
-- ----------------------------
CREATE TABLE `students` (
`student_id` int(10) NOT NULL auto_increment,
`student_name` varchar(50) NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `students` VALUES ('1', 'John');
INSERT INTO `students` VALUES ('2', 'Mark');
INSERT INTO `students` VALUES ('3', 'Donny');
- -- ----------------------------
- -- Table structure for exam
- -- ----------------------------
- CREATE TABLE `exam` (
- `exam_id` int(10) NOT NULL auto_increment,
- `exam_name` varchar(50) NOT NULL,
- PRIMARY KEY (`exam_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- -- ----------------------------
- -- Records
- -- ----------------------------
- INSERT INTO `exam` VALUES ('1', 'Biology');
- INSERT INTO `exam` VALUES ('2', 'Math');
- INSERT INTO `exam` VALUES ('3', 'Physics');
- INSERT INTO `exam` VALUES ('4', 'Chemist');
- INSERT INTO `exam` VALUES ('5', 'Sociology');
- -- ----------------------------
- -- Table structure for exam_results
- -- ----------------------------
- CREATE TABLE `exam_results` (
- `id` int(10) NOT NULL auto_increment,
- `student_id` int(10) NOT NULL,
- `exam_id` int(10) NOT NULL,
- `result` int(10) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- -- ----------------------------
- -- Records
- -- ----------------------------
- INSERT INTO `exam_results` VALUES ('1', '1', '1', '8');
- INSERT INTO `exam_results` VALUES ('2', '1', '2', '7');
- INSERT INTO `exam_results` VALUES ('3', '1', '4', '6');
- INSERT INTO `exam_results` VALUES ('4', '1', '5', '9');
- INSERT INTO `exam_results` VALUES ('5', '2', '1', '5');
- INSERT INTO `exam_results` VALUES ('6', '2', '2', '8');
- INSERT INTO `exam_results` VALUES ('7', '2', '3', '8');
- INSERT INTO `exam_results` VALUES ('8', '2', '4', '6');
- INSERT INTO `exam_results` VALUES ('9', '2', '5', '7');
- INSERT INTO `exam_results` VALUES ('10', '3', '1', '7');
- INSERT INTO `exam_results` VALUES ('11', '3', '2', '9');
- INSERT INTO `exam_results` VALUES ('12', '3', '3', '7');
- INSERT INTO `exam_results` VALUES ('13', '3', '5', '9');
- -- ----------------------------
- -- Table structure for students
- -- ----------------------------
- CREATE TABLE `students` (
- `student_id` int(10) NOT NULL auto_increment,
- `student_name` varchar(50) NOT NULL,
- PRIMARY KEY (`student_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- -- ----------------------------
- -- Records
- -- ----------------------------
- INSERT INTO `students` VALUES ('1', 'John');
- INSERT INTO `students` VALUES ('2', 'Mark');
- INSERT INTO `students` VALUES ('3', 'Donny');
1. Some data was stored in wrong columns:
John should have no "Physics" exam result, not "Sociology".
So do Donny, he has no "Chemist" exam result, not "Sociology"
Code: [ Select ]
<table border="1px" style="width: 100%;">
<tr>
<td>
<p>Student</p>
</td>
<td>
<p>Biology</p>
</td>
<td>
<p>Math</p>
</td>
<td>
<p>Physics</p>
</td>
<td>
<p>Chemist</p>
</td>
<td>
<p>Sociology</p>
</td>
</tr>
<tr>
<td>
John
</td>
<td>
8
</td>
<td>
7
</td>
<td>
6
</td>
<td>
9
</td>
</tr>
<tr>
<td>
Mark
</td>
<td>
5
</td>
<td>
8
</td>
<td>
8
</td>
<td>
6
</td>
<td>
7
</td>
</tr>
<tr>
<td>
Donny
</td>
<td>
7
</td>
<td>
9
</td>
<td>
7
</td>
<td>
9
</td>
</tr>
</table>
<tr>
<td>
<p>Student</p>
</td>
<td>
<p>Biology</p>
</td>
<td>
<p>Math</p>
</td>
<td>
<p>Physics</p>
</td>
<td>
<p>Chemist</p>
</td>
<td>
<p>Sociology</p>
</td>
</tr>
<tr>
<td>
John
</td>
<td>
8
</td>
<td>
7
</td>
<td>
6
</td>
<td>
9
</td>
</tr>
<tr>
<td>
Mark
</td>
<td>
5
</td>
<td>
8
</td>
<td>
8
</td>
<td>
6
</td>
<td>
7
</td>
</tr>
<tr>
<td>
Donny
</td>
<td>
7
</td>
<td>
9
</td>
<td>
7
</td>
<td>
9
</td>
</tr>
</table>
- <table border="1px" style="width: 100%;">
- <tr>
- <td>
- <p>Student</p>
- </td>
- <td>
- <p>Biology</p>
- </td>
- <td>
- <p>Math</p>
- </td>
- <td>
- <p>Physics</p>
- </td>
- <td>
- <p>Chemist</p>
- </td>
- <td>
- <p>Sociology</p>
- </td>
- </tr>
- <tr>
- <td>
- John
- </td>
- <td>
- 8
- </td>
- <td>
- 7
- </td>
- <td>
- 6
- </td>
- <td>
- 9
- </td>
- </tr>
- <tr>
- <td>
- Mark
- </td>
- <td>
- 5
- </td>
- <td>
- 8
- </td>
- <td>
- 8
- </td>
- <td>
- 6
- </td>
- <td>
- 7
- </td>
- </tr>
- <tr>
- <td>
- Donny
- </td>
- <td>
- 7
- </td>
- <td>
- 9
- </td>
- <td>
- 7
- </td>
- <td>
- 9
- </td>
- </tr>
- </table>
2. Your code needs too much MySQL queries. If we work w/ big-size data, our server will be "timed out || not responding || data will be loaded uncompletely".
For example; we have 1,000,000,000 students, and 50 exams.
we need to request exam_result.result for 50,000,000,000 times
(but I wont to try this
You use looping inside a looping (while in while).
Code: [ Select ]
$students = mysql_query("SELECT * FROM students");
while($student = mysql_fetch_assoc($students))
{
...
$process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
$table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
while($row = mysql_fetch_assoc($process))
{
...
- $students = mysql_query("SELECT * FROM students");
- while($student = mysql_fetch_assoc($students))
- {
- ...
- $process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
- $table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
- while($row = mysql_fetch_assoc($process))
- {
- ...
Would you write the data in array to make it simple?
$line[1]="$row[John] </td><td>$row[John][Biology]</td><td>$row[John][Math]...";
I mean, combining (w/ some modification, offcourse) MySQL Queries w/ script like this:
Code: [ Select ]
<?
$array = array(
array("App Name 1", "App Name 2", "App Name 3", "App Name 4", "App Name 5", "App Name 6"),
array("department 1", "App Name 2", "App Name 5", "App Name 6"),
array("department 2", "App Name 1", "App Name 2", "App Name 4"),
array("department 3", "App Name 1", "App Name 2", "App Name 3", "App Name 5", "App Name 6"),
);
//headers:
$headers = $array[0];
echo "<table border=1>
<tr>
<td>Department</td>\n";
foreach ( $headers AS $header ) {
echo "\t\t<td>$header</td>\n";
}
echo "\t</tr>\n";
//loop through the remainder of the array
$end = count($array);
for ( $p = 1; $p < $end; $p++ ) {
//echo the department:
echo "\t<tr>
<td>" . $array[$p][0] . "</td>\n";
//loop through the headers, print an X in the td if present in $array[$p]
foreach ( $headers AS $header ) {
echo "\t\t<td>";
if ( in_array($header, $array[$p]) ) {
echo "X";
} else {
echo " ";
}
echo "</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>";
?>
- <?
- $array = array(
- array("App Name 1", "App Name 2", "App Name 3", "App Name 4", "App Name 5", "App Name 6"),
- array("department 1", "App Name 2", "App Name 5", "App Name 6"),
- array("department 2", "App Name 1", "App Name 2", "App Name 4"),
- array("department 3", "App Name 1", "App Name 2", "App Name 3", "App Name 5", "App Name 6"),
- );
- //headers:
- $headers = $array[0];
- echo "<table border=1>
- <tr>
- <td>Department</td>\n";
- foreach ( $headers AS $header ) {
- echo "\t\t<td>$header</td>\n";
- }
- echo "\t</tr>\n";
- //loop through the remainder of the array
- $end = count($array);
- for ( $p = 1; $p < $end; $p++ ) {
- //echo the department:
- echo "\t<tr>
- <td>" . $array[$p][0] . "</td>\n";
- //loop through the headers, print an X in the td if present in $array[$p]
- foreach ( $headers AS $header ) {
- echo "\t\t<td>";
- if ( in_array($header, $array[$p]) ) {
- echo "X";
- } else {
- echo " ";
- }
- echo "</td>\n";
- }
- echo "\t</tr>\n";
- }
- echo "</table>";
- ?>
Thanks brothers!
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
r_t, your latest code displayed CORRECT RESULT!
That's I need.
But, where's the php code?
Code: [ Select ]
Attempt two - With Zeros
Students Biology Math Physics Chemist Sociology
John 8 7 0 6 9
Mark 5 8 8 6 7
Donny 7 9 7 0 9
Students Biology Math Physics Chemist Sociology
John 8 7 0 6 9
Mark 5 8 8 6 7
Donny 7 9 7 0 9
- Attempt two - With Zeros
- Students Biology Math Physics Chemist Sociology
- John 8 7 0 6 9
- Mark 5 8 8 6 7
- Donny 7 9 7 0 9
That's I need.
But, where's the php code?
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
Code: [ Select ]
<?php
$con = mysql_connect("localhost","ohdesign_root","Hennie@OhDesignX.com");
mysql_select_db("ohdesign_M4MobileTest");
$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");
$temp1 = "";
$temp2 = "";
$count = 0;
echo "<table><tr><td>Students</td>";
while($row = mysql_fetch_array($sql2))
{
echo "<td>" . $row['exam_name'] . "</td>";
}
echo "</tr>";
while($row = mysql_fetch_array($sql))
{
$temp1 = $row['student_id'];
if($temp1 == $temp2)
{
echo "<td>" . $row['result'] . "</td>";
}
else
{
if ($count == 0)
{
echo "<tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
$count ++;
}
else
{
echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
}
}
$temp2 = $row['student_id'];
}
echo "</table>";
mysql_close($con);
?>
$con = mysql_connect("localhost","ohdesign_root","Hennie@OhDesignX.com");
mysql_select_db("ohdesign_M4MobileTest");
$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");
$temp1 = "";
$temp2 = "";
$count = 0;
echo "<table><tr><td>Students</td>";
while($row = mysql_fetch_array($sql2))
{
echo "<td>" . $row['exam_name'] . "</td>";
}
echo "</tr>";
while($row = mysql_fetch_array($sql))
{
$temp1 = $row['student_id'];
if($temp1 == $temp2)
{
echo "<td>" . $row['result'] . "</td>";
}
else
{
if ($count == 0)
{
echo "<tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
$count ++;
}
else
{
echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
}
}
$temp2 = $row['student_id'];
}
echo "</table>";
mysql_close($con);
?>
- <?php
- $con = mysql_connect("localhost","ohdesign_root","Hennie@OhDesignX.com");
- mysql_select_db("ohdesign_M4MobileTest");
- $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");
- $temp1 = "";
- $temp2 = "";
- $count = 0;
- echo "<table><tr><td>Students</td>";
- while($row = mysql_fetch_array($sql2))
- {
- echo "<td>" . $row['exam_name'] . "</td>";
- }
- echo "</tr>";
- while($row = mysql_fetch_array($sql))
- {
- $temp1 = $row['student_id'];
- if($temp1 == $temp2)
- {
- echo "<td>" . $row['result'] . "</td>";
- }
- else
- {
- if ($count == 0)
- {
- echo "<tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
- $count ++;
- }
- else
- {
- echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" . $row['result'] . "</td>";
- }
- }
- $temp2 = $row['student_id'];
- }
- echo "</table>";
- mysql_close($con);
- ?>
There you go ... now just remember that you must add zeros(0) where the person hasn't taken the test ... got it?
Let's leave all our *plum* where it is and go live in the jungle ...
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
Sorry... It's my STUPID question:
where did you insert zero?
i'll try this but still not work:
<?php
$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$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");
$temp1 = "";
$temp2 = "";
$count = 0;
echo "<table><tr><td>Students</td>";
while($row = mysql_fetch_array($sql2))
{
echo "<td>" . $row['exam_name'] . "</td>";
}
echo "</tr>";
while($row = mysql_fetch_array($sql))
{
$temp1 = $row['student_id'];
if($temp1 == $temp2)
{
echo "<td>" . $row['result'] . "</td>";
}
else
{
if ($count == 0)
{
echo "<tr><td>" . $row['student_name'] . "</td><td>" ;
if(!$row[result])
{
echo 0;
}
else
{
echo $row['result'] ;
}
echo "</td>";
$count ++;
}
else
{
echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" ;
if(!$row[result])
{
echo 0;
}
else
{
echo $row['result'] ;
}
echo "</td>";
}
}
$temp2 = $row['student_id'];
}
echo "</table>";
mysql_close($con);
?>
it results:
again...

where did you insert zero?
i'll try this but still not work:
Code: [ Select ]
<?php
$con = mysql_connect("localhost","root","mypass");
mysql_select_db("mydb");
$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");
$temp1 = "";
$temp2 = "";
$count = 0;
echo "<table><tr><td>Students</td>";
while($row = mysql_fetch_array($sql2))
{
echo "<td>" . $row['exam_name'] . "</td>";
}
echo "</tr>";
while($row = mysql_fetch_array($sql))
{
$temp1 = $row['student_id'];
if($temp1 == $temp2)
{
echo "<td>" . $row['result'] . "</td>";
}
else
{
if ($count == 0)
{
echo "<tr><td>" . $row['student_name'] . "</td><td>" ;
if(!$row[result])
{
echo 0;
}
else
{
echo $row['result'] ;
}
echo "</td>";
$count ++;
}
else
{
echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" ;
if(!$row[result])
{
echo 0;
}
else
{
echo $row['result'] ;
}
echo "</td>";
}
}
$temp2 = $row['student_id'];
}
echo "</table>";
mysql_close($con);
?>
- <?php
- $con = mysql_connect("localhost","root","mypass");
- mysql_select_db("mydb");
- $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");
- $temp1 = "";
- $temp2 = "";
- $count = 0;
- echo "<table><tr><td>Students</td>";
- while($row = mysql_fetch_array($sql2))
- {
- echo "<td>" . $row['exam_name'] . "</td>";
- }
- echo "</tr>";
- while($row = mysql_fetch_array($sql))
- {
- $temp1 = $row['student_id'];
- if($temp1 == $temp2)
- {
- echo "<td>" . $row['result'] . "</td>";
- }
- else
- {
- if ($count == 0)
- {
- echo "<tr><td>" . $row['student_name'] . "</td><td>" ;
- if(!$row[result])
- {
- echo 0;
- }
- else
- {
- echo $row['result'] ;
- }
- echo "</td>";
- $count ++;
- }
- else
- {
- echo "</tr><tr><td>" . $row['student_name'] . "</td><td>" ;
- if(!$row[result])
- {
- echo 0;
- }
- else
- {
- echo $row['result'] ;
- }
- echo "</td>";
- }
- }
- $temp2 = $row['student_id'];
- }
- echo "</table>";
- mysql_close($con);
- ?>
it results:
Code: [ Select ]
Students Biology Math Physics Chemist Sociology
John 8 7 6 9
Mark 5 8 8 6 7
Donny 7 9 7 9
John 8 7 6 9
Mark 5 8 8 6 7
Donny 7 9 7 9
- Students Biology Math Physics Chemist Sociology
- John 8 7 6 9
- Mark 5 8 8 6 7
- Donny 7 9 7 9
again...
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
I didn't insert the 0 anywhere in the code I added this to the Database
Code: [ Select ]
INSERT INTO exam_results (student_id,exam_id,result) VALUES ('1','3','0');
INSERT INTO exam_results (student_id,exam_id,result) VALUES ('3','4','0');
INSERT INTO exam_results (student_id,exam_id,result) VALUES ('3','4','0');
- INSERT INTO exam_results (student_id,exam_id,result) VALUES ('1','3','0');
- INSERT INTO exam_results (student_id,exam_id,result) VALUES ('3','4','0');
Let's leave all our *plum* where it is and go live in the jungle ...
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
According me, that's not a good idea for inserting 0 data.
If I was an EDP staff, I wouldn't insert blank exam_result (per student, per exam)... It's a hard job for me if we have a large number of students and exams ( smile ).
We will have database with so many 0 value.
It should be generated automatically by the programmer, not the EDP Staff.
Btw, thx for your method.
It could fix my problem too.
Other methods will be awarded too
If I was an EDP staff, I wouldn't insert blank exam_result (per student, per exam)... It's a hard job for me if we have a large number of students and exams ( smile ).
We will have database with so many 0 value.
It should be generated automatically by the programmer, not the EDP Staff.
Btw, thx for your method.
It could fix my problem too.
Other methods will be awarded too
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
look, there is no other way that I can see to do this ... so take it or leave it ... Unfortunately I don't have time to code your whole site for you ... I gave all the help I can ... It seems to me that you are taking on a job that you do not have the necessary skills for, I'm not saying that this is a bad thing, you can learn a lot from a project like this, but it almost feels as if you're expecting me to code the whole site for you ... And at the end of the day that's not quite fair seeing as you're the one getting paid for this job and I'm neglecting my job to help out ...
Let's leave all our *plum* where it is and go live in the jungle ...
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
No... no...
You are miss understood.
I realy award Your helps and Bogey's helps.
But if I could not take it after i make a comparison, or I found a bad side of Your method, should you angry?
Please note that NOT all users marked as "newbie" in this forum are realy "newbie" or "plagiator" that will sell your code totaly with no permission at all and saying "Hahaha! I'm rich now by my self, not the Superhero at Ozzu".
Your idea is smart but I, like I was written in the title: "PHP-MySQL: Need Help to Create Multidimensional HTML Table" alway thinking that "Multidimensional Array" is the best way.
You are a "Superhero" aren't You?
Some of phpGuru that "real Guru" I knew, recommend me to use this method (Php Multidimensional array).
I tried, but it always fails. It's not mean I only want Your code, FREEly.
Thanks for your smart
idea.
You are miss understood.
I realy award Your helps and Bogey's helps.
But if I could not take it after i make a comparison, or I found a bad side of Your method, should you angry?
Please note that NOT all users marked as "newbie" in this forum are realy "newbie" or "plagiator" that will sell your code totaly with no permission at all and saying "Hahaha! I'm rich now by my self, not the Superhero at Ozzu".
Your idea is smart but I, like I was written in the title: "PHP-MySQL: Need Help to Create Multidimensional HTML Table" alway thinking that "Multidimensional Array" is the best way.
You are a "Superhero" aren't You?
Some of phpGuru that "real Guru" I knew, recommend me to use this method (Php Multidimensional array).
I tried, but it always fails. It's not mean I only want Your code, FREEly.
Thanks for your smart
- righteous_trespasser
- Scuffle


- Joined: Mar 12, 2007
- Posts: 6228
- Loc: South-Africa
- Status: Offline
lolz. I'm no superhero ... I started learning php a month ago ... And no pleas do not think I am angry ... I am just saying that this is all that I know ... I don't know more ... hopefully someone else could help you here ...
Let's leave all our *plum* where it is and go live in the jungle ...
- phplover
- Novice


- Joined: Jul 03, 2008
- Posts: 15
- Loc: INDONESIA
- Status: Offline
- Anonymous
- Bot


- Joined: 25 Feb 2008
- Posts: ?
- Loc: Ozzuland
- Status: Online
July 9th, 2008, 9:28 am
To Reply to this topic you need to LOGIN or REGISTER. It is free.
Post Information
- Total Posts in this topic: 61 posts
- Users browsing this forum: No registered users and 279 guests
- You cannot post new topics in this forum
- You cannot reply to topics in this forum
- You cannot edit your posts in this forum
- You cannot delete your posts in this forum
- You cannot post attachments in this forum
