Problem with ON clause in MySQL 5

[Debbie-Leigh]

Hi,

 

My hoster has recently updated their servers to the all new version 5s of PHP and MySQL, which has caused a bit of a problem with some of my queries.  It seems to be an obscure one and I can't seem to find anything in the manuals about it.

 

The query that I'm using is:

     SELECT t1.my_col1
          , t2.my_col2
          , t3.my_col3
          , t4.my_col4
       FROM offer      t1
          , price_type t2
  LEFT JOIN processor  t3
         ON t3.processor_id  = ''
  LEFT JOIN currency   t4
         ON t4.currency_id   = t1.currency_id
      WHERE t1.offer_id      = 1
        AND t1.price_type_id = t2.price_type_id

The problem is with the ON of the second LEFT JOIN. If I run it as it is i.e. comparing the two columns, it says 'Unknown column t1.currency_id', but the currency_id column definitely exists on the offer table. However, when I change that line to t4.currency_id = 1 or t1.currency_id = 1, the query works fine. The query also works fine in v4.

 

Obviously, I can't run it using t1.currency_id = 1, so could anyone tell me whether there was a change in the way ON clauses work between v4 and v5 of MySQL, as I can't find any indiction in the manuals about this?

 

Debbie

 

[fenway]

There wasn't a change in the ON clause, but rather a chance in the precedence of the comma operator.  Simply wrap parens around the tables "around" the comma, and you'll mimic the old behavior.

[DGO]

Hi Folks,

 

I also seem to be suffering from a MySQL 5 update on clause related problem, which I think is related to this thread.

 

-- edit -- solved...

 

I've posted my fix below.

[DGO]

I figured it out! For anyone else having the same problem, here was my fix:

 

This is the actual code:

 

$query = 'SELECT A.CatName,A.CatID,B.BoardID,B.bName,B.bDesc,D.PosterName,D.PosterID,D.DatePosted,C.TopicID,D.Subject,C.LastMsgID

FROM '.PREFIX.'categories A,'.PREFIX.'boards B,'.PREFIX.'bpermissions E

LEFT OUTER JOIN '.PREFIX.'topics C ON (B.BoardID=C.BoardID AND C.LastMsgID IN ('.$LastMsgIDList.'))

LEFT OUTER JOIN '.PREFIX.'messages D ON D.MsgID=C.LastMsgID

WHERE A.CatID=B.CatID

AND B.BoardID=E.BoardID

AND E.GroupID='.$CurrentUser['GroupID'].'

AND (BPermMask&'.SetBit(0,$BPermDefs,'view').')>0

'.(($_GET['CatID']) ? 'AND B.CatID='.$_GET['CatID'] : '').'

ORDER BY A.cOrder, B.bOrder';

 

I tried altering in the following way:

 

FROM '.PREFIX.'categories A,'.PREFIX.'boards B,'.PREFIX.'bpermissions E

 

changed to:

 

FROM ('.PREFIX.'categories A,'.PREFIX.'boards B,'.PREFIX.'bpermissions E)