Answers

• colin Posts: 15,240Questions: 1Answers: 2,599

  June 2019 Answer ✓

  Hi @crush123 ,

  This thread should help, it's asking the same thing. If not, let us know.

  Cheers,

  Colin
• crush123 Posts: 417Questions: 126Answers: 18

  June 2019 edited June 2019

  that works a treat !

  However it highlights an error generated by my code, which works fine on prior versions of mySQL.

  my query generates data using a linked table to an options list like so...

  Field::inst( 'tblevent.EventType' )
          ->options( Options::inst()
          ->table( 'refeventtype' )
          ->value( 'EventTypeID' )
          ->label( 'EventTypeDescription' )
          ->order( 'ListOrder' )
          ),
  

  the sql generated by this appears to be as follows...

  SELECT DISTINCT `EventTypeID`
  as 'EventTypeID', `EventTypeDescription` as 'EventTypeDescription' FROM `refeventtype` ORDER BY `ListOrder`
  

  which works perfectly well before mySQL 5.7.x but now generates this error

      #3065 - Expression #1 of ORDER BY clause
  is not in SELECT list, references column 'inchorus_members.refeventtype.ListOrder' which is not in SELECT list; this is incompatible with DISTINCT
  

  if I remove the 'order by' clause, or add the 'refeventtype.ListOrder' to the select list, it works again

  it may be something you have seen already ?
• allan Posts: 63,516Questions: 1Answers: 10,472 Site admin

  June 2019 Answer ✓

  Are you still using 1.7.0 of our PHP libraries? If so, updating to 1.9.0 should resolve that. This is the code that should handle what you are seeing.

  Allan
• crush123 Posts: 417Questions: 126Answers: 18

  June 2019

  perfect !

  i will update the site.

  thanks for all your help