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?

Jeff Shields

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,

Daryl Maximo
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

Jeff Shields

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,

Daryl Maximo
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!

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/