SQL warning when accessing admin page for affiliate
| Project: | Affiliate |
| Version: | HEAD |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
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>
#1
The affiliate module creates several tables at install time. Can you determine whether the affiliate_details table exists?
#2
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.
#3
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.
#4
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.
#5
What does the actual code look like to fix this and what lines do I change?
Im a SQL nooB.
#6
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.
#7
Fixed in drupal 5 version
#8