drobson
Posts: 4
|
| Posted: 05/15/2005, 6:12 PM |
|
I am working on a PHP/MySQL application that tracks student information. The desire is to display the sex of the student by retrieving it formal name (such as M, or Male) from a secondary table. (I realize there are other ways to accomplish this, but this issue nicely illustrates one of the bumps in my learning curve.)
The main table looks (in part) like this
Table: students
Fields and typical data:
student_id 12
first_name Sally
last_name Benson
sex_id 2
The secondary table looks like this
Table: sex
Fields and typical data:
sex_id 2
sex_name Female
So, sex_id is the primary key in the secondary table and is a foreign key in the main table.
I constructed a page which displays the record of one student. If I change the students.sex-id field from Textbox to Listbox, I can configure its properties so it correctly displays the related value Female. This is the behavior I want but I want the page to display values, not offer to alter values, so I want the field to be a Label (Label name is Sex).
Im stumbling over the syntax for retrieving the related value.
[1] Can CCDlookup be used here?
Here is an example of code that is not working.
-=-=-=-=-=-=-=
//DLookup @118-55BE89E1
global $DBHealthTrackerConnect;
global $students;
$result = CCDLookUp("sex_name", "students, sex", "students.sex_id = sex.sex_id AND students.student_id =".$students->student_id->GetValue(), $DBHealthTrackerConnect);
$result = strval($result);
$students->Sex->SetValue($result);
//End DLookup
//Close students_Sex_BeforeShow @15-9EFA869E
-=-=-=-=-=-
Can anyone point me to a suitable code example?
[2] On another page, Ill display a grid with many student records and want to list Male or Female for each record in the grid. I presume this will require stepping through the records using next_record() or similar function. Can you point me to an appropriate example for this situation?
Thanks!
David
|
 |
 |
Nicole
Posts: 586
|
| Posted: 05/16/2005, 1:01 AM |
|
Hello,
If I understand you properly the solution is the same for both cases. You can join two (or more) tables to be used as Data Source for a form. So fields from both tables could be displayed on a form without additional coding.
To join tables open Data Source dialog of a form and click on Build Query button to open Visual Query Builder. There you can build a query you need. Just a note: Query Builder could be also opened from Grid Builder.
This Help article helps better understand the meaning of Data Source and Visual Query Builder http://docs.codecharge.com/studio/html/UserGuide/Forms/...Source.html?toc
_________________
Regards,
Nicole |
|
|
|
drobson
Posts: 4
|
| Posted: 05/16/2005, 7:29 PM |
|
You are absolutely correct.
I was trying to define the relational properties of a field without first defining them for the form. When I set the form properties via Query Builder, the fields fell into place easily.
Thanks Nicole!
David
|
|
|
|
|
