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 ross - October 22, 2013

Hi Jeremy

Thanks! I've started looking at the files and it would be great if  you could set up the last section just so I have everything to work with.  Next, I've realized, I don't quite understand what kind of custom sort you'd like to have. Could you give me more detail on the custom sort you are looking for?

Perhaps you could give me some example data for each of the sections (just typed out in a forum post, not actual data files) and then list the data in the custom sort order you want. Does that make sense?

Let me know what you think. 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 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