Jump to content
nasimuddin58

My QUIZ site's Ranking page is not working properly

Recommended Posts

I have created a quiz site for my users to win some cash prize. They have to login and play(only one chance per test per user). And on the leader-board every one can see everyone's position according to their acquired marks. Each test has unique "testid". Each test has 10 questions (Serials 1-10). Now comes to the problem: Suppose Question 1 & Question 4 of Test 1 have the right answer "Option B". On the other hand Question 1 & Question 4 of Test 2 have the right answer "Option B". When a user wants to view the leader-board of "Test 1", the page adds up the marks of correct answers from Question 1 to Question 2 of Test 1. But it also adding the marks of Question 1 and Question 4 of Test 2 (As the answers of Q1 & Q4 of both the tests are same.) Although Test 2 has not yet answered by that user.

Here is the code: 

 

<?php
error_reporting(0);
session_start();
include_once 'oesdb.php';
if(!isset($_SESSION['stdname'])){
    $_GLOBALS['message']="Session Timeout.Click here to <a href=\"index.php\">Re-LogIn</a>";
}
else if(isset($_REQUEST['logout'])) {
    //Log out and redirect login page
        unset($_SESSION['stdname']);
        header('Location: index.php');

    }
    else if(isset($_REQUEST['dashboard'])) {
        //redirect to View Result

            header('Location: stdwelcome.php');

        }
        else if(isset($_REQUEST['dashboard'])) {
        //redirect to dashboard

            header('Location: ranking.php');

        }

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
    <head>
        <title>OES-View Result</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
        <meta http-equiv="CACHE-CONTROL" content="NO-CACHE"/>
        <meta http-equiv="PRAGMA" content="NO-CACHE"/>
        <meta name="ROBOTS" content="NONE"/>

        <link rel="stylesheet" type="text/css" href="oes.css"/>
        <script type="text/javascript" src="validate.js" ></script>
    </head>
    <body >
        <?php

        if($_GLOBALS['message']) {
            echo "<div class=\"message\">".$_GLOBALS['message']."</div>";
        }
        ?>
        <div id="container">
            <div class="header">
                <img style="margin:10px 2px 2px 10px;float:left;" height="80" width="200" src="images/logo.gif" alt="OES"/><h3 class="headtext"> &nbsp;BCS QUIZ System </h3><h4 style="color:#ffffff;text-align:center;margin:0 0 5px 5px;"><i>...because Money Matters</i></h4>
            </div>
            <form id="summary" action="ranking.php" method="post">
                <div class="menubar">
                    <ul id="menu">
                        <?php if(isset($_SESSION['stdname'])) {
                        // Navigations

                            ?>
                        <li><input type="submit" value="LogOut" name="logout" class="subbtn" title="Log Out"/></li>
                            <?php  if(isset($_REQUEST['testid'])) { ?>
                        <li><input type="submit" value="Back" name="back" class="subbtn" title="Manage Results"/></li>
                            <?php }else { ?>
                        <li><input type="submit" value="DashBoard" name="dashboard" class="subbtn" title="Dash Board"/></li>
                            <?php } ?>

                    </ul>


                </div>
                <div class="page">

<?php
                        if(isset($_REQUEST['testid'])) {
                            $result=executeQuery("select t.testname,DATE_FORMAT(t.testfrom,'%d %M %Y') as fromdate,DATE_FORMAT(t.testto,'%d %M %Y %H:%i:%S') as todate,sub.subname,prize,IFNULL((select sum(marks) from question where testid=".$_REQUEST['testid']."),0) as maxmarks from test as t, subject as sub where sub.subid=t.subid and t.testid=".$_REQUEST['testid'].";") ;
                            if(mysql_num_rows($result)!=0) {

                                $r=mysql_fetch_array($result);
                                ?>
                    <table cellpadding="20" cellspacing="30" border="0" style="background:#ffffff url(../images/page.gif);text-align:left;line-height:20px;">
                        <tr>
                            <td colspan="2"><h3 style="color:#0000cc;text-align:center;">Test Summary</h3></td>
                        </tr>
                        <tr>
                            <td colspan="2" ><hr style="color:#ff0000;border-width:4px;"/></td>
                        </tr>
                        <tr>
                            <td>Test Name</td>
                            <td><?php echo htmlspecialchars_decode($r['testname'],ENT_QUOTES); ?></td>
                        </tr>
                        <tr>
                            <td>Subject Name</td>
                            <td><?php echo htmlspecialchars_decode($r['subname'],ENT_QUOTES); ?></td>
                        </tr>
                        <tr>
                            <td>Validity</td>
                            <td><?php echo $r['fromdate']." To ".$r['todate']; ?></td>
                        </tr>
                        <tr>
                            <td>Max. Marks</td>
                            <td><?php echo $r['maxmarks']; ?></td>
                        </tr><tr>
                            <td>Prize Breakup</td>
                            <td><?php echo $r['prize']; ?></td>
                        </tr>
                        <tr><td colspan="2"><hr style="color:#ff0000;border-width:2px;"/></td></tr>

                            <tr><td colspan="2"><h3 style="color:#0000cc;text-align:center;">Leader Board</h3></td></tr>

                        <tr>
                            <td colspan="2"><h6 style="color:red;text-align:justify;">In the event of a tie, the winning Participants shall be declared Winners and the prize shall be equally divided among such Participants. [Eg: If 2 participants acquire 2nd position, then the sum of 2nd Prize and 3rd Prize will be distributed equally among them. If 4 participants acquire 1st position, then the sum of 1st, 2nd and 3rd prize will be equally distributed among them. For example if 5 participants acquired 1st position. Then (1st + 2nd + 3rd Prize) Rs. 30+20+10=60 will be distributed equally among them. In that case they all will get Rs. 12 (60/5) as winnings. Rest participants will not be eligibile for any prize.]</h6></td>
                        </tr>
                        </tr>
                        <tr>
                            <td colspan="2" ><hr style="color:#ff0000;border-width:4px;"/></td>
                        </tr>

                    </table>

                                <?php
                                $result1=executeQuery("select s.stdname,name,contactno,IFNULL((select sum(q.marks) from studentquestion as sq,question as q where q.qnid=sq.qnid and sq.testid=".$_REQUEST['testid']." and sq.stdid=st.stdid and sq.stdanswer=q.correctanswer),0) as om from studenttest as st, student as s where s.stdid=st.stdid and st.testid=".$_REQUEST['testid']." ORDER BY om DESC;" );

                                if(mysql_num_rows($result1)==0) {
                                    echo"<h3 style=\"color:#0000cc;text-align:center;\">No Students Yet Attempted this Test!</h3>";
                                }
                                else {
                                    ?>
                    <table cellpadding="30" cellspacing="10" class="datatable">
                        <tr>
                            <th>Rank</th>
                            <th>Account Number</th>
                            <th>Name</th>
                            <th>Contact</th>
                            <th>Obtained Marks</th>
                            <th>Result(%)</th>

                        </tr>
                                        <?php
                                        while($r1=mysql_fetch_array($result1)) {

                                            ?>
                        <tr>
                            <td><?php echo ++$counter; ?></td>
                            <td><?php echo htmlspecialchars_decode($r1['stdname'],ENT_QUOTES); ?></td>
                            <td><?php echo htmlspecialchars_decode($r1['name'],ENT_QUOTES); ?></td>
                            <td><?php
$phone = $r1['contactno'];
$result = substr($phone, 0, 2);
$result .= "*****";
$result .= substr($phone, 7, 3);
echo $result;
?></rd>
                            <td><?php echo $r1['om']; ?></td>
                            <td><?php echo ($r1['om']/$r['maxmarks']*100)." %"; ?></td>


                        </tr>
                                        <?php

                                        }

                                    }
                                }
                                else {
                                    echo"<h3 style=\"color:#0000cc;text-align:center;\">Something went wrong. Please logout and Try again.</h3>";
                                }
                                ?>
                    </table>


                        <?php

                        }
                        else {
                            $result=executeQuery("select t.testid,t.testname,DATE_FORMAT(t.testfrom,'%d %M %Y') as fromdate,DATE_FORMAT(t.testto,'%d %M %Y %H:%i:%S') as todate,sub.subname,prize,(select count(stdid) from studenttest where testid=t.testid) as attemptedstudents from test as t, subject as sub where sub.subid=t.subid and CURRENT_TIMESTAMP>t.testfrom and CURRENT_TIMESTAMP<t.validto;");
                            if(mysql_num_rows($result)==0) {
                                echo "<h3 style=\"color:#0000cc;text-align:center;\">No Tests Yet...!</h3>";
                            }
                            else {
                                $i=0;

                                ?>
                    <table cellpadding="30" cellspacing="10" class="datatable">
                        <tr>
                            <th>Quiz Name</th>
                            <th>Prize</th>
                            <th>Subject Name</th>
                            <th>Quiz Validity</th>
                            <th>Attempted Students</th>
                            <th>Check Leader Board</th>
                        </tr>
            <?php
                                    while($r=mysql_fetch_array($result)) {
                                        $i=$i+1;
                                        if($i%2==0) {
                                            echo "<tr class=\"alt\">";
                                        }
                                        else { echo "<tr>";}
                                        echo "<td>".htmlspecialchars_decode($r['testname'],ENT_QUOTES)."</td><td>".$r['prize']."</td>"
                                            ."<td>".htmlspecialchars_decode($r['subname'],ENT_QUOTES)."</td>
                                            <td>".$r['fromdate']." To ".$r['todate']." PM </td>
                                            <td>".$r['attemptedstudents']."</td>"
                                            ."<td class=\"tddata\"><a title=\"Details\" href=\"ranking.php?testid=".$r['testid']."\"><img src=\"images/detail.png\" height=\"30\" width=\"40\" alt=\"Check Leader Board\" /></a></td></tr>";
                                    }
                                    ?>
                    </table>
        <?php
                            }
                        }
                        closedb();
                    }

                    ?>

                </div>
            </form>
            <div id="footer">
          <p style="font-size:70%;color:#ffffff;"> Developed By-<b>BCS Team</b><br/> </p>
      </div>
      </div>
  </body>
</html>

 

Share this post


Link to post
Share on other sites

This will likely be an issue with your query. Please post just the SQL of the one query that's producing the incorrect results, and also show us your database table structures - hopefully with some sample data so it's easier to understand.

Share this post


Link to post
Share on other sites

In your first and third queries you refer to test,testfrom and test.testto

Quote

select t.testid
      ,t.testname
      ,DATE_FORMAT(t.testfrom,'%d %M %Y') as fromdate
      ,DATE_FORMAT(t.testto,'%d %M %Y %H:%i:%S') as todate
      ,sub.subname
      ,prize
      ,( select count(stdid)
         from studenttest
         where testid=t.testid
       ) as attemptedstudents
from test as t, subject as sub
where sub.subid=t.subid
      and CURRENT_TIMESTAMP > t.testfrom
      and CURRENT_TIMESTAMP < t.validto

yet you have "test.validto" in the final WHERE clause.

Share this post


Link to post
Share on other sites

Revrese engineering your queries, it appears your table structure is like the model below.

If I am right, the "studenttest" table is redundant.

Your first and third queries are almost identical and both contain inefficient dependent subqueries. You could combine them into a single query

select t.testid
      ,t.testname
      ,DATE_FORMAT(t.testfrom,'%d %M %Y') as fromdate
      ,DATE_FORMAT(t.testto,'%d %M %Y %H:%i:%S') as todate
      ,sub.subname
      ,prize
      ,COUNT(DISTINCT stdid) as attemptedstudents
      , MAX(totmarks) as maxmarks
FROM (
			SELECT s.stdid
				 , s.stdname
				 , sq.testid
				 , IFNULL(SUM(q.marks),0) as totmarks
			FROM student s
				INNER JOIN studentquestion sq USING (stdid)
				LEFT JOIN question q 
					ON sq.testid = q.testid
						AND sq.qnid = q.qnid
						AND sq.stdanswer = q.correctanswer
			GROUP BY sq.testid, s.stdid
        ) tots
		INNER JOIN test t USING (testid)
        INNER JOIN subject sub USING (subid)
GROUP BY testid;
+--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+
| testid | testname | fromdate         | todate                    | subname  | prize | attemptedstudents | maxmarks |
+--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+
|      1 | Test 1   | 01 January 2019  | 31 January 2019 00:00:00  | Science  |    50 |                 5 |       40 |
|      2 | Test 2   | 01 February 2019 | 28 February 2019 00:00:00 | Science  |   100 |                 3 |       60 |
|      3 | Test 3   | 01 January 2019  | 31 January 2019 00:00:00  | Politics |    50 |                 5 |       20 |
|      4 | Test 4   | 01 February 2019 | 28 February 2019 00:00:00 | Politics |   100 |                 3 |       30 |
|      5 | Test 5   | 01 January 2019  | 31 January 2019 00:00:00  | History  |    50 |                 5 |       30 |
|      6 | Test 6   | 01 February 2019 | 28 February 2019 00:00:00 | History  |   100 |                 3 |       25 |
+--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+

This query would give the ranking of the students for each test

SELECT subname
     , stdname
     , @seq := IF(testid=@prevt, @seq+1, 1) as seq
     , @rank := IF(totmarks=@prevm, @rank, @seq)+0 as rank
     , @prevt := testid as testid
     , @prevm := totmarks as marks
     FROM (
            SELECT s.stdid
                 , s.stdname
                 , sq.testid
                 , IFNULL(SUM(q.marks),0) as totmarks
            FROM student s
                INNER JOIN studentquestion sq USING (stdid)
                LEFT JOIN question q
                    ON sq.testid = q.testid
                        AND sq.qnid = q.qnid
                        AND sq.stdanswer = q.correctanswer
            GROUP BY sq.testid, s.stdid
            ORDER BY sq.testid, totmarks DESC
        ) tots
        INNER JOIN test t USING (testid)
        INNER JOIN subject sub USING (subid)
        JOIN (SELECT @seq:=0, @prevm:=0, @prevt:=0, @rank:=0) init;
        
+----------+-----------+------+------+--------+-------+
| subname  | stdname   | seq  | rank | testid | marks |
+----------+-----------+------+------+--------+-------+
| Science  | Student 5 |    1 |    1 | 1      |    40 |
| Science  | Student 2 |    2 |    2 | 1      |    25 |
| Science  | Student 3 |    3 |    3 | 1      |    15 |
| Science  | Student 4 |    4 |    3 | 1      |    15 |
| Science  | Student 1 |    5 |    5 | 1      |     5 |
| Science  | Student 3 |    1 |    1 | 2      |    60 |
| Science  | Student 1 |    2 |    2 | 2      |     0 |
| Science  | Student 2 |    3 |    2 | 2      |     0 |
| Politics | Student 3 |    1 |    1 | 3      |    20 |
| Politics | Student 1 |    2 |    1 | 3      |    20 |
| Politics | Student 5 |    3 |    3 | 3      |     0 |
| Politics | Student 2 |    4 |    3 | 3      |     0 |
| Politics | Student 4 |    5 |    3 | 3      |     0 |
| Politics | Student 1 |    1 |    1 | 4      |    30 |
| Politics | Student 3 |    2 |    2 | 4      |    25 |
| Politics | Student 2 |    3 |    3 | 4      |    15 |
| History  | Student 2 |    1 |    1 | 5      |    30 |
| History  | Student 4 |    2 |    2 | 5      |    25 |
| History  | Student 1 |    3 |    3 | 5      |    15 |
| History  | Student 3 |    4 |    4 | 5      |     5 |
| History  | Student 5 |    5 |    5 | 5      |     0 |
| History  | Student 3 |    1 |    1 | 6      |    25 |
| History  | Student 1 |    2 |    2 | 6      |     0 |
| History  | Student 2 |    3 |    2 | 6      |     0 |
+----------+-----------+------+------+--------+-------+

 

Iimage.png.8e3a39e5022498be646aa2941054d11f.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.