php - How to get sql WHERE statement auto update from localhost link

Question

I have my exam webpage develop in php code.

I wish to know that how to auto retrieve the WHERE value from the link.

For example my webpage link will be :

http://localhost/Exam/OES/OES/oes/admin/rsltmng.php?testid=4

When I click on another result for others test paper, the link will change to http://localhost/Exam/OES/OES/oes/admin/rsltmng.php?testid=3

I wish to know that how can I auto pass the testid value into my sql code.

Currently my sql code will look like this: //code formatting for your question.

 `$result3=executeQuery("SELECT x.stdid, x.testid, x.questionsInTest, x.questionsCorrect, ROUND( 100.0 * x.questionsCorrect / x.questionsInTest ) AS percentScore FROM ( SELECT s.stdid, q.testid, COUNT(q.qnid) AS questionsInTest, SUM( IF(s.answered='answered' AND s.stdanswer=q.correctanswer, 1, 0 ) ) AS questionsCorrect FROM question AS q INNER JOIN studentquestion AS s ON q.testid = s.testid AND q.qnid = s.qnid WHERE q.testid=".$_REQUEST['testid']." GROUP BY s.stdid, q.testid ) AS X ORDER BY x.stdid, x.testid;"); ` 

When I put in this code, there is no result being shown on my page.

==== As below will be the table that is involve.

question table:

CREATE TABLE IF NOT EXISTS `question` (
`testid` bigint(20) NOT NULL DEFAULT '0',
`qnid` int(11) NOT NULL DEFAULT '0',
`question` varchar(500) DEFAULT NULL,
`optiona` varchar(100) DEFAULT NULL,
`optionb` varchar(100) DEFAULT NULL,
`optionc` varchar(100) DEFAULT NULL,
`optiond` varchar(100) DEFAULT NULL,
 `correctanswer` enum('optiona','optionb','optionc','optiond') DEFAULT NULL,
`marks` int(11) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 --
 -- Dumping data for table `question`
  --

    INSERT INTO `question` (`testid`, `qnid`, `question`, `optiona`,   `optionb`, `optionc`, `optiond`, `correctanswer`, `marks`) VALUES
  (1, 1, 'question for test 1', 'ans1', 'ans 2', 'ans 3', 'ans 4', 'optiona', 1),
  (2, 1, 'question test', 'ans1', 'ans2', 'ans3', 'ans4', 'optiona', 1);

studentquestion table:

CREATE TABLE IF NOT EXISTS `studentquestion` (
`stdid` bigint(20) NOT NULL DEFAULT '0',
`testid` bigint(20) NOT NULL DEFAULT '0',
`qnid` int(11) NOT NULL DEFAULT '0',
`answered` enum('answered','unanswered','review') DEFAULT NULL,
`stdanswer` enum('optiona','optionb','optionc','optiond') DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `studentquestion`
--

INSERT INTO `studentquestion` (`stdid`, `testid`, `qnid`, `answered`,  `stdanswer`) VALUES
(1, 1, 1, 'answered', 'optiona'),
(1, 2, 1, 'answered', 'optiona');

Answer 1

Use this statement to get all student scores of your test.

SELECT sq.stdid, sq.testid, COUNT(*) AS correctAnswers, 
SUM(q.marks) AS studentScore, (SELECT SUM(marks) FROM question 
WHERE testid=$_REQUEST['testid']) AS totalScore 
FROM question q, studentquestion sq 
WHERE sq.testid=$_REQUEST['testid'] AND q.testid = sq.testid 
AND q.qnid = sq.qnid AND sq.answered = 'answered' 
AND q.correctanswer = sq.stdanswer GROUP BY sq.stdid;

You can create a PHP variable to calculate the percentage. For example:

$scorePercentage = $r3['studentScore'] / $r3['totalScore'] * 100;

or

$scorePercentage = $r3['studentScore'] .'/'. $r3['totalScore'];

// Output: echo $scorePercentage;
60
60/100

---

Use this SQL statement to get scorePercentage now:

SELECT sq.stdid, sq.testid, COUNT(*) AS correctAnswers, 
(SUM(q.marks) / (SELECT SUM(marks) FROM question WHERE testid=1) * 100) 
AS studentScorePercentage FROM question q, studentquestion sq 
WHERE sq.testid=$_REQUEST['testid'] AND q.testid = sq.testid AND q.qnid = sq.qnid 
AND sq.answered = 'answered' AND q.correctanswer = sq.stdanswer 
GROUP BY sq.stdid;

SQLFiddle with above results here: http://sqlfiddle.com/#!9/8d47c/10