Best way to access complicated join data in php?

David@Quantum

Apologies for the cryptic title, couldn't think of a way to summarise what I'm trying to do.

I have three tables in my database that I want to access here:
* Organisations
* Sites (context: as in physical location, not as in website - has foreign key to Organisations: site_org)
* Users (has foreign key to Organisations: user_org)

I want to get all users with the same organisation key as the site I'm accessing, but I'm only supplying the primary key of the site to the PHP script.
My approach would be to run a separate database query prior to instantiating the dt-editor object and accessing and use the org ID in the inst block, but I'm assuming there's a better way I haven't thought of?..

Thanks in advance,
Dan@Quantum

tangerine

Not sure I fully understand your data - could you spell out your table definitions?

David@Quantum

@tangerine :

Organisations:
* org_id (Primary key)
* org_name

Sites:
* site_id (Primary key)
* site_name
* site_org (foreign key links to org_id in organisations)
* site_contact_user (CSV list of foreign keys for users)

Users:
* user_id (Primary key)
* user_name
* user_org (foreign key to org_id in organisations)

The exact system I'm developing is a table that lists all users assigned to an organisation and has a rendered checkbox - that is checked if that rows' user's ID is in the specified (by $_SESSION['focused_site_id'] - which is the site_id key) sites' list of contact users.

I'm hoping to achieve this without having to run a separate SQL query to find the sites organisation before collecting the site and org users.

David@Quantum

Apparently I'm really feeling those Blue Monday blues...

->leftjoin('users', 'users.user_org', "=", 'sites.site_org')
->where('sites.site_id', $_SESSION['focused_site_id'])

Obviously that'd do it

Cheers,
Dan@Quantum