LEFTJOIN with non CMSB Table?
4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 7, 2016 (RSS)
By Perchpole - March 4, 2016
Hello, All
I am trying to find a way to pull data from a non-CMSB table and use it in conjunction with the cms_accounts table. I could (in theory) LEFTJOIN both tables using the usernum value - which both tables share. I know this can be done in a getRecords call with 2 cms_ tables - but I don't know if it will work where one is and one isn't.
For the record I am using a separate script to run a membership sign-up operation for a hobby website. The users can pay a small fee to upgrade their standard (Website Membership plugin) account to a Membership+ scheme (which gives them free postage and such). The other script records the payments and Membership+ subscription date in one table - whilst the cms_accounts table holds all of the other user data.
Ideally I'd like to feed the subsciption date straight into the cms_account table - but I'm not sure I can do it cleanly at the moment. The next best option is the LEFTJOIN route.
Can anyone offer any advice?
Thanks,
Perch
By Dave - March 7, 2016
Hi Perch,
Most of the CMSB function are going to assume the MySQL table starts with the table prefix set during install (usually cms_).
So the cleanest way to do it would be with straight MySQL code. Alternatively, you could use straight MySQL to insert the data into the CMSB tables.
Hope that helps! Let me know any questions. Thanks.
interactivetools.com
By Perchpole - March 7, 2016
Hi, Dave -
Thanks for the info. Could I just cheat and call my table cms_blah - or is there more to it than that?
:0)
Perch
By Dave - March 7, 2016
Yea, if the other program lets you have arbitrary table prefixes then you could just create the table in CMSB.
Generally once the mysql queries get more complicated I prefer to write them in plain mysql. The LEFTJOIN option in getrecords was experimental and undocumented and I found it doesn't do a great job of making the (potentially complicated) task of joining tables all that much simpler than plain mysql code. So when I write code myself I usually don't do it that way. That said, I'd say experiment with a few ways and see what you can get working.
interactivetools.com