PHP MYSQL CASE substitution in array

Question

Morning all, happy new year!

I am trying to select all records within a MYSQL database into an array where 1 column matches a list, and then replace the output of the cells when selected from that column.

It's a sports table, and I have the Positions as MF, DF, CF and want to replace them with Midfield, Defence, and Forward respectively.

I was hoping that the following would crack it, but get an error message, which lines up to the FROM line:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/peterborough/www/www/wp-content/plugins/insert-php/insert_php.php(48) : eval()’d code on line 14

   $result = mysql_query("SELECT *,
                        CASE Position 
                          WHEN 'DF' THEN 'Defence'
                          WHEN 'MF' THEN 'Midfield'
                          WHEN 'CF' THEN 'Forward'
                      FROM People 
                      WHERE
                         (Position='DF' or
                          Position='MF' or
                          Position='CF') and
                          Season = '2014'
                      ORDER BY Number");
  while($row = mysql_fetch_assoc($result)){...}

Thanks guys

Jasen · Accepted Answer · 2015-01-01T10:44:02.010

2

you have a syntax error in your SQL: the CASE expression requires an END

  SELECT *,
    CASE Position 
      WHEN 'DF' THEN 'Defence'
      WHEN 'MF' THEN 'Midfield'
      WHEN 'CF' THEN 'Forward'
    END AS position_long
  FROM People 
  WHERE
     (Position='DF' or
      Position='MF' or
      Position='CF') and
      Season = '2014'
  ORDER BY Number

Explanation: when the syntax error hits, you get a FALSE from the mysql_query() call and when that's passed into mysql_fetch_assoc() it complains about being given a boolean instead of a resource

edited Jan 01 '15 at 10:44

answered Jan 01 '15 at 10:29

Jasen

11,837
2
30
48

Ahhh that helps!! although it's now syntaxing, it's not doing the substitution? – MongoUK Jan 01 '15 at 10:33
1

not sure what you mean. it's not substituting - you're adding a new column to the results, you probably want `AS position_long` or similar after the `END` so that the new column gets a sensible name instead of the default name. – Jasen Jan 01 '15 at 10:38
Of course! sorry, forgot the basics!! Thought it would dynamically change it, rather than create a new column!! – MongoUK Jan 01 '15 at 10:41

PHP MYSQL CASE substitution in array

1 Answers1