homepage Welcome to WebmasterWorld Guest from 54.197.94.241
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Struggling with 3 (maybe 4) level MySQL query
Need a little help forming the most efficient cross-table query
ManMountain




msg:4361291
 7:34 am on Sep 12, 2011 (gmt 0)

Hi

while I am here, I have another question that hopefully someone can help me out with. Apologies, I am fairly new with php / mysql etc.

I have a table called SITES, containing website records (site1, site2 etc).
Another table called CATEGORIES containing categories (cooking, sport etc).

There is another table SITES_CATEGORIES containing fields SITEID and CATEGORYID, thus assigning specific categories to specific sites.

There is a MEDIA table, listing details on stored pdf documents.

There is a MEDIA_CATEGORIES table (fields CATEGORYID and MEDIAID, assigning specific media to specific categories.

There is a USERS table, containing users info.

Finally there is a MEDIA_AUTHORS table (fields USERID, MEDIAID) linking authors(users) to their media. There may be one or more authors to a given media, e.g. if a document was co-authored.

I would like to display a list of AUTHORS (users) that have MEDIA on SITE X.

However, this query goes a bit beyond my capabilities. In short:

LIST USER(S)
WHERE USER(S) LINKED TO MEDIA
WHERE MEDIA LINKED TO CATEGORY
WHERE CATEGORY IS ON SITE X

I don't know whether I should be trying to create a complex join, or subquery, or what. Could anyone enlighten me? Assistance is much appreciated.

Thanks

Ted

 

Dinkar




msg:4361296
 7:44 am on Sep 12, 2011 (gmt 0)

I am not sure, but I guess there is problem with your databases.

Example: Does your SITES table has SITEID that you use in your SITES_CATEGORIES table to set relation?

ManMountain




msg:4361303
 8:10 am on Sep 12, 2011 (gmt 0)

Hi Dinkar

yes, there are tables:

USERS
MEDIA
CATEGORIES
SITES

all with relevant data and unique primary key. Then there are several linking tables, each with 3 fields (first field also being primary key, + 2 linking fields):

MEDIA_AUTHORS - links USERS.ID to MEDIA.ID
MEDIA_CATEGORIES - links MEDIA.ID to CATEGORIES.ID
CATEGORIES_SITES - links CATEGORIES.ID to SITES.ID

So, the data + linking data is all there, I just need some help formulating what seems to me to be quite a deep query - at least beyond my scope right now.

Dinkar




msg:4361324
 10:26 am on Sep 12, 2011 (gmt 0)

I am assuming you have something similar to the following tables :-


Table SITES
-------------
- SITEID
- URL


Table CATEGORIES
-----------------
- CATEGORYID
- CATEGORYNAME


Table SITES_CATEGORIES
------------------------
- SITEID
- CATEGORYID


Table MEDIA
----------------
- MEDIAID
- SITEID
- PDF


Table MEDIA_AUTHORS
---------------------
- USERID
- MEDIAID
- USERNAME


Now, you want to display a list of AUTHORS (users) that have MEDIA on SITE X.


You have URL (SITE X)

- So get SITEID from Table SITES using the URL.

- Then get all MEDIAID related to SITEID from Table MEDIA.

- Get all USERID related to MEDIAID from Table MEDIA_AUTHORS. Remove duplicate USERID

- Show the list of USERID and related data (USERNAME etc)



Suggestion:-
----------------

Instead of adding MEDIAID to Table MEDIA_AUTHORS, Add USERID to Table MEDIA. So you can get USERID using SITEID from Table MEDIA.


Table MEDIA
----------------
- MEDIAID
- USERID
- SITEID
- PDF


Table MEDIA_AUTHORS
---------------------
- USERID
- USERNAME

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved