Tony77 Tony77 - 2 months ago 6
MySQL Question

Mysql query to output the comparission between subqueries

How can I create a third column on my query ouput as 'Pass' or 'Fail' when the score is equal to the maxScore

<?php

$database =& JFactory::getDBO();

//Declare Variables
$user = JFactory::getUser();
$id = $user->get('id');
$name = $user->get('name');

// Display quizzes
echo "</br>";
echo "<h1>";
echo "Quizzes History for : " ;
echo "<b>";
echo $name;
echo "</h1>";
echo "</b>";


echo "</br>";
echo "</br>";

$database->setQuery(" SELECT distinct qui.title AS Course_Name,

(SELECT sum(score)
FROM jos_jquarks_quizzes_answersessions

WHERE score IS NOT NULL AND quizsession_id = quizSession.id
AND status <> -1 ) AS score,

( SELECT count(distinct question_id) FROM jos_jquarks_quizzes_answersessions
WHERE quizsession_id = quizSession.id ) AS maxScore,


DATE_FORMAT(quizSession.finished_on,'%W, %M %e, %Y @ %h:%i %p') As Finished FROM jos_jquarks_quizsession AS quizSession

LEFT JOIN jos_jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id
LEFT JOIN jos_jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id
LEFT JOIN jos_jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id
LEFT JOIN jos_jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id
LEFT JOIN jos_jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id
LEFT JOIN jos_jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id
WHERE sessionWho.user_id = " .$id ) ;

if (!$database->query()) { //write data and if error occurs alert
echo "<script> alert('".$database->getErrorMsg()."'); </script>";
}
$tableStyle = "padding: 5px;border:1px solid black";
$tdStyle = "padding:5px";
$thStyle = "padding:7px";

$row = $database->loadRowList();
if ( 0<count($row)) {
echo '<table style="', $tableStyle, '" cellpadding="7" cellspacing="7">'; // with echo, commas are slightly more effective than dots
echo '<tr><th style="', $thStyle, '" align=center>Quiz Title </th><th style="', $thStyle, '" align=center> Score </th>
<th style="', $thStyle, '" align=center>Maximum Score </th>
<th style="', $thStyle, '" align=center>Finished On </th></tr>';

$row = $database->loadRowList();
foreach($row as $valuearray) {
echo '<tr align="center">';

foreach($valuearray as $field){
echo '<td style="', $tdStyle, '" align=center>', $field, '</td>'; // note: there was an error here
} // field
echo '</tr>'; // note: apostrophs rather than " every time it is possible
} // $valuearray
echo '</table>';
} // if count
?>

Answer

Use variables:

SELECT DISTINCT qui.title AS Course_Name,

  @score := ( SELECT sum(score) FROM jos_jquarks_quizzes_answersessions     
     WHERE score IS NOT NULL AND quizsession_id = quizSession.id     
     AND status <> -1 ) AS score, 

  @maxScore := ( SELECT count(distinct question_id)
    FROM jos_jquarks_quizzes_answersessions            
    WHERE quizsession_id = quizSession.id ) AS maxScore,

  IF( @score = @maxScore, 'Pass', 'Fail' ) AS passOrFail

  ...

But I'd personally do this test in my PHP application.

Comments