Showing records from 2 tables

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!

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.

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