PHP SQL query to separate multiple columns (in the same row) into separate rows

Question

I have a table that lists students' test results. Each row contains a student ID, and there are columns, each column representing an individual test score. Now I need to create a query that will allow me to see a separate row for the student ID corresponding to each test score. I've searched Stack Overflow for the best approach, but I haven't found one that looks efficient. I wrote the code listed below for one student (1000002), but it seems overly complex for what I'm trying to achieve and only works for three test scores, so I need to add more code to get All 16 points. Is there an easy way to do this with a single query (preferred) or do I need to extract the rows and then use a loop to build an array from each element (second preferred)?

SELECT
   scores_uid
 , score1
FROM
 assessment_scores WHERE fk_assigned_uid = '1000002'

UNION ALL

SELECT
   scores_uid
 , score2
FROM
 assessment_scores WHERE fk_assigned_uid = '1000002'

UNION ALL

SELECT
   scores_uid
 , score3
FROM
 assessment_scores WHERE fk_assigned_uid = '1000002';

Answer 1

Ideally, you should have a structure like this:

students

• id
• name

tests

• id
• number

test_results

• id
• student_id
• test_id
• result

This way you can get all results for students in one query.

SELECT test_results.result AS Result,
 students.name as Student,
 tests.number as Testnumber 
FROM test_results 
LEFT JOIN students ON students.id = test_results.student_id
LEFT JOIN tests ON tests.id = test_results.test_id
WHERE student_id = {{id}}