I have been trying every trick I can to accomplish what I am looking for. I am not a noob but I am stuck on this. I am trying to make a "grid" of data from 3 tables. I want it to look like this:
Assignments | Assgn 1 | Assgn 2 | Assgn 3 | Assgn 4 | Final Grade
----------------------------------------------------------------------
Username 1 | Grade | Grade | Grade | Grade | Total
----------------------------------------------------------------------
Username 2 | Grade | Grade | Grade | Grade | Total
----------------------------------------------------------------------
Username 3 | Grade | Grade | Grade | Grade | Total
The first Table columns for members:
Memberid, Username
The assignments table:
id, title
The grades are kept in subassign :
id,assign,student,grade
assign is the id number of the assignment in the assignments table student is the Memberid from the members table.
I have been looking into this for a couple of days.
The one that gave some results is this query
$result = mysql_query("SELECT assignments.title,subassign.grade, members.Username FROM assignments, subassign, members WHERE members.Memberid = subassign.student and assignments.id = subassign.assign ",$connect);
it links the information correctly but I can't get it to display the records like I want. It just creates a new row in php. Can someone please help. I am about to throw my computer out the window.
You can use joins for this like so:
SELECT
members.title AS 'Assignments',
assignment1.grade AS 'Assign1',
assignment2.grade AS 'Assign2',
assignment3.grade AS 'Assign3',
assignment3.grade AS 'Assign4'
FROM members
INNER JOIN subassign AS assignment1 ON assignment1.person = members.MemberId AND assign = 1
INNER JOIN subassign AS assignment2 ON assignment2.person = members.MemberId AND assign = 2
INNER JOIN subassign AS assignment3 ON assignment3.person = members.MemberId AND assign = 3
INNER JOIN subassign AS assignment4 ON assignment4.person = members.MemberId AND assign = 4
Note that if you want to return dynamic columns (which I assume you do) based on the assignments in your table, your best bet would be to dynamically generate a query similar to the above. See below for some very rough example code that would generate a dynamic query that you want (this is wholly untested):
$results = mysql_query("SELECT id, title FROM assignments");
$query = "SELECT
members.title AS 'Assignments',";
$joins = "";
while($row = mysql_fetch_assoc($results))
{
$name = 'assignment' . $row['id'];
$query .= "
" . $name . ".grade AS '" . $row['title'] . "',";
$joins .= "
LEFT JOIN subassign AS " . $name . " ON " . $name . ".person = members.MemberId AND " . $name . ".assign = " . $row['id'];
}
$query = substr($query, 0, -1) . " FROM members" . $joins;
$result = mysql_query($query);
Not 100% sure if I got the query right, but you get the idea:
SELECT
a.title,
s.grade,
m.Username
FROM
members m
LEFT JOIN
subassign AS s ON s.student = m.Memberid
LEFT JOIN
assignments AS a ON a.id = s.assign
This is basically a pivot of the data but MySQL does not have a pivot. So you can replicate the functionality by using an aggregate function with a CASE similar to this:
select m.username as assignments,
max(case when s.assign = 1 then s.grade end) Assign1,
max(case when s.assign = 2 then s.grade end) Assign2,
max(case when s.assign = 3 then s.grade end) Assign3,
max(case when s.assign = 4 then s.grade end) Assign4,
avg(s.grade) FinalGrade
from members m
left join subassign s
on m.memberid = s.student
left join assignments a
on s.assign = a.id
group by m.username
Result:
| ASSIGNMENTS | ASSIGN1 | ASSIGN2 | ASSIGN3 | ASSIGN4 | FINALGRADE |
--------------------------------------------------------------------
| username 1 | 50 | 68 | 32 | 68 | 54.5 |
| username 2 | 75 | 86 | 89 | 36 | 71.5 |
| username 3 | 99 | 70 | 24 | 100 | 73.25 |