Minimizing MySQL queries for a site map?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Minimizing MySQL queries for a site map?

Post by JAB Creations »

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...

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)
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!
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Minimizing MySQL queries for a site map?

Post by JAB Creations »

Actually I did get this working with a single query! This is an exceptionally early example though thoughts are still welcomed. :)

...and this is a situation where I do want to use LEFT JOIN. I haven't forgotten about INNER JOIN. :wink:

Code: Select all

$query1 = "SELECT cs.id AS id_section, cs.section_name, cs.section_url, cp.meta_description, cp.meta_language, cp.meta_robots, cp.title, cp.url AS file_url FROM cms_sections AS cs LEFT JOIN cms_pages AS cp ON (cs.id = cp.id_section)";
 echo $query1.'<br />';
 $result1 = mysql_query($query1);
 echo '<div class="border">'."\n";
 echo '<table summary="">'."\n";
 echo '<colgroup style="width: 25%;"></colgroup>'."\n";
 echo '<colgroup style="width: 25%;"></colgroup>'."\n";
 echo '<colgroup style="width: 50%;"></colgroup>'."\n";
 echo '<thead>'."\n";
 echo '<tr><td>Page Title</td><td>Page</td><td>Description</td></tr>'."\n";
 echo '</thead>'."\n";
 echo '<tfoot>'."\n";
 echo '<tr><td>Page Title</td><td>Page</td><td>Description</td></tr>'."\n";
 echo '</tfoot>'."\n";
 echo '<tbody>'."\n";
 while($i <= count($row1) && $row1 = mysql_fetch_assoc($result1))
 {
  if ($i == '1') {$section = $row1['section_name']; echo '<tr><th colspan="3">'.$row1['section_name'].'</th></tr>'."\n";}
  else if ($section != $row1['section_name']) {$section = $row1['section_name']; echo '<tr><th colspan="3">'.$row1['section_name'].'</th></tr>'."\n";}
  echo '<tr><td><a href="'.$row1['section_url'].'/'.$row1['file_url'].'" tabindex="3">'.$row1['title']. '</a></td><td><span>'.$row1['meta_robots'].'</span></td><td><span>'.$row1['meta_description'].'</span></td></tr>'."\n";
 }
 echo '</tbody>'."\n";
 echo '</table>'."\n";
 echo '</div><!-- /.border -->'."\n";
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Minimizing MySQL queries for a site map?

Post by Benjamin »

Glad you were able to get it working. I just wanted to comment on your programming style.

Rather than using PHP to render html, you should instead focus on adding PHP to the HTML. What I mean is that when you are outputting HTML, it's a lot cleaner and easier to modify the code if you drop out of PHP.

This is more along the lines of how I would have formatted the code.

Code: Select all

 
<?php
$query1 = "SELECT
             cs.id AS id_section,
             cs.section_name,
             cs.section_url,
             cp.meta_description,
             cp.meta_language,
             cp.meta_robots,
             cp.title,
             cp.url AS file_url
           FROM
             cms_sections AS cs
             LEFT JOIN cms_pages AS cp ON (cs.id = cp.id_section)";
 
$result1 = mysql_query($query1);
 
?>
<div class="border">
  <table summary="">
    <colgroup style="width: 25%;"></colgroup>
    <colgroup style="width: 25%;"></colgroup>
    <colgroup style="width: 50%;"></colgroup>
    <thead>
      <tr>
        <td>Page Title</td>
        <td>Page</td>
        <td>Description</td>
      </tr>
    </thead>
    <tfoot>
      <tr>
        <td>Page Title</td>
        <td>Page</td>
        <td>Description</td>
      </tr>
    </tfoot>
    <tbody>
<?php
while($i <= count($row1) && $row1 = mysql_fetch_assoc($result1)) {
    if ($i == '1') {
        $section = $row1['section_name'];
        ?>
        <tr>
          <th colspan="3"><?php echo $row1['section_name']; ?></th>
        </tr>
        <?php
    } else if ($section != $row1['section_name']) {
        $section = $row1['section_name'];
        ?>
        <tr>
          <th colspan="3"><?php echo $row1['section_name']; ?></th>
        </tr>
        <?php
    }
    
    ?>
    <tr>
      <td><a href="<?php echo $row1['section_url'] . '/' . $row1['file_url']; ?>" tabindex="3"><?php echo $row1['title']; ?></a></td>
      <td><span><?php echo $row1['meta_robots']; ?></span></td>
      <td><span><?php echo $row1['meta_description']; ?></span></td>
    </tr>
    <?php
 }
 
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Minimizing MySQL queries for a site map?

Post by JAB Creations »

Thanks, yeah I kinda have a mix, sometimes there are bits of code that require a lot of mixing of XHTML and PHP though not always.

Also congrats on 4K posts. :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Minimizing MySQL queries for a site map?

Post by Benjamin »

Thank you :)
Post Reply