How do I improve query in php and MySQL for fast display

Question

I have two tables below: The table candidates store candidates information and the table candidate_subjects store results. I want to query from both tables and display the result on a web page

CREATE TABLE candidate_subjects (
  id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  candidate_id INT(11),
  exam_type_id INT(10),
  subject_id INT(10),
  ca_score INT(11),
  exam_score INT(6),
  score_grade VARCHAR(10),
  date_created VARCHAR(10),
  date_modified TIMESTAMP
);

INSERT INTO `candidate_subjects` (`id`, `candidate_id`, `exam_type_id`, 
`subject_id`, `ca_score`, `exam_score`, `score_grade`, `date_created`, 
`date_modified`) VALUES
  (1, 2, 1, 32, 22, 61, A, '2017-02-01', '2017-08-28 13:10:33'),
  (2, 2, 1, 5, 21, 38, B, '2017-02-01', '2017-08-28 13:10:33'),
  (3, 2, 1, 14, 21, 51, A, '2017-02-01', '2017-08-28 13:10:33'),
  (4, 2, 1, 1, 19, 34, B, '2017-02-01', '2017-08-28 13:10:33'),
  (5, 2, 1, 2, 23, 39, B, '2017-02-01', '2017-08-28 13:10:33'),
  (6, 2, 1, 38, 20, 32, B, '2017-02-01', '2017-08-28 13:10:33'),
  (7, 2, 1, 53, 24, 47, A, '2017-02-01', '2017-08-28 13:10:33'),
  (8, 4, 1, 32, 19, 61, A, '2017-02-01', '2017-08-28 13:11:27'),
  (9, 4, 1, 5, 22, 41, B, '2017-02-01', '2017-08-28 13:11:27'),
  (10, 4, 1, 14, 20, 46, A, '2017-02-01', '2017-08-28 13:11:27'),
  (11, 4, 1, 1, 23, 37, B, '2017-02-01', '2017-08-28 13:11:27'),
  (12, 4, 1, 2, 21, 36, B, '2017-02-01', '2017-08-28 13:11:27'),
  (13, 4, 1, 38, 22, 34, B, '2017-02-01', '2017-08-28 13:11:27'),
  (14, 4, 1, 53, 24, 52, A, '2017-02-01', '2017-08-28 13:11:27'),
  (15, 5, 1, 32, 20, 62, A, '2017-02-01', '2017-08-28 13:11:44'),
  (16, 5, 1, 5, 22, 38, B, '2017-02-01', '2017-08-28 13:11:44');


CREATE TABLE candidates (
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  exam_no VARCHAR(15),
  surname VARCHAR(50),
  other_names VARCHAR(100),
  school_id INT(11),
  registration_completed INT(11),
  exam_scores_completed INT(5),
  remark VARCHAR(10)
);

INSERT INTO candidates (id, exam_no, surname, other_names, school_id,
registration_completed, exam_scores_completed, remark) VALUES
 (1, '1171052001', 'ABADO', 'MASENENGEN', 1052, 1, '1', 'RESIT'),
 (2, '1170938001', 'AGBA', 'NGUHER', 938, 1, '1', 'FAIL'), 
 (3, '1170071001', 'ABEE', 'SESUUR', 71, 1, '1', 'PASS'),
 (4, '1170938002', 'AHEN', 'REBECCA DOOSUUN', 938, 1, '1', 'PASS');

I have a function that gets the candidate's grade for a subject in the candidate_subjects table.

    function getgradebycandsub($candidate_id, $sub){

        $s = "SELECT score_grade FROM `candidate_subjects` WHERE candidate_subjects.candidate_id='$candidate_id' AND candidate_subjects.subject_id='$sub'";

            $r = mysql_query($s);
            $c = mysql_fetch_array($r);
            return $c['score_grade'];
     }

I have the following loop:

      $s = "SELECT id, exam_no, surname, other_names, remark FROM candidates ORDER BY surname";
    $r = mysql_query($s);

echo "<tr><td align='center'>S/N</td><td align='center'>EXAM NO.</td><td align='center'>NAMES OF CANDIDATES</td><td align='center'>ENG</td><td align='center'>MATH</td><td align='center'>B.STD</td><td align='center'>C.ART</td><td align='center'>FREN</td><td align='center'>HAU</td><td align='center'>BST</td><td align='center'>ARAB</td><td align='center'>P.VOC</td><td align='center'>R.N.V</td><td align='center'>REMARK</td></tr>";
 while ($candidates = mysql_fetch_array($r)) {

        echo "<tr>";
        echo "<td height='30'>" . ++$sno . "</td>";
        echo "<td>" . $candidates['exam_no'] . "</td>";
        echo "<td>" . $candidates['surname'] . "&nbsp;&nbsp; " . $candidates['other_names'] . "</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '1')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '2')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '5')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '14')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '22')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '53')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '32')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '33')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '38')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '27')."</td>";
        echo "<td align='center'>".$candidates['remark']."</td>";
        echo "</tr>";
    }

My challenge is it takes 5 hours to display 4000 records. UPDATE
The expected output is S/No, Exam No, Name, ENG, MATH, B.STD, C.ART ,FREN, HAU, BST ,ARAB ,P.VOC ,R.N.V ,REMARK. That is the candidate details such as Exam no, Name, grade in the subjects and remark

Answer 1

Use JOIN instead of function call will improve the performance. Because, in each function call you are again executing the query, which goes to database and fetch the result, which is causing the performance.

SELECT score_grade, id, exam_no, surname, other_names, remark 
FROM candidate_subjects cs
INNER JOIN  candidates  ON 
cs.candidate_id=c.id ORDER BY id, subject_id;

It will return the result order by Student Id and Subject.