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: [Dave] List Options from database - multiple tables

By s2smedia - January 8, 2009

OK... yea i was thinking about doing a locations tab and then making a field to chose the Academy.

But for instance if you were to click on a location in Futsal Academy, the detail page of that location would have a different header than the detail page of Summer Academy.

not sure i understand the where=Summer thing and if that will work..

goto apexfinal.highviews.co.cc

you will see 3 picture tabs at the top for each Academy...

When you click on there it takes you to that academy page.. from there you can select a location in that Academy..

just take a quick look and let me know if what you are saying will work.

I only have testing records posted as of now so it wouldnt be a big deal to combine all into a locations tab...

instead of having 3 locations tab in the CMS

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.