Old Mysql? Syntax issue - Need advise Error "Unknown column in on clause"

[Looktrne]

SELECT profile.id as profile_id, photos.filename_1 as filename_1, photos.filename_2 as filename_2, photos.filename_3 as filename_3, photos.filename_4 as filename_4, photos.filename_5 as filename_5 FROM dt_billing_history as billing_history, dt_members as member, dt_profile as profile, dt_photos as photos LEFT JOIN dt_privacy as privacy ON privacy.member_id = member.id WHERE billing_history.gender = 'Female' AND member.id = billing_history.member_id AND (privacy.spotlight_yn <> 'Y' OR privacy.spotlight_yn IS NULL) AND photos.member_id = member.id AND profile.id = member.profile_id AND profile.status = 1 ORDER BY billing_history.id DESC LIMIT 4;

 

Can someone please help me find the Syntax error in the above mysql Querry?

 

Unknown column 'member.id' in 'on clause'

Edited April 29, 2014 by 2paulm

[fastsol]

Most likely it's cause of the second member_id in the string here

 

privacy.member_id = member.id

privacy.member_id = member.id

Maybe it's supposed to be

privacy.member_id = photos.member.id

It can't determine what table the member_id is supposed to compare

[Looktrne]

The Querry works on a different server so it must be a depreciated something going on in there

[Looktrne]

Do you think it could be a mysql version issue? or maybe I missed something importing the database? I think it's an "old syntax"

 

I know it worked fine on the other server but now we have upgraded everything.

[Jacques1]

Hi,

 

yes, there has been a change of how MySQL processes joins:

 

 

https://dev.mysql.com/doc/refman/5.6/en/join.html

 

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

 

 

But the real problem is that you've mixed those syntaxes in the first place. Either use the old syntax with commas or the new syntax with “LEFT JOIN”, “INNER JOIN” etc. When you put the two together, you're asking for trouble.

Edited April 29, 2014 by Jacques1

[Psycho]

Tips:

 

1. Don't create your queries as a single line of text. Add some line breaks and spacing to make it readable. It will save you many, many hours in debugging. 2. No need to create aliases for all of your fields if you want them returned with the same name as the field? Just include the field in your select list and the server will return it using the field name (assuming you are using a fetch_assoc() function).

3. I see you have fields for filename_1, filename_2, etc. You should probably have those in a separate table with a foreign key back to the parent record. Using separate fields like that makes it difficult to change the number you allow and to do any type of queries based upon those values.

 

 

Don't know if it would fix your issue, but I revised your query to use JOINs for all the tables instead of doing it through the WHERE clause.

 

$query = "SELECT profile.id as profile_id, photos.filename_1, photos.filename_2,
                 photos.filename_3, photos.filename_4, photos.filename_5
 
          FROM dt_billing_history as billing_history
          JOIN dt_members as member
                 ON member.id = billing_history.member_id
          JOIN dt_photos as photos
                 ON photos.member_id = member.id
          JOIN dt_profile as profile
                 ON profile.id = member.id
          LEFT JOIN dt_privacy as privacy
                 ON privacy.member_id = member.id
 
          WHERE billing_history.gender = 'Female'
            AND (privacy.spotlight_yn <> 'Y' OR privacy.spotlight_yn IS NULL)
            AND profile.status = 1
 
          ORDER BY billing_history.id DESC
 
          LIMIT 4";

 

That's so much easier to read and greatly improves the ability to debug.