Minimizing MySQL queries for a site map?
Posted: Wed Feb 04, 2009 6:52 pm
It's that time again! I'm now working on a admin control panel feature called Site Map which is essentially intended to be an access point to all sections and pages on my site which is now database drive (Version 2.9).
I currently have two CMS related tables, cms_pages and cms_sections. The later only contains the id, section_name, and section_url (so 1, 'Control Panel', and 'admin' would be an example).
Visually I intend to have a table or table-like setup with a table sub-header (th element) for each section (on my site examples would be blog, forum, home, philosophy, web, etc) and below the page (entries) associated with each section.
Currently I conceptually have the query count down to two. The main issue is dynamically determining what the highest number for the section would be though I suppose I could simply $i++. The issue however is if there are no pages associated with a section (say section three) then you wouldn't see anything for section three and greater (presuming you had let's say a dozen sections). So the first query I imagine is to get the highest ID of from the cms_sections table for the moment at least. Here is the second MySQL query which works just fine...
I'm really excited about this as I'm moving from a static file/FTP setup to a database driven CMS that I'm building myself. I'm interested in everyone's thoughts please!
I currently have two CMS related tables, cms_pages and cms_sections. The later only contains the id, section_name, and section_url (so 1, 'Control Panel', and 'admin' would be an example).
Visually I intend to have a table or table-like setup with a table sub-header (th element) for each section (on my site examples would be blog, forum, home, philosophy, web, etc) and below the page (entries) associated with each section.
Currently I conceptually have the query count down to two. The main issue is dynamically determining what the highest number for the section would be though I suppose I could simply $i++. The issue however is if there are no pages associated with a section (say section three) then you wouldn't see anything for section three and greater (presuming you had let's say a dozen sections). So the first query I imagine is to get the highest ID of from the cms_sections table for the moment at least. Here is the second MySQL query which works just fine...
Code: Select all
SELECT cp.meta_description, cp.meta_language, cp.meta_robots, cp.title, cs.id AS id_section, cs.section_name, cs.section_url FROM cms_pages AS cp INNER JOIN cms_sections AS cs ON (cp.id_section = cs.id)