Need a little Display help here
Moderator: General Moderators
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
Need a little Display help here
Hi,
I need some help.
I have a query that returns two columns. Each record is a quiz number in the first column and a score in the second column. So my query might return:
Quize# | Score
------------------
1 30
3 60
6 23
7 97
8 48
9 12
And I want to print that out, but filling in the quiz #'s missing from the first column with scores of 0. So output this:
Quize# | Score
------------------
1 30
2 0
3 60
4 0
5 0
6 23
7 97
8 48
9 12
10 0
So how can I get it that way?
I imagine I could put the values form the query into an array, and then fill in the values of 0 for the numbers that are missing or something. But my array skills have gone to SH#%@ tonight.
I need some help.
I have a query that returns two columns. Each record is a quiz number in the first column and a score in the second column. So my query might return:
Quize# | Score
------------------
1 30
3 60
6 23
7 97
8 48
9 12
And I want to print that out, but filling in the quiz #'s missing from the first column with scores of 0. So output this:
Quize# | Score
------------------
1 30
2 0
3 60
4 0
5 0
6 23
7 97
8 48
9 12
10 0
So how can I get it that way?
I imagine I could put the values form the query into an array, and then fill in the values of 0 for the numbers that are missing or something. But my array skills have gone to SH#%@ tonight.
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
your query might look something like:
select count(scores.score) as scorecount, quiz.quizid
from quiz full outer join scores on quiz.quizid = scores.scoreid
group by quiz.quizid
order by quiz.quizid
this should give the intended output. not tested of course.
and you might have to switch the group by and order by clauses with each other if an error gets spit up.
select count(scores.score) as scorecount, quiz.quizid
from quiz full outer join scores on quiz.quizid = scores.scoreid
group by quiz.quizid
order by quiz.quizid
this should give the intended output. not tested of course.
and you might have to switch the group by and order by clauses with each other if an error gets spit up.
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
Ok, I got some syntax errors (MySQL 4.0.x), so I'm sure I don't understand what's going on. So here's what I typed in phpMyAdmin:
Both of these columns are from the same table (tblCourseInstanceSelfCheckScore), the name of first column is "scsLessonID" and the name of the second score column is "scsSelfCheckScore". May be you could fill in what I did wrong based on what you gave me.? Keep in mind, that this table contains lots of records for quiz scores that have been taken. There is also an enrollmentID column in this table, that associates each record to a course. And some courses have more Lessons than others, and every Student can take any of the Lesson Self Checks (Quizes) in a course as much as they want. So this table only stores scores for the Lesson Self Check's (Quizes) that have been taken. I just want to be able to print out a list (1-x) of the Lessons and put the scores for the ones that have been taken beside them and 0's besides the records that don't exist basically. Hope I'm not confusing anyone by saying all this.
Appreciate this, thx!
Code: Select all
SELECT COUNT (tblcourseinstanceselfcheckscore.scsSelfCheckScore) AS scorecount, tblcourseinstanceselfcheckscore.scsLessonID
FROM tblcourseinstanceselfcheckscore FULL OUTER JOIN tblcourseinstanceselfcheckscore ON tblcourseinstanceselfcheckscore.scsLessonID = tblcourseinstanceselfcheckscore.scsSelfCheckScore
GROUP BY tblcourseinstanceselfcheckscore.scsLessonID
ORDER BY tblcourseinstanceselfcheckscore.scsLessonIDAppreciate this, thx!
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
I am so close now! You are my hero Microthick!
I just now need to get the scores that belong to a particular enrollmentID and the Lessons from a courseID.
Code: Select all
SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID ORDER BY unlLessonNumber- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
Ok, I'm 95% there. Little problem holding me back. My query now gets everything I want, except it also gets all the records from the scores table (tblcourseinstanceselfcheckscore) for any user, and I only want it to match the records for a particular enrollmentID, like say 89. So if I do:
SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson, tblcourseunit LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID
WHERE (tblunitlesson.unlUnitID=tblcourseunit.cruCourseUnitID) AND (tblcourseunit.cruCourseID='1') ORDER BY unlLessonNumber
I get what I want, mostly.
But If I add the enrollmentid='89' or whatever to the where, it doesn't do like the first query, it only shows the records that have scores.
SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson, tblcourseunit LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID
WHERE (tblunitlesson.unlUnitID=tblcourseunit.cruCourseUnitID) AND (tblcourseunit.cruCourseID='1') AND (tblcourseinstanceselfcheckscore.scsEnrollmentID='89') ORDER BY unlLessonNumber
So, help? pleasee.
SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson, tblcourseunit LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID
WHERE (tblunitlesson.unlUnitID=tblcourseunit.cruCourseUnitID) AND (tblcourseunit.cruCourseID='1') ORDER BY unlLessonNumber
I get what I want, mostly.
But If I add the enrollmentid='89' or whatever to the where, it doesn't do like the first query, it only shows the records that have scores.
SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson, tblcourseunit LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID
WHERE (tblunitlesson.unlUnitID=tblcourseunit.cruCourseUnitID) AND (tblcourseunit.cruCourseID='1') AND (tblcourseinstanceselfcheckscore.scsEnrollmentID='89') ORDER BY unlLessonNumber
So, help? pleasee.
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
It would be a big help if you could draw out a visual representation of what you are getting and what you WANT to get, with the column names at the top of the table.
Your table names and columns have such long names.. getting confused.
I think if you want to do what you're doing, you'll have to use UNION and union two queries.
It would be something like:
Query 1: Get all scores and lessions that 89 has done.
UNION
Query 2: Get all the lessions that 89 has not done and assume the score is 0.
Your table names and columns have such long names.. getting confused.
I think if you want to do what you're doing, you'll have to use UNION and union two queries.
It would be something like:
Query 1: Get all scores and lessions that 89 has done.
UNION
Query 2: Get all the lessions that 89 has not done and assume the score is 0.
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
And see I thought about trying that with PHP, get all the Lessons/Scores that 89 has done, and then put them into an array and then add in the Lesson #'s and Scores of 0 for the Lessons that aren't in there. But my php skills just weren't cutting it. And see the LEFT JOIN almost gets it, in fact it does get it, just with extra records from other enrollmentID's that don't belong. Would it help if I reexplained the tables/fields with simple made up names?
I'll see what I can come up with for a drawing.
I'll see what I can come up with for a drawing.
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
Using PHP would be easier, I think.
...
untested of course...
Code: Select all
// query 1
$scoreArray = array();
while (row = mysql_fetch_array($result)) {
$tempArray = array_merge($scoreArray, array(rowї"lessionID"] => rowї"score"]));
}
// do the same with query two
while (row2 = mysql_fetch_array($result2)) {
$tempArray = array_merge($scoreArray, array(row2ї"lessionID"] => "0"));
}
// order by lessionID
ksort($scoreArray);untested of course...
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
Except, going the php route, there would be no query2. This way, would just need the first query to pull the Lesson # (scsLessonID) and Score (scsSelfCheckScore) for each record in the scores table (tblCourseInstanceSelfCheckScore) where the enrollmentid is '89'.
Then I know for sure there are 15 Lessons in each course. So would need to merge the Lesson #'s that don't exist in the first query with the first array with scores of 0.
Then I know for sure there are 15 Lessons in each course. So would need to merge the Lesson #'s that don't exist in the first query with the first array with scores of 0.
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
My god this is driving me so nucking futz. Hopefully this will spell it out..
This of course, doens't return anything but a blank page. The query however, is fine.
Code: Select all
// This Query Will Return This
/*
Lesson # | Score
----------------
1 30
2 20
3 96
4 45
5 29
9 10
15 87
*/
$sql ="SELECT scsLessonID, scsSelfCheckScore FROM tblcourseinstanceselfcheckscore WHERE (scsEnrollmentID='89')";
$result=mysql_query($sql) or die("Query Failed");
$scoreArray = array();
while ($row = mysql_fetch_array($result)) {
$tempArray = array_merge($scoreArray, array($row["scsLessonID"] => $row["scsSelfCheckScore"]));
}
// 15 Lessons: So Fill In Lesson #'s + Scores of 0 for Lesson #'s
// The first query didn't return.
for ($i=1; $i < 16; $i++) {
$tempArray = array_merge($scoreArray, array($i => "0"));
}
// order by lessionID
ksort($scoreArray);
// show the stuff
while (list($key, $val) = each($scoreArray)) {
echo "Lesson: $key = $val\n";
}
/*
And I want to see this:
Lesson # | Score
----------------
1 30
2 20
3 96
4 45
5 29
6 0
7 0
8 0
9 10
10 0
11 0
12 0
13 0
14 0
15 87
*/
mysql_close();-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
You could do it rather grotesquely.
1) Query and get all lessons.
2) Loop through lessons and run a query to get the score for 89 for each lesson.
3) If mysql_num_rows = 0, then score is 0.
If you don't like working with arrays, you could always create a temporary MySQL table to hold your data (Lesson, Score) then delete it when you're done.
1) Query and get all lessons.
2) Loop through lessons and run a query to get the score for 89 for each lesson.
3) If mysql_num_rows = 0, then score is 0.
If you don't like working with arrays, you could always create a temporary MySQL table to hold your data (Lesson, Score) then delete it when you're done.
- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
I fixed my problem. Alls I needed was a little array syntax help. This method is the best, and doens't involved complex queries and relies on php to do the sorting. Can't believe it took me all weekend to fix this .. But a big thank you to infolock and microthick for helping me brainstorm ideas ..
Code: Select all
$sql ="SELECT scsLessonID, scsSelfCheckScore FROM tblcourseinstanceselfcheckscore WHERE (scsEnrollmentID='89')";
$result=mysql_query($sql) or die("Query Failed");
$scores = array();
while ($row = mysql_fetch_array($result)) {
$scores[$row['scsLessonID']] = $row['scsSelfCheckScore'];
}
for($i=1; $i < 16; $i++) {
if ($scores[$i] == '') {
echo "Lesson: $i = 0<br>\n";
} else {
echo "Lesson: $i = " . $scores[$i] . "<br>\n";
}
}