nasimuddin58 Posted May 7, 2019 Share Posted May 7, 2019 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"> 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> Quote Link to comment Share on other sites More sharing options...
requinix Posted May 7, 2019 Share Posted May 7, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 7, 2019 Share Posted May 7, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 7, 2019 Share Posted May 7, 2019 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 | +----------+-----------+------+------+--------+-------+ I Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.