MySQL 4.0.16 query should be easy, but it isn't :-(

[PBeakley]

I'm building a website for a school. A major part of this is granting editing rights to certain users (administrators, teachers, volunteers, etc.) for certain pages. I'm doing this with a simple join table (id_page, id_user, id_pageeditorlookup as the primary).

 

In addition, each page as a news feed. Any news entry can be assigned to multiple pages: i.e. the results of a football game can appear on the team's page, the site's front page, and on the students' page. I'm doing this with another join table (id_news, id_page, id_newslookup as the primary).

 

This is where it gets tricky.

 

I want to restrict the "assign this news to page X" funtion to the range of pages a particular user is allowed to edit. Right now, the only version of this form that I've gotten to work brings ALL the pages in the site up on the form, with the pages to which this news item is assigned being checked off. Fine for a "superadmin" who has full rights everywhere, but I want to limit this list to just the pages for which this particular user has editing rights.

 

This is the working query that brings up all available pages:

 

SELECT pagecontent.ID_pagecontent, pagecontent.description_pagecontent, newslookup.ID_news
FROM pagecontent LEFT JOIN newslookup ON (newslookup.ID_page=pagecontent.ID_pagecontent AND newslookup.ID_news=0123456789)
ORDER BY pagecontent.description_pagecontent ASC

 

This is the query I THOUGHT would do what I want it to do:

 

SELECT pagecontent.ID_pagecontent, pagecontent.description_pagecontent,
news.ID_news
FROM (pageeditorlookup LEFT JOIN pagecontent on pageeditorlookup.ID_page
= pagecontent.ID_pagecontent) Left Join (SELECT newslookup.ID_page,
newslookup.ID_news FROM newslookup WHERE newslookup.ID_news = 0123456789) AS
news on pagecontent.ID_pagecontent = news.ID_page
WHERE 
ID_useraccount = $_GET['ID']

 

As should this

 

 

SELECT pagecontent.ID_pagecontent, pagecontent.description_pagecontent,
news.ID_news
FROM 
((SELECT newslookup.ID_page, newslookup.ID_news FROM newslookup WHERE
newslookup.ID_news = 0123456789) AS news right join pagecontent on news.ID_page
= pagecontent.ID_pagecontent)  RIGHT JOIN pageeditorlookup  on
pagecontent.ID_pagecontent = pageeditorlookup.ID_page 
WHERE 
ID_useraccount = $_GET['ID']

 

So I'm wondering...how do I return a result where the available pages you can assign this news item to can ONLY correlate to pages the logged-in editor can edit?

 

Thanks for listening!

 

p.

 

[fenway]

Can you use derived tables on 4.0.X? I don't remember...

[PBeakley]

I think derived tables get introduced in 4.1. Unfortunately, the 4.0.16 MySQL is non-negotiable with the client. 

 

p.

[fenway]

Well, that explains the not working part....