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 jjem - July 1, 2013
Hi,
I will try to make this clearer, sorry.
I first enter the first_name and the name of the performers in the "performers" table.
In the "Creations" table, I enter date, venue, etc. and I can then choose a performer in a list (multi check list) that comes from the "perfoermers" table.
I then create a work in the "works" table, where I can associate a creation to it (drawn from the "creations" table). In that multi check field I was able to show the date and city but the performer is shown only by its "num" and not first_name, name. See attached file.
In clear;)
I am trying to associate a creation that has one or more performers to a work.
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
PHP Programmer - interactivetools.com