SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: joins....again

  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy joins....again

    This could likely go in PHP or MySQL but I think I'll put it here...

    2 tables...
    Code:
    CREATE TABLE `pages` (
      `id` int(3) NOT NULL auto_increment,
      `title` varchar(40) default NULL,
      `secid` int(1) default NULL,
      `shortname` varchar(10) default NULL,
      `content` text,
      `lastupdated` timestamp(14) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `id` (`id`)
    ) TYPE=MyISAM;
    Code:
    DROP TABLE IF EXISTS sections;
    CREATE TABLE `sections` (
      `id` int(1) NOT NULL auto_increment,
      `section` varchar(11) default NULL,
      `url` varchar(63) default NULL,
      KEY `id` (`id`)
    ) TYPE=MyISAM;
    Trying to pull the sections out of there and any pages associated with that section (or the reverse?). Basically, I am needing to execute one loop in PHP and get all the data I need for that section (if it is possible). The join would be on pages.secid and sections.id.

    This is what I have now but it's throwing errors if there are no pages associated with the section.
    PHP Code:
    <?php
    echo '<ul class="navitems">';
                
    $sqlmainmenu mysql_query("SELECT * FROM sections");
                while(
    $sqlmainmenurow=mysql_fetch_array($sqlmainmenu))
                    {
                    echo
    '<li class="navitems"><a href="#">'.$sqlmainmenurow['section'].'</a></li>';
                    
    $getmenuitemssql ="SELECT * FROM pages WHERE secid = '".$sqlmainmenurow['id']."'";
                    
    //exit($getmenuitemssql);
                    //$getmenuitemssql = @mysql_query($getmenuitemssql);
                    //if(@mysql_affected_rows($getmenuitemssql) !=0)
                        //{
                        
    echo'<ul>';
                        while(
    $menuitemrows mysql_fetch_array($getmenuitemssql))
                            {
                            echo
    '<li><a href="'.$menuitemrows['shortname'].'.html">'.$menuitemrows['title'].'</a></li>\n\t\t';
                            }
                        echo
    '</ul>';
                        }
                    }
    echo 
    '</ul>';
    ?>
    Forgive my commenting. I'm debugging as I go.
    Aaron Brazell
    Technosailor



  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    This query should get everything at once, as opposed to having to use two separate queries and a nested loop.
    PHP Code:
    $sql "SELECT p.title AS pagetitle, p.shortname AS shortname, 
    s.section AS section, s.id
    FROM pages p 
    INNER JOIN sections s ON p.secid = s.id 
    ORDER BY s.id;"


  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    will try it. Thanks Vinnie.
    Aaron Brazell
    Technosailor




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •