External database (pulling data from there)

7 posts by 3 authors in: Forums > CMS Builder
Last Post: April 27, 2016   (RSS)

By Jesus - April 20, 2016

Hi everybody,

I've an external database where I've a few fields that I want to use while creating or editing a record. Basically I want to capture some promotions to certain clients/zones or cities. Then I'll display those promotions based on what I captured (but that's something else).

At this point I want to be sure I can do this using select boxes, one per each field (clients, zones and cities).

My field will be a Pulldown, Get options from MySQL (Advanced)

I saw an old post (back from 2008, link:http://interactivetools.com/forum/forum-topics-create.php?forumNum=791&postNum=2190548)

Where it seems its possible, can someone point me to the right direction here?

Thanks in advance,

Jesus

By Damon - April 21, 2016

Hi Jesus,

I don't think this can be done in the List Options: Get options from MySQL (advanced) text box as you are just entering the MySQL query for the CMS Builder database.

You may be able to enter PHP (with the mySQL connect details, etc) in the List Options: Use options listed below text box but this isn't what it was intended for so would at the very least require some trial and error to test it. Also you would need to make sure the output was when record per line.

Cheers,
Damon Edis - interactivetools.com

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

By Jesus - April 21, 2016

Hi Damon, thanks for your answer.

Do you have something like this previously done?

Any reference you you have might help me a bit to see if this could be something I can work with.

Thanks!

By Damon - April 21, 2016

Hi,

This isn't something I have done before but this post has some example code to connect to your server:
http://interactivetools.com/forum/forum-posts.php?postNum=2190548#post2190548

You would need to modify the mysql_query to get the values you need.

Set this up as just a php file outside of CMS Builder to first see if you can return the results you are after, one per line.

Then you can test adding that into the list options. Never done this before so not sure how it will work out but worth trying to see.

Cheers,
Damon Edis - interactivetools.com

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

By Jesus - April 26, 2016

Thanks Damon,

Just wondering... Is there an API or are you planning to have an API so we can comunicate from other systems into CMSB?

Thanks!

By Dave - April 27, 2016

Hi Jesus, 

You could think of MySQL as the API.  The first step could be to come up with some custom stand-alone PHP code to load the values from the remote database.  Once you had that you could: 

  • Run your new script on a cron and have it update a CMSB database table once a minute/hour/day, and then load the values from there with CMSB
  • Figure out how to run your custom PHP code from inside the "list fields options" text box in the field editor.  We added a new feature for this in 3.03: Editors: List field options specified as text can now contain PHP code

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com