Showing records from 2 tables

4 posts by 3 authors in: Forums > CMS Builder
Last Post: July 2, 2013   (RSS)

By jjem - June 21, 2013

Hello,

This doesn't concern the front end but only cmsAdmin.

I have 3 tables:

"works", "creations", "performers".

in the "creations" table, I have a list fields where I can select "performers" (by first_name and name).

in the "works" table, I would like to be able to select from a list that comes from the "creations" table.

it works but I'd like to show the "performers"  first_name, and  name. For now it only shows  "num" field, which doesn't help.

Thanks for your help

By Jason - June 24, 2013

Hi,

To help us clarify exactly what you are looking for, are  you trying to associate a work with a creation, just using performer names as a label, or are you trying to associate a work with a perform that has a creation?

Let me know and we'll see about designing a solution for you.

Thanks!

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

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

By gregThomas - July 2, 2013

Hi jjem,

I think I have a solution to your problem. You need to change your list field in the works table to use  'Get options from MySQL query (advanced)' type. Then you'll need to create a MySQL statement that will retrieve values from the creations and performers table and combine them into one value. It should look something like this:

SELECT c.num, CONCAT(c.title, ' ', (SELECT p.first_name FROM <?php echo $TABLE_PREFIX ?>performers p WHERE p.num = c.performer))
  FROM `<?php echo $TABLE_PREFIX ?>creations` c

This is just example code, so you will have to make a few changes to get it working.

So this statement selects num values from the creations table as well as the associated title combined with a second select statement from the performers table. The MySQL CONCAT function is used to combine multiple strings into one string. The MySQL select statement for the performers table will return a string with the performers first name, using the num value stored in the performer field in the creations table.

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com