Showing records from several tables on a detail page

7 posts by 3 authors in: Forums > CMS Builder
Last Post: November 12, 2013   (RSS)

By jjem - October 17, 2013

Hi,

I have 4 tables:

1) Works

2) Interpretes

3) Premieres

4) Discography

here is what I am trying to do:

1) Enter records into the "interpretes" table (name, first_name, function fields)

2) In the  "Premieres" table, I need to make a list field (pull down multivalue) with an advanced MYSQL query that lists the fields from "interpretes" in the following way:

name first_mane (function). In the viewer code I'd need to be able to custom sort them.

3) in the "Works" table, I need to be able to pull the info from the "premieres" table, including the name first_mane (function) fields from the "interpretes" table as well as the custom sorting

Is this possible? I have no idea where to start to solve the custom sorting in point 2) and the point 3). If needed I'd hire you.

Thanks so much for your help,

Jeremy

By ross - October 17, 2013

Hi Jeremy

Thanks for the post! 

I don't quite understand what you are looking for on this so I was wondering if you could post up the schema files for each of the 4 sections. Maybe once I can see the fields and things we are working with, it will get me jump started on the right track.

You can find your schema files in /cmsAdmin/data/schema/

Thanks!

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

By jjem - October 18, 2013

Hi Ross,

Here are the files, there is one table I did not create yet, it's the Discography. But I guess the process will be the same as for the others.

Thank you!

Jeremy

Attachments:

creations-ini.php 5K

interpretes-ini.php 3K

oeuvres-ini.php 10K

By jjem - October 23, 2013

Hi Ross,

I'm out of office these days... I'll post the needed information tomorrow.

Thanks,

Jeremy

By jjem - November 6, 2013

Hi Ross,

Here is the missing file (discographie).

About the sorting option I'll try to make it as clear as I can...

I need to be able to sort the records called from another table. For example in the Creations, when I select multiple records in the interpretes field, I need to be able to give a sort order which can be different from what the pulldown shows or from the sorting setting.

Example: the pulldown list shows:

  1. John Doe, piano
  2. Phiharmonia Orchestra
  3. Charles Dutoit, conductor

I'd like it to appear fronted like:

  1. Philharmonia Orchestra
  2. Charles Dutoit, conductor
  3. John Doe, piano

I hope this makes it clearer.

Thanks again,

Jeremy

Attachments:

discographie-ini.php 5K

By Dave - November 12, 2013

Hi jjem,

I'm just jumping in on this one.  Here's some tips: 

By default most sections inherit their sort order from whatever is set for the section under:
Admin > Section Editors > Your Section Name > Sorting

For pulldown lists, if you wanted to change the default display values or sort order you can do that in the field editor for pulldowns by selecting "Get options from MySQL query (advanced)" and then writing a custom MySQL query to output the values you want sorted in the order needed.

For viewer pages created with the code generator, you can specify a custom sort order by adding this option to the getRecords() options:
'orderBy' => 'name, city', // use a MySQL ORDER BY value here

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com