PHP MYSQL Gradebook

Question

joeedel89 0 Newbie Poster

16 Years Ago

Hello and Thanks,
I am trying to create a teacher grade book for my school, I have a mysql database 'gradebook' and two tables 'students' and 'data' (mysql code below) to start, I am also using PHP. I have googled this to death and can't seem to come up with anything that I can understand; I have a two part question.

First Part:
How do I get the data arranged into a table like to one below:

|   Test1 (100pts)  |   Test2(100pts)  |
-----------------------------------------------------------------------------
Joe Edel        |   100                  |     80                |
-----------------------------------------------------------------------------
Angela Edel   |   99                    |    95                 |
-----------------------------------------------------------------------------

Second Part:
Is there a term for the way/method I am extracting the data and arranging it (multi-diemsional array?). The reason I ask is that this type of query seems different at a fundamental level than a standard mysql query that most website use as an example.

Thanks,
Joe

-- phpMyAdmin SQL Dump
-- version 2.11.7.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 06, 2009 at 02:01 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `gradebook`
--

-- --------------------------------------------------------

--
-- Table structure for table `data`
--

CREATE TABLE `data` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` int(11) default NULL,
  `assignment_name` varchar(25) default NULL,
  `points_possible` int(3) default NULL,
  `points_earned` int(3) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `data`
--

INSERT INTO `data` VALUES(1, 1, 'test1', 100, 90);
INSERT INTO `data` VALUES(2, 1, 'test2', 100, 85);
INSERT INTO `data` VALUES(3, 2, 'test1', 100, 99);
INSERT INTO `data` VALUES(4, 2, 'test2', 100, 100);

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL auto_increment,
  `student_name` varchar(25) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `students`
--

INSERT INTO `students` VALUES(1, 'Joe Edel');
INSERT INTO `students` VALUES(2, 'Angela Edel');

data-structure mysql php

4 Contributors
4 Replies
795 Views
3 Months Discussion Span
Latest Post 16 Years Ago Latest Post by sallecpt

Dani AI

Generated 10 Months Ago

Short answer: fetch the rows and pivot them in PHP into a two‑dimensional associative array, then render an HTML table with dynamic headers. That pattern is usually called a "pivot" or "crosstab" (the in‑memory structure is simply a 2D associative array: student => [assignment => score]). is right that you output HTML tables; 's idea of collecting rows into a variable works — scale it by first building an ordered list of assignments (columns) and a map of students to assignment scores, then emit the header and rows.

Example workflow (high level + runnable sketch):

Query the assignment list (ordered) so column order is stable:

SELECT DISTINCT assignment_name, points_possible FROM data ORDER BY assignment_name;

Query students with their scores (use LEFT JOIN so students with no score still appear):

SELECT s.id AS sid, s.student_name, d.assignment_name, d.points_earned
FROM students s
LEFT JOIN data d ON d.student_id = s.id
ORDER BY s.student_name, d.assignment_name;

In PHP build:
- an ordered array $assignments and map $pointsPossible[assignment] => points,
- a $students[sid] = ['name'=>..., 'scores'=>[assignment=>points]],
- then render <table>: first header row (assignment headers with points possible), then for each student row print cells in the same assignment order using isset($students[$sid]['scores'][$a]) ? value : '-' .

Why this over trying to pivot in SQL: SQL pivots require fixed column names or dynamic SQL; doing it in the app is simpler and flexible. A few practical notes: use PDO or mysqli (avoid deprecated mysql_*), use prepared statements, add indexes on data.student_id (and assignment_name if you filter by it), normalize assignments into an assignments table if names must be unique, and for very large classes page or stream results to avoid large in‑memory arrays.

All 4 Replies

zortec 11 Junior Poster

16 Years Ago

The answer to your first question is to create a table using HTML.

This link should help you out with the basics.

As to the second part of your question, you need to fetch the data in rows and display it in the table.

Edited 16 Years Ago by zortec because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

joeedel89 0 Newbie Poster · Answer 1 · 2009-12-07T21:22:46+00:00

Hello,
Sorry I should have been more specific, I need to know how to extract data from the aforementioned tables in a way that would lend itself to the table that I described. I don't think the standard SELECT students.student_name, data.points_possible, data.points_earned WHERE data.student_id = students.id would give me the results in a what that I could easily put them into the table the way that I want.

Arianna 1 Light Poster · Answer 2 · 2009-12-08T00:42:45+00:00

You could use a $content variable.
So basically:

$content="<table>";
while($i<$numrows) { //while there are still rows in the table
$grade=@mysql_result($result,$i,"grade"); //The grade
$name=@mysql_result($result,$i,"name"); //Student's name
$content=$content."<tr><td>".$name."</td><td>".$grade."</td>";
}
$content=$content."</table>";

That would put a table in the variable $content. Just echo it where you want it... You need to use the select query and stuff, but those are the basics.

sallecpt 0 Junior Poster in Training · Answer 3 · 2010-04-03T03:12:47+00:00

We;ve been using a custom developed gradebook for our school in Robenson, but changed to, ummm... fox gradebook? about a year ago. Its really effective and easy and instead of having a guru to load the exam marks, each class sup now only update his/her own excel file. yes the gradebook works with an excel file, and then only the excel file is loaded with a click, and walla.

excellent software, and saves us so much time and effort (and money)

thank address is

oh btw i dunno much of programming but i know that we got the source files which our it guy said is magnicifent for the price.