Need a little Display help here

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
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

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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!
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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...
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

I dunno dude. It's over my head now.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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";
    }
}
Post Reply