Back To Programming / Scripting / Coding Forum

PHP-MySQL: Need Help to Create Multidimensional HTML Table

  • phplover
  • Novice
  • Novice
  • User avatar
  • Joined: Jul 03, 2008
  • Posts: 15
  • Loc: INDONESIA
  • Status: Offline

Post July 3rd, 2008, 11:32 pm

friends, i wanna print datas from MySQL into html with PHP like this:

===============================================================================
student | Biology | Math | Physics | Chemist | Sociology | average
===============================================================================
John | 8 | 7 | - | 6 | 9 |
Mark | 5 | 8 | 8 | 6 | 7 |
Donny | 7 | 9 | 7 | - | 9 |
================================================================================
Total | | | | | |
================================================================================

where the datas are taken from 3 tables (MySQL):

`students` table:
---------
student_id, student_name
---------------------------
1 John
2 Mark
3 Donny

`exam` table:
-----------
exam_id, exam_name
-------------------
1. Biology
2. Math
3. Physics
4. Chemist
5. Sociology

ada

`exam_result` table:
------------
student_name, exam_name, exam_result
--------------------------------------
John Biology 8
...


How can i do this whith php_array?

I tried to display the exam_result using:

$exam_result[student][exam]

but it fails.

Please help, bros...

Thx


oom Donny yang Guanteng
From Jakarta w/ love & peace
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post July 3rd, 2008, 11:32 pm

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 3rd, 2008, 11:51 pm

Okay phplover, I think you could set up the database way better to make life easier for you ... here's how I would set up that Database and the code I would use to get the results ...
Code: [ Select ]
students
student_id - BIGINT,auto-increment
student_name - VARCHAR (100)
exam
exam_id - BIGINT auto-increment
exam name - VARCHAR (100)
exam_results
student_id - BIGINT
exam_id - BIGINT
result - INT(3)
  1. students
  2. student_id - BIGINT,auto-increment
  3. student_name - VARCHAR (100)
  4. exam
  5. exam_id - BIGINT auto-increment
  6. exam name - VARCHAR (100)
  7. exam_results
  8. student_id - BIGINT
  9. exam_id - BIGINT
  10. result - INT(3)


and a query to get the results would be:
Code: [ Select ]
SELECT student.student_name,exam.exam_name,exam_results.result FROM students LEFT JOIN exam_result ON student.student_id = exam_result.student_id LEFT JOIN exam ON exam.exam_id = exam_result.exam_id ORDER BY student.student_id,exam.exam_name

This way you'll get something like the following:
John - English - 80
John - Math - 72
Mary - English - 60
Mary - English - 23

hope this helped a little ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • phplover
  • Novice
  • Novice
  • User avatar
  • Joined: Jul 03, 2008
  • Posts: 15
  • Loc: INDONESIA
  • Status: Offline

Post July 7th, 2008, 11:13 am

Thanks, righteous_trespasser.
But it's not easy as joining query from one or more table only.
I mean, the headers (exam names) are data in `exam_name` table, and i need to place the exam_result datas to be match to their columns and rows.
The Column headers are: `exam_name` data
The Row headers Are: `student_name` data

So, the table structure should be displayed like:
Code: [ Select ]
 
----------------------------------------------------------
student | Biology | Math | Physics | Chemist | Sociology |
==========================================================
John    | 8       | 7    | -       | 6       | 9         |
Mark    | 5       | 8    | 8       | 6       | 7         |
Donny   | 7       | 9    | 7       | -       | 9         |
==========================================================
Total   | 20      | 24   | 15      | 12      | 25        |
==========================================================
// `exam_name` will be columns header
// `student_name` will be rows header
// `exam_result` will be data that is match to its column and rows.
 
  1.  
  2. ----------------------------------------------------------
  3. student | Biology | Math | Physics | Chemist | Sociology |
  4. ==========================================================
  5. John    | 8       | 7    | -       | 6       | 9         |
  6. Mark    | 5       | 8    | 8       | 6       | 7         |
  7. Donny   | 7       | 9    | 7       | -       | 9         |
  8. ==========================================================
  9. Total   | 20      | 24   | 15      | 12      | 25        |
  10. ==========================================================
  11. // `exam_name` will be columns header
  12. // `student_name` will be rows header
  13. // `exam_result` will be data that is match to its column and rows.
  14.  

How to make table structure like this w/ PHP?
Thanks for stay helping.

PhpLover
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 7th, 2008, 12:34 pm

I'd let php worry about that actually ... And not try to get the table exactly like that because as far as my knowledge stretches that isn't possible ... here's a simple example ...
Code: [ Select ]
$con = mysql_connect("localhost","username","password");
mysql_select_db("db_name");
$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_result.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY user_id, exam_id");
$temp1 = "";
$temp2 = "";
echo "student | Biology | Math | Physics | Chemist | Sociology |";
while($row = mysql_fetch_array($sql))
{
$temp1 = $row['user_id'];
if($temp1 == temp2)
{
echo $row['exam_result'] . " |";
}
else
{
echo $row['user_name'] . " |" . $row['exam_result']
}
$temp2 = $row['user_id'];
}
mysql_close($con);
  1. $con = mysql_connect("localhost","username","password");
  2. mysql_select_db("db_name");
  3. $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_result.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY user_id, exam_id");
  4. $temp1 = "";
  5. $temp2 = "";
  6. echo "student | Biology | Math | Physics | Chemist | Sociology |";
  7. while($row = mysql_fetch_array($sql))
  8. {
  9. $temp1 = $row['user_id'];
  10. if($temp1 == temp2)
  11. {
  12. echo $row['exam_result'] . " |";
  13. }
  14. else
  15. {
  16. echo $row['user_name'] . " |" . $row['exam_result']
  17. }
  18. $temp2 = $row['user_id'];
  19. }
  20. mysql_close($con);


Only thing that's missing here is working out the totals ... If you don't quite get what I did please ask and I'll explain in more detail ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • phplover
  • Novice
  • Novice
  • User avatar
  • Joined: Jul 03, 2008
  • Posts: 15
  • Loc: INDONESIA
  • Status: Offline

Post July 8th, 2008, 2:04 am

The totals values is not too difficult. But printing results regarding its column and rows i ever heard before, it could be done with creating multidimensional array 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 "&nbsp;";
        }
        echo "</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>";  
?>
 
  1.  
  2. <?
  3.  $array = array(
  4.     array("App Name 1", "App Name 2", "App Name 3", "App Name 4", "App Name 5", "App Name 6"),
  5.     array("department 1", "App Name 2", "App Name 5", "App Name 6"),
  6.     array("department 2", "App Name 1", "App Name 2", "App Name 4"),
  7.     array("department 3", "App Name 1", "App Name 2", "App Name 3", "App Name 5", "App Name 6"),
  8.     );
  9.      
  10. //headers:
  11. $headers = $array[0];
  12. echo "<table border=1>
  13.     <tr>
  14.         <td>Department</td>\n";
  15. foreach ( $headers AS $header ) {
  16.     echo "\t\t<td>$header</td>\n";
  17. }
  18. echo "\t</tr>\n";
  19.  
  20. //loop through the remainder of the array
  21. $end = count($array);
  22. for ( $p = 1; $p < $end; $p++ ) {
  23.     //echo the department:
  24.     echo "\t<tr>
  25.         <td>" . $array[$p][0] . "</td>\n";
  26.     //loop through the headers, print an X in the td if present in $array[$p]
  27.     foreach ( $headers AS $header ) {
  28.         echo "\t\t<td>";
  29.         if ( in_array($header, $array[$p]) ) {
  30.             echo "X";
  31.         } else {
  32.             echo "&nbsp;";
  33.         }
  34.         echo "</td>\n";
  35.     }
  36.     echo "\t</tr>\n";
  37. }
  38. echo "</table>";  
  39. ?>
  40.  

The above code produces:
Code: [ Select ]
 
<table border=1>
    <tr>
        <td>Department</td>
        <td>App Name 1</td>
        <td>App Name 2</td>
        <td>App Name 3</td>
        <td>App Name 4</td>
        <td>App Name 5</td>
        <td>App Name 6</td>
    </tr>
    <tr>
        <td>department 1</td>
        <td>&nbsp;</td>
        <td>X</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>X</td>
        <td>X</td>
    </tr>
    <tr>
        <td>department 2</td>
        <td>X</td>
        <td>X</td>
        <td>&nbsp;</td>
        <td>X</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>department 3</td>
        <td>X</td>
        <td>X</td>
        <td>X</td>
        <td>&nbsp;</td>
        <td>X</td>
        <td>X</td>
    </tr>
</table>
 
  1.  
  2. <table border=1>
  3.     <tr>
  4.         <td>Department</td>
  5.         <td>App Name 1</td>
  6.         <td>App Name 2</td>
  7.         <td>App Name 3</td>
  8.         <td>App Name 4</td>
  9.         <td>App Name 5</td>
  10.         <td>App Name 6</td>
  11.     </tr>
  12.     <tr>
  13.         <td>department 1</td>
  14.         <td>&nbsp;</td>
  15.         <td>X</td>
  16.         <td>&nbsp;</td>
  17.         <td>&nbsp;</td>
  18.         <td>X</td>
  19.         <td>X</td>
  20.     </tr>
  21.     <tr>
  22.         <td>department 2</td>
  23.         <td>X</td>
  24.         <td>X</td>
  25.         <td>&nbsp;</td>
  26.         <td>X</td>
  27.         <td>&nbsp;</td>
  28.         <td>&nbsp;</td>
  29.     </tr>
  30.     <tr>
  31.         <td>department 3</td>
  32.         <td>X</td>
  33.         <td>X</td>
  34.         <td>X</td>
  35.         <td>&nbsp;</td>
  36.         <td>X</td>
  37.         <td>X</td>
  38.     </tr>
  39. </table>
  40.  

I only need to load the datas from 3 mysql tables into the table. But all i need is to make the following (minimum):
Code: [ Select ]
 
----------------------------------------------------------
student | Biology | Math | Physics | Chemist | Sociology |
==========================================================
John    | 8       | 7    | -       | 6       | 9         |
Mark    | 5       | 8    | 8       | 6       | 7         |
Donny   | 7       | 9    | 7       | -       | 9         |
 
 
  1.  
  2. ----------------------------------------------------------
  3. student | Biology | Math | Physics | Chemist | Sociology |
  4. ==========================================================
  5. John    | 8       | 7    | -       | 6       | 9         |
  6. Mark    | 5       | 8    | 8       | 6       | 7         |
  7. Donny   | 7       | 9    | 7       | -       | 9         |
  8.  
  9.  

or
Code: [ Select ]
HOSTING PLANS
----------------------------------------------------
Features/Plan US-MICRO US-MINI US-STARTER US-BASIC
====================================================
Disk Space          1 MB 25 MB 50 MB 100 MB
Bandwidth / Bulan 150 MB 750 MB 1.5 GB     3 GB
Monthly             899,- 2.500,- 5.000,- 8.500,-
Setup Fee         FREE     FREE      FREE     FREE
=====================================================

where it comes from 3 tables like:
plan_name, features_name, and plan_features.
  1. HOSTING PLANS
  2. ----------------------------------------------------
  3. Features/Plan US-MICRO US-MINI US-STARTER US-BASIC
  4. ====================================================
  5. Disk Space          1 MB 25 MB 50 MB 100 MB
  6. Bandwidth / Bulan 150 MB 750 MB 1.5 GB     3 GB
  7. Monthly             899,- 2.500,- 5.000,- 8.500,-
  8. Setup Fee         FREE     FREE      FREE     FREE
  9. =====================================================
  11. where it comes from 3 tables like:
  12. plan_name, features_name, and plan_features.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 8th, 2008, 2:25 am

The code I showed you will return this:
Code: [ Select ]
 
----------------------------------------------------------
student | Biology | Math | Physics | Chemist | Sociology |
==========================================================
John    | 8       | 7    | -       | 6       | 9         |
Mark    | 5       | 8    | 8       | 6       | 7         |
Donny   | 7       | 9    | 7       | -       | 9         |
 
 
  1.  
  2. ----------------------------------------------------------
  3. student | Biology | Math | Physics | Chemist | Sociology |
  4. ==========================================================
  5. John    | 8       | 7    | -       | 6       | 9         |
  6. Mark    | 5       | 8    | 8       | 6       | 7         |
  7. Donny   | 7       | 9    | 7       | -       | 9         |
  8.  
  9.  

It just needs some fine tuning (placement) ... but I know it'll work ... I have done it before ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • phplover
  • Novice
  • Novice
  • User avatar
  • Joined: Jul 03, 2008
  • Posts: 15
  • Loc: INDONESIA
  • Status: Offline

Post July 8th, 2008, 10:23 am

Thanks for your help, brother.
I have tried to use your code but it doesn't work although I've modified some aparts.

Your code will not display data from a new `exam_name`.
I mean, we need always change our php & html code if a new `exam_name` is registered.
These are the tables related I used to run your code:
Code: [ Select ]
 
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 of `students` :
-- ----------------------------
INSERT INTO `students` VALUES ('1', 'John');
INSERT INTO `students` VALUES ('2', 'Mark');
INSERT INTO `students` VALUES ('3', 'Donny');
 
-- =========================================
 
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 of `exam`:
-- ----------------------------
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');
 
-- =========================================
 
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 of `exam_results`:
-- ----------------------------
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');
 
 
  1.  
  2. CREATE TABLE `students` (
  3.   `student_id` int(10) NOT NULL auto_increment,
  4.   `student_name` varchar(50) NOT NULL,
  5.   PRIMARY KEY  (`student_id`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  7.  
  8. -- ----------------------------
  9. -- Records of `students` :
  10. -- ----------------------------
  11. INSERT INTO `students` VALUES ('1', 'John');
  12. INSERT INTO `students` VALUES ('2', 'Mark');
  13. INSERT INTO `students` VALUES ('3', 'Donny');
  14.  
  15. -- =========================================
  16.  
  17. CREATE TABLE `exam` (
  18.   `exam_id` int(10) NOT NULL auto_increment,
  19.   `exam_name` varchar(50) NOT NULL,
  20.   PRIMARY KEY  (`exam_id`)
  21. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  22.  
  23. -- ----------------------------
  24. -- Records of `exam`:
  25. -- ----------------------------
  26. INSERT INTO `exam` VALUES ('1', 'Biology');
  27. INSERT INTO `exam` VALUES ('2', 'Math');
  28. INSERT INTO `exam` VALUES ('3', 'Physics');
  29. INSERT INTO `exam` VALUES ('4', 'Chemist');
  30. INSERT INTO `exam` VALUES ('5', 'Sociology');
  31.  
  32. -- =========================================
  33.  
  34. CREATE TABLE `exam_results` (
  35.   `id` int(10) NOT NULL auto_increment,
  36.   `student_id` int(10) NOT NULL,
  37.   `exam_id` int(10) NOT NULL,
  38.   `result` int(10) NOT NULL,
  39.   PRIMARY KEY  (`id`)
  40. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  41.  
  42. -- ----------------------------
  43. -- Records of `exam_results`:
  44. -- ----------------------------
  45. INSERT INTO `exam_results` VALUES ('1', '1', '1', '8');
  46. INSERT INTO `exam_results` VALUES ('2', '1', '2', '7');
  47. INSERT INTO `exam_results` VALUES ('3', '1', '4', '6');
  48. INSERT INTO `exam_results` VALUES ('4', '1', '5', '9');
  49. INSERT INTO `exam_results` VALUES ('5', '2', '1', '5');
  50. INSERT INTO `exam_results` VALUES ('6', '2', '2', '8');
  51. INSERT INTO `exam_results` VALUES ('7', '2', '3', '8');
  52. INSERT INTO `exam_results` VALUES ('8', '2', '4', '6');
  53. INSERT INTO `exam_results` VALUES ('9', '2', '5', '7');
  54. INSERT INTO `exam_results` VALUES ('10', '3', '1', '7');
  55. INSERT INTO `exam_results` VALUES ('11', '3', '2', '9');
  56. INSERT INTO `exam_results` VALUES ('12', '3', '3', '7');
  57. INSERT INTO `exam_results` VALUES ('13', '3', '5', '9');
  58.  
  59.  

Then, I run Your scripts (some has been modified, please tell me if i do some wrong modification):
Code: [ Select ]
 
<?php
$con = mysql_connect("localhost","root","mypassword");
mysql_select_db("exam_db");
$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 students.student_id, exam.exam_id")or die(mysql_error());
$temp1 = "";
$temp2 = "";
echo "student | Biology | Math | Physics | Chemist | Sociology |";
    while($row = mysql_fetch_array($sql))
    {
        $temp1 = $row['student_id'];
        if($temp1 == temp2)
        {
            echo $row['result'] . "   |<br />";
        }
        else
        {
            echo $row['student_name'] . "   |" . $row['result']."<br />";
        }
        $temp2 = $row['student_id'];
    }
mysql_close($con);
 
?>
 
  1.  
  2. <?php
  3. $con = mysql_connect("localhost","root","mypassword");
  4. mysql_select_db("exam_db");
  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 students.student_id, exam.exam_id")or die(mysql_error());
  6. $temp1 = "";
  7. $temp2 = "";
  8. echo "student | Biology | Math | Physics | Chemist | Sociology |";
  9.     while($row = mysql_fetch_array($sql))
  10.     {
  11.         $temp1 = $row['student_id'];
  12.         if($temp1 == temp2)
  13.         {
  14.             echo $row['result'] . "   |<br />";
  15.         }
  16.         else
  17.         {
  18.             echo $row['student_name'] . "   |" . $row['result']."<br />";
  19.         }
  20.         $temp2 = $row['student_id'];
  21.     }
  22. mysql_close($con);
  23.  
  24. ?>
  25.  

the above code produce the following:
Code: [ Select ]
 
student | Biology | Math | Physics | Chemist | Sociology |John |8John |7John |6John |9Mark |5Mark |8Mark |8Mark |6Mark |7Donny |7Donny |9Donny |7Donny |9
 
 
  1.  
  2. student | Biology | Math | Physics | Chemist | Sociology |John |8John |7John |6John |9Mark |5Mark |8Mark |8Mark |6Mark |7Donny |7Donny |9Donny |7Donny |9
  3.  
  4.  

"The table header and all datas are in one row."
You said that it returned:
Code: [ Select ]
 
----------------------------------------------------------
student | Biology | Math | Physics | Chemist | Sociology |
==========================================================
John    | 8       | 7    | -       | 6       | 9         |
Mark    | 5       | 8    | 8       | 6       | 7         |
Donny   | 7       | 9    | 7       | -       | 9         |
----------------------------------------------------------
 
  1.  
  2. ----------------------------------------------------------
  3. student | Biology | Math | Physics | Chemist | Sociology |
  4. ==========================================================
  5. John    | 8       | 7    | -       | 6       | 9         |
  6. Mark    | 5       | 8    | 8       | 6       | 7         |
  7. Donny   | 7       | 9    | 7       | -       | 9         |
  8. ----------------------------------------------------------
  9.  

Sorry, but I think it doesn't.
 
1st reason:
------------
John[Physics] is empty, and (your) John[Chemist] value will place (your) empty John[Physics]'s column. It's mean, some datas aren't placedin the right columns (and rows).
 
 
2nd reason:
-----------
Your table headers (Biology, Math, Physics, Chemist, Sociology) is written in static HTML, it's not data from `exam` table.
This will force you work hard if any additional on `exam` table.
You have to place the exam_result value matching its column.
=========
 
There's only one method to place the result into its correct row and column, using array in array (multidimensional array):
Code: [ Select ]
 
echo "$result[student_name] | $result[student_name][Biology] | $result[student_name][Math] ...and so on";
 
  1.  
  2. echo "$result[student_name] | $result[student_name][Biology] | $result[student_name][Math] ...and so on";
  3.  

It's almost like the code i posted before. The data will be placed in the right place.

The question is:
How to make that like data using MySQL Data (3 tables)?

PhpLover
Donny Tjandra
http://www.webprogrammer.asia
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 8th, 2008, 11:56 am

Okay, I think you didn't quite understand my code so here we go ... I am going to stop quickly and write it for you ... here we go ...

Code: [ Select ]
<?php
$con = mysql_connect("localhost","username","password");
mysql_select_db("db_name");
$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_result.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY user_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['user_id'];
if($temp1 == temp2)
{
echo "<td>" . $row['exam_result'] . "</td>";
}
else
{
if ($count == 0)
{
echo "<tr><td>" . $row['user_name'] . "</td><td>" . $row['exam_result'] . "</td>";
$count ++;
}
else
{
echo "</tr><tr><td>" . $row['user_name'] . "</td><td>" . $row['exam_result'] . "</td>";
}
}
$temp2 = $row['user_id'];
}
echo "</table>";
mysql_close($con);
?>
  1. <?php
  2. $con = mysql_connect("localhost","username","password");
  3. mysql_select_db("db_name");
  4. $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_result.student_id LEFT JOIN exam ON exam_results.exam_id = exam.exam_id ORDER BY user_id, exam_id");
  5. $sql2 = mysql_query("SELECT DISTINCT exam_name FROM exam ORDER BY exam_id");
  6. $temp1 = "";
  7. $temp2 = "";
  8. $count = 0;
  9. echo "<table><tr><td>Students</td>";
  10. while($row = mysql_fetch_array($sql2))
  11. {
  12. echo "<td>" . $row['exam_name'] . "</td>";
  13. }
  14. echo "</tr>";
  15. while($row = mysql_fetch_array($sql))
  16. {
  17. $temp1 = $row['user_id'];
  18. if($temp1 == temp2)
  19. {
  20. echo "<td>" . $row['exam_result'] . "</td>";
  21. }
  22. else
  23. {
  24. if ($count == 0)
  25. {
  26. echo "<tr><td>" . $row['user_name'] . "</td><td>" . $row['exam_result'] . "</td>";
  27. $count ++;
  28. }
  29. else
  30. {
  31. echo "</tr><tr><td>" . $row['user_name'] . "</td><td>" . $row['exam_result'] . "</td>";
  32. }
  33. }
  34. $temp2 = $row['user_id'];
  35. }
  36. echo "</table>";
  37. mysql_close($con);
  38. ?>

Do you need me to explain to you what I did?
Let's leave all our *plum* where it is and go live in the jungle ...
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 8th, 2008, 2:19 pm

:lol: Alright... I decided to barge in here and interrupt your conversation with R_T and phplover.

Is OOP (Object Oriented Programming)...(A class in this case) a too much approach for this? Is that an overkill for something like this?

I mean, I started typing a class for this and I'm half-way done but now that I think of it... is it too much?

Or would you still try it? :lol:

[EDIT:] Actually I'm done with it...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 8th, 2008, 4:54 pm

Forget CLASSES... it's not worth it for this case... I redid it into one function without the class but the damn thing doesn't want to work :lol:

Shows the score for all of them for only 1 exam and the rest don't get a chance haha can't figure out the problem as the problem is a bit more advanced that I am... (Error on line 30... )
Quote:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\test_exams\exams.class.php on line 30

and line 29+ is...
Quote:
$score = mysql_query("SELECT * FROM exam_results WHERE student_id = '{$e_st}' AND exam_id = '{$e_id}'");
while($row = mysql_fetch_assoc($score))
{

Anyway... here is the function... I'm giving up on it now :lol: Spent more than a freaking hour on it :shock: Should get started on my own project :D Maybe R_T would look over it...
PHP Code: [ Select ]
<?php
 function exams() {
   $con = mysql_connect('localhost','user','pass') or die(mysql_error());
   $select = mysql_select_db('database') 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}'");
      $e = mysql_fetch_assoc($process);
      $e_id = $e['exam_id'];
      $e_st = $e['student_id'];
      $table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
      $score = mysql_query("SELECT * FROM exam_results WHERE student_id = '{$e_st}' AND exam_id = '{$e_id}'");
      while($row = mysql_fetch_assoc($score))
      {
       $score = $row['result'];
       if(empty($score))
       {
         $score = "--";
       }
       $table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
      }
      $table .= "\t</tr>\n";
    }
    $table .= "</table>";
 
   echo $table;
   }
 }
?>
  1. <?php
  2.  function exams() {
  3.    $con = mysql_connect('localhost','user','pass') or die(mysql_error());
  4.    $select = mysql_select_db('database') or die(mysql_error());
  5.    if($con && $select)
  6.    {
  7.     $sql = "SELECT `exam_name` FROM exam ORDER BY exam_id";
  8.     $exams = mysql_query($sql);
  9.     // Get the table started
  10.     $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";
  11.     while($exam = mysql_fetch_assoc($exams))
  12.     {
  13.       $e_name = $exam['exam_name'];
  14.       $table .= "\t\t<td>\n\t\t\t<p>{$e_name}</p>\n\t\t</td>\n";
  15.     }
  16.     $table .= "\t</tr>\n";
  17.  
  18.     $students = mysql_query("SELECT * FROM students");
  19.     while($student = mysql_fetch_assoc($students))
  20.     {
  21.       $st_id = $student['student_id'];
  22.       $st_name = $student['student_name'];
  23.  
  24.       $process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
  25.       $e = mysql_fetch_assoc($process);
  26.       $e_id = $e['exam_id'];
  27.       $e_st = $e['student_id'];
  28.       $table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
  29.       $score = mysql_query("SELECT * FROM exam_results WHERE student_id = '{$e_st}' AND exam_id = '{$e_id}'");
  30.       while($row = mysql_fetch_assoc($score))
  31.       {
  32.        $score = $row['result'];
  33.        if(empty($score))
  34.        {
  35.          $score = "--";
  36.        }
  37.        $table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
  38.       }
  39.       $table .= "\t</tr>\n";
  40.     }
  41.     $table .= "</table>";
  42.  
  43.    echo $table;
  44.    }
  45.  }
  46. ?>

(The tabs (\t) between the table tags [e.g: <table>,<tr>,<td>...] are to make the source look cleaner...)

Have fun and hope you get it to work...

@R_T: I tried your code and it gives me the same forsaken error that I get...

BTW phplover: Thanks for that example database thing... helped me test the script(s).
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 8th, 2008, 9:58 pm

That error usually shows up when you use an incorrect query ... I didn't have time to go set up that database and actually test my code ... there may just be a typo or something ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 8th, 2008, 10:02 pm

Alright... I fixed that problem and made another one... hang on as I fix it...

[EDIT:] Here is the fixed and completely working version (The fix was actually an easy one... I actually had everything correct, just that I didn't put the right SQL process into that while(); loop and had a little too much coding...)

PHP Code: [ Select ]
<?php
 function exams() {
   $con = mysql_connect('localhost','user','pass') or die(mysql_error());
   $select = mysql_select_db('database') 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;
   }
 }
?>
  1. <?php
  2.  function exams() {
  3.    $con = mysql_connect('localhost','user','pass') or die(mysql_error());
  4.    $select = mysql_select_db('database') or die(mysql_error());
  5.    if($con && $select)
  6.    {
  7.     $sql = "SELECT `exam_name` FROM exam ORDER BY exam_id";
  8.     $exams = mysql_query($sql);
  9.     // Get the table started
  10.     $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";
  11.     while($exam = mysql_fetch_assoc($exams))
  12.     {
  13.       $e_name = $exam['exam_name'];
  14.       $table .= "\t\t<td>\n\t\t\t<p>{$e_name}</p>\n\t\t</td>\n";
  15.     }
  16.     $table .= "\t</tr>\n";
  17.  
  18.     $students = mysql_query("SELECT * FROM students");
  19.     while($student = mysql_fetch_assoc($students))
  20.     {
  21.       $st_id = $student['student_id'];
  22.       $st_name = $student['student_name'];
  23.  
  24.       $process = mysql_query("SELECT * FROM exam_results JOIN exam ON exam_results.exam_id = exam.exam_id WHERE student_id = '{$st_id}'");
  25.       $table .= "\t<tr>\n\t\t<td>\n\t\t\t{$st_name}\n\t\t</td>\n";
  26.       while($row = mysql_fetch_assoc($process))
  27.       {
  28.        $score = $row['result'];
  29.        $table .= "\t\t<td>\n\t\t\t{$score}\n\t\t</td>\n";
  30.       }
  31.       $table .= "\t</tr>\n";
  32.     }
  33.     $table .= "</table>";
  34.  
  35.    echo $table;
  36.    }
  37.  }
  38. ?>


Enjoy :D I know for sure that this works as I have tested it... Just one drawback to it that I'm trying to fix...

That drawback is that it doesn't do a <td>...</td> when the score is empty...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 8th, 2008, 10:25 pm

So Bogey what's the difference between your code and mine ... ? Because mine will actually still add a <td></td> if the exam result is empty ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 8th, 2008, 10:32 pm

righteous_trespasser wrote:
So Bogey what's the difference between your code and mine ... ? Because mine will actually still add a <td></td> if the exam result is empty ...

Probably because mine works? :lol: I tested your script and tried to fix the error...

Not only that this is my version :D Probably no real difference...

And those missing <td>...</td> don't make the code invalid... it's still xhtml strict valid code... the only reason I called it a drawback because I didn't knew it was valid... until now :)
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 8th, 2008, 11:51 pm

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: [ 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 ... ?
Let's leave all our *plum* where it is and go live in the jungle ...
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post July 8th, 2008, 11:51 pm

1, 2, 3, 4, 5

Post Information

  • Total Posts in this topic: 61 posts
  • Users browsing this forum: No registered users and 192 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
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.