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 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 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,

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/