Here is a simple structure of 5 related tables.
roll_no is primarykey for 'student' table and foreign key for other tables.
Code:
Student
roll_no name
1 James
2 George
3 Kevin
Subject_alloc
subject_id subject_type roll_no
101 1 1
102 1 1
103 1 1
104 2 2
105 2 2
106 2 2
107 3 3
108 3 3
109 3 3
subject_1
subject_id marks year
101 25 2009
104 35 2009
107 45 2009
subject_2
subject_id subject_name theory practical Total_marks year
102 Biology 25 25 50 2009
102 Chemistry 35 30 65 2009
102 Physics 45 45 90 2009
105 Biology 20 30 50 2009
105 Chemistry 45 40 85 2009
105 Physics 15 45 60 2009
108 Biology 25 20 45 2009
108 Chemistry 30 30 60 2009
108 Physics 25 45 70 2009
subject_3
subject_id subject_name internal marks year
103 Geography 25 30 2009
103 History 30 45 2009
106 Geography 35 20 2009
106 History 50 65 2009
109 Geography 15 60 2009
109 History 35 35 2009
Table 'subject_1' got only one entry for each student.
Table 'Subject_2' got 3 entry each for a student
Table 'subject_3' got 2 entries for each student.
Needs query/procedure/view which returns results as an xml as below or as json.
Input for query will be 'roll_no' and 'year'.
This xml/jason needs to be loaded in browser for building UI.
Code:
<Result roll_no="1" name="James">
<Subjects>
<Subject subject_id="101" subject_type="1">
<Marks>25</Marks>
</Subject>
<Subject subject_id="102" subject_type="2">
<Marks subject_name="Biology" theory="25" practical="25">50</Marks>
<Marks subject_name="Chemistry" theory="35" practical="30">65</Marks>
<Marks subject_name="Physics" theory="45" practical="45">90</Marks>
</Subject>
<Subject subject_id="103" subject_type="3">
<Marks subject_name="Geography" internal="25">30</Marks>
<Marks subject_name="History" internal="30">45</Marks>
</Subject>
</Subjects>
<Result>
Or is it possible only to build xml/json in php by looping through results.
dBforums
>
xml/json returned from query/procedure
01-17-10, 07:12
Linear Mode
