Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Display field from related table
Display field from related table [message #298] Tue, 03 October 2006 21:35 Go to next message
Acharn is currently offline  Acharn
Messages: 6
Registered: August 2006
Location: Thailand
Junior Member
I've been using the example of the Ruby on Rails Cookbook to try to get a handle on using your framework, and I've hit on a snag. The database contains two tables: categories and recipes. Categories has two fields: id and name. Recipes has 6 fields: id, title, description, date, instructions, and category_id. Category_id is a foreign key to the categories table.

I've managed to get the basic menu and process scripts working, but in the screens which display data from the recipes table I want to show the name of the category, not its index number. I figured I could examine your solutions and chose the Classrooms prototype. The screen listing Lessons looked good, because you display the name of the teacher, rather than the teacher_id. But when I examine lesson_list.php and the associated crs_lesson.list.screen.inc I don't find any clue. The way the SQL is constructed is helpful on its own, but I expected some reference to the crs_teacher table to retrieve the name field.

The teacher's name displayed in the lesson_list screen is a nicely formatted string made from the three fields crs_teacher.title, crs_teacher.first_name, and crs_teacher.last_name. How did you do it?


Roger
Re: Display field from related table [message #299 is a reply to message #298] Wed, 04 October 2006 04:44 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2309
Registered: April 2006
Location: Surrey, UK
Senior Member
To answer your question about the formatted teacher name which appears in the lesson_list screen, first go to the Data Dictionary and take a look at the definition of the 'teacher_id' column in the 'crs_lesson' table. You will see that the control type is 'dropdown list' and the option list is 'teacher_id'. In the '_cm_getExtaData()' method in the 'crs_lesson' class you will see where it calls the 'getValRep()' method on an instance of the 'crs_teacher' class to obtain the contents of an array of data which it loads into $this->lookup_data['teacher_id']. If you examine the '_cm_getValRep()' method in the 'crs_teacher' class you will see how this array is constructed. When a dropdown list control is read-only, such as in a LIST screen, the entire dropdown list is not displayed, but the selected entry from the list is. That is how 'teacher_id' is automatically shown as the teacher's name.

To achieve the same sort of effect without using a dropdown list control is easy. Basically it requires a JOIN, which can be performed in one of two ways as outlined in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq18a and http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq18b.

To get the framework to perform the JOIN automatically you must go to the Data Dictionary and define the relationship between the two tables, as documented in http://www.tonymarston.net/php-mysql/menuguide/dict_relation ship(upd).html. This is where you say 'tableA is related to tableB, and the primary key field(s) of tableA are linked to so-and-so foreign key field(s) of tableB'. Notice also on this screen there is a column called 'parent field'. This is where you tell the framework to include that field from the parent table when you retrieve occurrences from the child table. You can either pick a single field from the dropdown list, or you can select the keyword 'CALCULATED' and fill in the details for 'calculated field'. Don't forget to export any changes you make using http://www.tonymarston.net/php-mysql/menuguide/dict_table(ex port).html so that the table structure file is updated.

If you do not set up the relationship details in the Data Dictionary so that the framework can perform the JOIN automatically, then you will have to do it manually, as outlined in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq08.

I hope this answers your question.


Re: Display field from related table [message #300 is a reply to message #299] Wed, 04 October 2006 22:11 Go to previous message
Acharn is currently offline  Acharn
Messages: 6
Registered: August 2006
Location: Thailand
Junior Member
Thanks for the quick response. Lots of meat for me to chew on, here. My first impression is that writing the JOIN would be the more convenient approach, but my goal is an application similar to but even more complex than your Classroom prototype so having the alternative approaches is good. Thanks again.

Roger
Previous Topic: Creating filters
Next Topic: NULL string
Goto Forum:
  


Current Time: Wed Sep 28 12:35:25 EDT 2022

Total time taken to generate the page: 0.01588 seconds