cburns
Založený: 20.09.2004
Príspevky: 8
Zaslal: 2004-09-20 20:34
Návrat hore
Odpovedať s citátom
|
Please bear with this long post; I will try to be as concise as possible, but I want to give you as much detail as possible to understand the problem.
I have four DB tables. Here are the relevant fields:
SECTIONS:
- id (int)[1]
- instructor (int)[2]
- course (int)[3]
- section_name (txt)
INSTRUCTORS:
- id (int)[2]
- instructor_name (txt)
COURSES:
- id (int)[3]
- course_name (txt)
CURRENT_SECTIONS:
- section_id (int)[1]
The database is for a course catalog. General course info is in the COURSES table. Instructor information is in the INSTRUCTORS table. Sections of each course are detailed in the SECTIONS table. CURRENT_SECTIONS lists all the current offerings, so date and time info is found in this table.
I am working with displaying/editing the CURRENT_SECTIONS table. I'd like to make some joins as shown above in brackets, i.e. SECTIONS.id = CURRENT_SECTIONS.section_id; in other words, each current section has a section listing associated with it. The other bracketed relationships follow the same pattern.
Because the CURRENT_SECTIONS table only has a link to the section_id, I can only easily retrieve the section name using the 'values' configuration option. I want to display course names, and instructor names, too, otherwise users will not be able to tell which course they are adding information for.
I have made it work using 'dummy' fields to display instructor name and course name, however, these do not display when users add/edit information in the CURRENT_SECTIONS table. Here is the code for the dummy fields:
| Kód: |
$opts['fdd']['section_id'] = array(
'name' => 'Section',
'select' => 'D',
'values' => array(
'table' => 'sections',
'column' => 'id',
'description' => 'section_name'),
'required' => true,
'sort' => true
);
$opts['fdd']['name'] = array(
'name' => 'Course ID',
'sql' => 'PMEjoin9.name',
'options' => 'VLFD',
'values' => array(
'table' => 'courses',
'column' => 'id',
'description' => 'name',
'join' => '$join_table.$join_column = PMEjoin8.course')
);
$opts['fdd']['instructor'] = array(
'name' => 'Instructor',
'sql' => 'PMEjoin10.instructor_name',
'options' => 'VLFD',
'values' => array(
'table' => 'instructors',
'column' => 'id',
'description' => 'instructor_name',
'join' => '$join_table.$join_column = PMEjoin8.instructor')
);
|
I think that the route I'd like to follow is just to use the 'description' array configuration option to display a concatenation of the three pieces of information using : as the 'divs' character. I have gotten it to display information from the sections table, i.e. something along the lines of "5: 1: The Art of Landscape Design" Where 5 is the instructor and 1 is the course. I'd like to replace the 5 and the 1 with the textual values associated under the instructor_name field in the INSTRUCTOR table and the name field in the COURSES table respectively.
Here is the code for the above:
| Kód: |
$opts['fdd']['section_id'] = array(
'name' => 'Section',
'select' => 'D',
'required' => true,
'sort' => true,
'values' => array(
'table' => 'sections',
'column' => 'id',
'description' => array(
'columns' => array('instructor', 'course', 'section_name'),
'divs' => array(': ', ': ')))
);
|
I know that many posts have been written on this topic, but I couldn't find one that addressed this issue specifically.
Is there a way to replace the #'s with the textual names?
Thanks for your help!
Chris
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-09-21 09:35
Návrat hore
Odpovedať s citátom
|
as you've read, joining three tables is not easy with current pme layout. btw congratulations to your workaround for VLF modes, good job....
for AC: 'filters' option is an equivalent for 'join' in V, so it could help.
the select should be (roughly):
| Kód: |
SELECT concat(instructor.instructor_name, course.course_name, section.section_name')
FROM current_sections, instructor, course, section
WHERE current_sections.id = section.id and section.instructor = instructor.id and section.course = course.id
|
so something like this might work
| Kód: |
'values|AC' => array(
'table' => 'sections, instructor, course',
'column' => 'sections.id',
'description' => 'concat(...)',
'filters' => 'sections.instructor = ...'),
|
if this works, i'm not sure whether that's feature or bug.
|
cburns
Založený: 20.09.2004
Príspevky: 8
Zaslal: 2004-09-21 17:49
Návrat hore
Odpovedať s citátom
|
I am trying to work with your suggestion. It keeps giving me SQL query errors. Something that might be helpful would be to see the SQL statement that is being executed, rather than just a portion that is throwing the error. Is there a debug flag I can use to do so?
|
cburns
Založený: 20.09.2004
Príspevky: 8
Zaslal: 2004-09-21 18:04
Návrat hore
Odpovedať s citátom
|
Never mind, I just found it:
function myquery($qry, $line = 0, $debug = 1)
(change $debug to 1)
|
cburns
Založený: 20.09.2004
Príspevky: 8
Zaslal: 2004-09-21 21:56
Návrat hore
Odpovedať s citátom
|
I have posted my solution on this thread:
http://opensource.platon.sk/forum/projects/viewtopic.php?p=1809#1809
|
|
Odoslať novú tému
Odpovedať na tému
|
|
Prihlásiť
Registrácia
English
