Myujth

I currently have an output that is populating a table. One of the outputs it gives is a year from the student's table. Whenever 6 is outputted as the year I need it to return L6 and when 7 is outputted I need U6. I have made an attempt bellow:

"$stmt = $conn->prepare(
 "SELECT st.Name AS student, st.House AS house, T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
 CASE
 WHEN st.Year = 6 THEN 'L6'
 WHEN st.Year = 7 THEN 'U6'
 ELSE st.Year
 END CASE as year
 From Students AS st INNER JOIN Student_Choices AS sc
 ON st.Username = sc.Username INNER JOIN Current_DB AS db
 ON sc.DB_year = db.DB
 INNER JOIN Choices AS c1
 ON sc.T1_Choice = c1.Choice_ID
 INNER JOIN Sports AS T1
 ON c1.Sport_ID = T1.Sport_ID
 INNER JOIN Choices AS c2
 ON sc.T2_Choice = c2.Choice_ID
 INNER JOIN Sports AS T2
 ON c2.Sport_ID = T2.Sport_ID
 INNER JOIN Choices AS c3
 ON sc.T3_Choice = c3.Choice_ID
 INNER JOIN Sports AS T3
 ON c3.Sport_ID = T3.Sport_ID
 ");

I then get an error:

errorSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN st.Year = 6 THEN 'L6' WHEN st.' at line 2

Any solutions would be great thanks.

Answer is as follows:

SELECT st.Name AS student, st.House AS house,
 (CASE WHEN st.Year = 6 THEN 'L6' WHEN st.Year = 7 THEN 'U6' ELSE st.Year END) as year,
 T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
 From Students AS st
 INNER JOIN Student_Choices AS sc
 ON st.Username = sc.Username INNER JOIN Current_DB AS db
 ON sc.DB_year = db.DB
 INNER JOIN Choices AS c1
 ON sc.T1_Choice = c1.Choice_ID
 INNER JOIN Sports AS T1
 ON c1.Sport_ID = T1.Sport_ID
 INNER JOIN Choices AS c2
 ON sc.T2_Choice = c2.Choice_ID
 INNER JOIN Sports AS T2
 ON c2.Sport_ID = T2.Sport_ID
 INNER JOIN Choices AS c3
 ON sc.T3_Choice = c3.Choice_ID
 INNER JOIN Sports AS T3
 ON c3.Sport_ID = T3.Sport_ID

CASE needed to be in brackets and the as year needed to be after when being selected

The case statements cannot sit on their own like that, they have to be part of the main query. Remove them and instead try

SELECT 
 st.Name AS student, 
 st.House AS house, 
 CASE 
 WHEN st.Year = 6 THEN 'L6' 
 WHEN st.Year = 7 THEN 'U6'
 ELSE st.Year 
 END CASE as year

...etc