hi,

i'm running drupal 4.7 and have taken the cvs code of affiliate module. after enabling the module i went to
the administration of affiliates (http://localhost/drupal/admin/affiliate). on top of the page there's a SQL warning

user warning: Unknown column 'ad.aid' in 'on clause' query: SELECT ad.aid, ad.enabled, u1.name, count(au.uid) as referred FROM affiliate_details as ad, users as u1 LEFT JOIN affiliate_users as au on ad.aid = au.aid WHERE ad.aid = u1.uid GROUP BY ad.aid ORDER BY referred DESC LIMIT 0, 50 in /opt/httpd-2.2.3/htdocs/drupal-4.7.3/includes/database.mysql.inc on line 120.

I typed the same query on my mysql prompt

mysql> SELECT ad.aid, ad.enabled, u1.name, count(au.uid) as referred
-> FROM affiliate_details as ad, users as u1
-> LEFT JOIN affiliate_users as au on ad.aid = au.aid
-> WHERE ad.aid = u1.uid
-> GROUP BY ad.aid ORDER BY referred DESC LIMIT 0, 50
-> ;
ERROR 1054 (42S22): Unknown column 'ad.aid' in 'on clause'
mysql>

Comments

dtj’s picture

dtj commented

The affiliate module creates several tables at install time. Can you determine whether the affiliate_details table exists?

Gurpartap Singh’s picture

Gurpartap Singh commented

Every table and field is there. Occured for me on MySql 5. Havn't tried on other versions. There seems to be something wrong with the query itself, or Mysql5 incompatibilty? Forgive me if I am wrong.

bjaspan’s picture

bjaspan commented

The query works for me with MySQL 4:

mysql> SELECT ad.aid, ad.enabled, u1.name, count(au.uid) as referred FROM affiliate_details as ad, users as u1 LEFT JOIN affiliate_users as au on ad.aid = au.aid WHERE ad.aid = u1.uid GROUP BY ad.aid ORDER BY referred DESC;

4 rows in set (0.01 sec)

Try running 'show columns from affiliate_details' and then for affiliate_users and affilate_counts.

jhuckabee’s picture

jhuckabee commented

I was able to modify the query:

SELECT ad.aid, ad.enabled, u1.name, count(au.uid) as referred
FROM affiliate_details as ad, users as u1
LEFT JOIN affiliate_users as au on ad.aid = au.aid
WHERE ad.aid = u1.uid
GROUP BY ad.aid ORDER BY referred DESC LIMIT 0, 50

to this

SELECT ad.aid, ad.enabled, u1.name, count(au.uid) as referred
FROM affiliate_details as ad
LEFT JOIN users as u1 on ad.aid = u1.uid
LEFT JOIN affiliate_users as au on ad.aid = au.aid
GROUP BY ad.aid ORDER BY referred DESC LIMIT 0, 50

and it worked for me. I'm using MySQL 5 as well, which is think is the issue.

kenyob’s picture

kenyob commented

What does the actual code look like to fix this and what lines do I change?
Im a SQL nooB.

dksdev01’s picture

dksdev01 commented

This is what I did
line 314 affiliate.module

'FROM {affiliate_details} as ad, {users} as u1 '.

'FROM {users} as u1 , {affiliate_details} as ad '.

This resolve the issues.

thierry_gd’s picture

thierry_gd commented
Status: Active » Fixed

Fixed in drupal 5 version

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)