Page 1 of 1

Need a little Display help here

Posted: Sat Dec 06, 2003 5:31 am
by Pyrite
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.

Posted: Sat Dec 06, 2003 12:12 pm
by microthick
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.

Posted: Sat Dec 06, 2003 3:44 pm
by Pyrite
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:

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.scsLessonID
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!

Posted: Sat Dec 06, 2003 4:22 pm
by Pyrite
Ok, I get it now. I do have another table that has all the Quize #'s yea, so I could OUTER JOIN it to the Scores tables WHERE the Quiz #'s intersect? After looking at some OUTER JOIN docs, the OUTER JOIN is the way I want to go. Thanks! But I still gotta make it work. Give me a few

Posted: Sat Dec 06, 2003 4:41 pm
by Pyrite
I am so close now! You are my hero Microthick!

Code: Select all

SELECT unlLessonNumber, scsSelfCheckScore FROM tblunitlesson LEFT JOIN tblcourseinstanceselfcheckscore ON tblunitlesson.unlLessonNumber = tblcourseinstanceselfcheckscore.scsLessonID  ORDER BY unlLessonNumber
I just now need to get the scores that belong to a particular enrollmentID and the Lessons from a courseID.

Posted: Sat Dec 06, 2003 5:23 pm
by Pyrite
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.

Posted: Sat Dec 06, 2003 6:58 pm
by microthick
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.

Posted: Sat Dec 06, 2003 7:05 pm
by Pyrite
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.

Posted: Sat Dec 06, 2003 7:22 pm
by microthick
Using PHP would be easier, I think.

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...

Posted: Sat Dec 06, 2003 7:51 pm
by Pyrite
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.

Posted: Sat Dec 06, 2003 8:36 pm
by Pyrite
My god this is driving me so nucking futz. Hopefully this will spell it out..

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();
This of course, doens't return anything but a blank page. The query however, is fine.

Posted: Sat Dec 06, 2003 8:41 pm
by microthick
I dunno dude. It's over my head now.

Posted: Sat Dec 06, 2003 8:46 pm
by Pyrite
No, it's not. What I want to do is so ungodly simple. I don't blame you for not wanting to fsck with it anymore, unfortunately I have to though.

Posted: Sat Dec 06, 2003 9:02 pm
by microthick
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.

Posted: Mon Dec 08, 2003 4:03 am
by Pyrite
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";
    }
}