Replacing an output from an SQL statement with a different string

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.

2 answers

  • answered 2018-11-10 12:33 ADyson

    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

  • answered 2018-11-10 16:14 Toby Dixon Smith

    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