SitePoint Sponsor

User Tag List

Results 1 to 25 of 25

Threaded View

  1. #1
    SitePoint Member
    Join Date
    May 2009
    0 Post(s)
    0 Thread(s)

    How do I instantiate objects based on a query across many database tables

    Hi everyone

    I'm experimenting with OO by reworking an old procedural coded system. The system is based on users (participants) making preferences for sites they would like to be placed for study.

    I have no problem setting up DAO's for accessing single tables/records, but I am struggling with how to setup code to deal with database queries across multiple tables and records.

    I have described the scenario below if you want to refer to it, but basically I need to query multiple records across multiple tables, but don't know how to go about setting up the myriad of objects to represent this.

    Thanks in advance!

    I have a screen for an administrator to view a participant's preferences. The preferences displayed are also based on a 'form section' filter.

    Eg a preference form could have 2 sections: Section A and Section B ... if Section A is selected, only display the preferences made for this section

    I have attached an image below of 5 tables required to get this information.

    site: a building or facility
    unit: a department within a site where a participant gets placed.
    pref_form_section: stores the possible sections that can make up the preference form
    preference_option: stores the preference options (related by unit_id) and the section they appear under
    preference: stores the preferences that a participant has made.

    To be able to list all of the selected participant's preferences, the query would be:

    SELECT *
    FROM preference
    INNER JOIN preference_option ON preference.preference_option_id = preference_option.preference_option_id
    INNER JOIN unit ON preference_option.unit_id = unit.unit_id
    INNER JOIN site ON unit.site_id = site.site_id

    WHERE preference.participant = #form.participant_id#
    AND preference_option.pref_form_section_id = #form.pref_form_section_id#

    ORDER BY preference.preference_number
    Attached Images Attached Images


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts