Fetch Consecutive Data from MYSQL

lostron

This is pretty simple but having a bit of trouble.

I have an exams table and in it are 2 fields, exam_number, and exam_score. The exams are in consecutive order from 1 to 10. However, people can take any exam they want at any given time.

I want to make a dynamic table that lists in one column the exams (1 to 10, and not just the ones that are in the database and are finished) and the scores for those (if it is empty then there is no score).

My database is like this
exam_number exam_score
2 90
5 60

and in php I want to echo this
exam_number exam_score
1 0
2 90
3 0
4 0
5 60
6 0
7 0
8 0
etc.

Thank you!! 🙂

laserlight

One solution would be along these lines:

$exams = array();
while ($row = $stmt->fetch()) {
    $exams[$row['exam_number']] = $row['exam_score'];
}

for ($i = 1; $i <= $n; ++$i) {
    echo $i . ' ' . (isset($exams[$i]) ? $exams[$i] : 0);
}

So the idea is that you populate the result array using the exam number retrieved as the key and the exam score as the value. Then you print the exam numbers from 1 to $n. On each iteration, if there is a corresponding element in the exam array, you print the exam score, otherwise you print 0.

That said, note that this means that there is nothing to differentiate a candidate who scores 0 for an exam and one who has not taken the exam.