Here is the outline:
I have a table, which is holding data for student's report cards. Very normalized, so studentid, school_id, etc. Also, the table holds 3 comments, comment1_id, comment2_id, comment3_id. These are numbers, that are linked to a table called grade_comments, with grade_id being the number, and grade_desc being the actual comment.
Teacher's can choose 1, 2, or 3 comments. How do I build the query to extract this information? I am really stumped!
Any help would be excellent.
Doug
Getting multiple comments
Moderator: General Moderators
for starters, you should consider restructuring your tables so that the comments table references the report card table by its ID. Then you can have as many as you want (they won't be tied to the specific fields on the report card table).
for a query like that you'd use:
that of course assumes that you're using a url param to identify the student for whom you're looking for information.
for a query like that you'd use:
Code: Select all
$query = "select r.*,c.comments from report_cards r, comments c where r.id = c.reportcardid and r.id = ".$_GET['studentid'];Well the comment table basically has 300 comments in it that teachers can pull from. And for ease of formatting the report cards, we made it so you could have a maximum of 3 comments per class.
So each entry in the report card table is one class grade with comments.
John Smith, Math, Mr. Franks, Good work, Bad work, lousy student. (that sort of thing)
21345, 8, 16, 100, 109, 56 (all stored as numbers)
21345, 8, 16, 100 (here, Mr. Franks decided one comment was enough)
So this is what I am dealing with!
Thanks.
Doug
So each entry in the report card table is one class grade with comments.
John Smith, Math, Mr. Franks, Good work, Bad work, lousy student. (that sort of thing)
21345, 8, 16, 100, 109, 56 (all stored as numbers)
21345, 8, 16, 100 (here, Mr. Franks decided one comment was enough)
So this is what I am dealing with!
Thanks.
Doug
ahh ok, then yes in that situation your table structure makes sense.
try a query like this:
try a query like this:
Code: Select all
$query = "select r.*, c.comment from report_cards r, comments c where r.comment_1 = c.id";