List Options from database - multiple tables

7 posts by 3 authors in: Forums > CMS Builder
Last Post: January 9, 2009   (RSS)

By s2smedia - January 8, 2009

Is there a way when creating a list field to get options from multiple tables?

the "Get options from Database" function only lets you pull from 1 table..

I wasnt sure if I could do this using "Get options from Msql Query"

Re: [s2smedia] List Options from database - multiple tables

By Dave - January 8, 2009

You could do this with "Get options from Msql Query" but it would be some complex MySQL.

Depending on what you're trying to do you'd have to use a mysql UNION (http://dev.mysql.com/doc/refman/5.0/en/union.html). This lets you combine multiple SELECT statements.

It might be simpler to re-arrange your sections and data. What result are you looking for (what are you trying to combine, etc)?
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List Options from database - multiple tables

By s2smedia - January 8, 2009

ok... well i have 3 sections

Summer Academy Locations, Futsal Academy Locations, Goalkeeper Academy Locations

they all have the same fields when creating a new location within one of these academys.

I split them up because I needed a different viewer url for each Academy.

Re: [s2smedia] List Options from database - multiple tables

By Dave - January 8, 2009

Ok, well let me know how you want to proceed. It might be easier to combine them again then to figure out the SQL union.

Have many records been entered yet?

You can combine them into "Academies" and have a list field such as "type" for (Summer, Futsal, GoalKeeper). You can also use multiple viewers even though they're from the same section. You just add "type = 'Summer'" or something like that into the where on the list page.

Let me know what works best and what you want to try first.
Dave Edis - Senior Developer
interactivetools.com

Re: [s2smedia] List Options from database - multiple tables

By ross - January 9, 2009

Hi there.

I took a look at your site and I think I see what you are aiming for. When you click to one of your academies, there is a part on each of those pages that is labeled "Session Schedule". This is where a list of locations needs to load right?

If this is the case, Dave's suggestion would work. You would create a new section called "Locations" and in that section you would have a drop down menu called "type" with each academy as an option.

From there, one each of your three pages, you would just need to add one extra line to the viewer code for each of your academy pages so that it loads the proper list of locations.

Let me know if that all still makes sense and we can go over the code in some more detail if you like
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Re: [ross] List Options from database - multiple tables

By s2smedia - January 9, 2009

Got it...thanks..

I was just trying to avoid what i set up in the CMS already.