Over populated where clause on leftjoin
6 posts by 3 authors in: Forums > CMS Builder
Last Post: April 28, 2016 (RSS)
By kitsguru - April 16, 2016
Where $invoiceNum = 10
list($transactionRecords, $transactionMetaData) = getRecords(array(
'tableName' => 'member_transactions',
"where" => "member_transactions.num = $invoiceNum",
'leftJoin' => array('members' => 'member_num'),
'limit' => 1,
"debugSql" => true,
));
Is generating the following SQL and error:
SELECT SQL_CALC_FOUND_ROWS `member_transactions`.*,
members.`num` as `members.num`,
members.`status` as `members.status`,
members.`membership_type` as `members.membership_type`,
members.`fullname` as `members.fullname`,
members.`email` as `members.email`,
members.`username` as `members.username`,
members.`password` as `members.password`,
members.`isAdmin` as `members.isAdmin`,
members.`isStaff` as `members.isStaff`,
members.`lastLoginDate` as `members.lastLoginDate`,
members.`expiresDate` as `members.expiresDate`,
members.`neverExpires` as `members.neverExpires`,
members.`disabled` as `members.disabled`,
members.`adult_2` as `members.adult_2`,
members.`children` as `members.children`,
members.`address` as `members.address`,
members.`city` as `members.city`,
members.`province` as `members.province`,
members.`postal_code` as `members.postal_code`,
members.`phone` as `members.phone`,
members.`newsletter` as `members.newsletter`
FROM `cms_member_transactions` as `member_transactions`
LEFT JOIN `cms_members` AS `members` ON member_transactions.`member_num` = members.num
WHERE ((member_transactions.num = 10)
AND ((`createdByUserNum` = '1')
AND (`createdDate` = '2016-03-21 12:35:04')
AND (`num` = '4') AND (`status` = '1')
AND (`updatedByUserNum` = '1')
AND (`updatedDate` = '2016-04-16 10:24:09')
AND (`members`.`num` = '1')
AND (`members`.`createdDate` = '2016-03-18 15:52:48')
AND (`members`.`createdByUserNum` = '0')
AND (`members`.`updatedDate` = '2016-03-20 14:41:36')
AND (`members`.`updatedByUserNum` = '1')
#(added all the columns from both table and their actual values to the where clause)
)
ORDER BY membership_year DESC,member_num LIMIT 1
MySQL Error: Column 'createdByUserNum' in where clause is ambiguous
I am using the membership addon (with its own account table called members). I use similar code in another area but the where clause does not add all the columns from both tables. I do not see why the where clause is being over populated.
Anyone have any ideas what is going on here?
By Daryl - April 21, 2016
Hi Jeff,
It seems like the function doesn't add the table name to "createdByUserNum".
Ie:
AND ((`members`.`createdByUserNum` = '1')
I will look into this and let you know.
Thanks,
PHP Programmer - interactivetools.com
By kitsguru - April 21, 2016 - edited: April 21, 2016
HI Daryl,
BUT none of the extra columns is needed or wanted. So the fact that the table name is missing is not really the issue.
Jeff
By Daryl - April 21, 2016
You're right, the 'createdByUserNum', and the other fields, shouldn't be included at all.
I will check this out and let you know ASAP.
Cheers,
PHP Programmer - interactivetools.com
By Daryl - April 27, 2016
Hi Jeff,
I can't replicate the issue on my local test server.
I'd like to take a closer look if you don't mind. Could you send through a second level support request?
https://www.interactivetools.com/support/email_support_form.php
Thanks,
PHP Programmer - interactivetools.com
By Damon - April 28, 2016
Hi Jeff,
We recently moved to a new server and are having some issue with email forms. If you have sent in a Support Request, could you resend the details (CMS Builder login, FTP) to us directly at support@interactivetools.com.
Thanks!
Damon Edis - interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/