Page 1 of 1

php mysql statement to help child find its parent

Posted: Tue May 23, 2006 10:57 am
by sublimenal
Hello everyone,
Image

Im trying to make submenus for a cms im playing with, but i cant find the correct SQL SELECT im suppose to use to get the submenu items. basicly I want it to show up like this

Home
About us
Contact
Test Page
- test page sub

and for each sub to be categorized under its parent. As you see i have a parent column with the id of the items parent. What sql statement would I use to select all subitems of the parent?

I have the code working for my parents but not for the child, here is the current child code:

Code: Select all

$query3 = $database->query("SELECT * FROM pages WHERE parent = id");

while ($list3 = mysql_fetch_array($query3))
{
       		$id = stripslashes($list3['id']);
		$date = stripslashes($list3['date']);
		$title = stripslashes($list3['title']);
		$linktitle = stripslashes($list3['linktitle']);
		$pagename = stripslashes($list3['pagename']);
		$description = stripslashes($list3['description']);
		$menuitem = stripslashes($list3['menuitem']);
		$parent = stripslashes($list3['parent']);
			
	
		$items .=  '<div id="anylinkmenu'.$h++.'" class="anylinkcss"><a  href="'.$siteroot.'pages/'.$id.'/'.$pagename.'.html">'.$linktitle.' </a></div>';
		echo $items;
        }
heres the page in action:

Code: Select all

http://www.mcguireandmcguirelaw.com
any help is greatly appreciated thanks :)

Posted: Tue May 23, 2006 11:43 am
by TheMoose
You can't just straight up query parents and their children, because the children is a separate recordset in and of itself, based on the way you have your DB schema. You'll need to use a nested loop to get each child for each parent. IE:

Code: Select all

$query3 = $database->query("SELECT * FROM pages WHERE parent = 0");  // gets only parent items, no children
while ($list3 = mysql_fetch_array($query3))
{
                
                $id = stripslashes($list3['id']);

                $childQuery = $database->query("SELECT * FROM pages WHERE parent=$id");
                while($childlist = mysql_fetch_array($childQuery)) {
                     //  do stuff for children here
                }

                $date = stripslashes($list3['date']);
                $title = stripslashes($list3['title']);
                $linktitle = stripslashes($list3['linktitle']);
                $pagename = stripslashes($list3['pagename']);
                $description = stripslashes($list3['description']);
                $menuitem = stripslashes($list3['menuitem']);
                $parent = stripslashes($list3['parent']);
                       
       
                $items .=  '<div id="anylinkmenu'.$h++.'" class="anylinkcss"><a  href="'.$siteroot.'pages/'.$id.'/'.$pagename.'.html">'.$linktitle.' </a></div>';
                echo $items;
        }

Posted: Tue May 23, 2006 11:59 am
by sublimenal
Hey the moose thanks for the fast reply! Im actually getting submenus now but they are the wrong ones. Check it out, this is my full code from menu.php


and check out the page, its odd hehe

Code: Select all

<?php

  	  		
       echo '<ul>';
	  $query2 = $database->query("SELECT * FROM pages WHERE parent = '0' ORDER by pos");

while ($list2 = mysql_fetch_array($query2))
{
       		$id = stripslashes($list2['id']);
		$date = stripslashes($list2['date']);
		$title = stripslashes($list2['title']);
		$linktitle = stripslashes($list2['linktitle']);
		$pagename = stripslashes($list2['pagename']);
		$description = stripslashes($list2['description']);
		$menuitem = stripslashes($list2['menuitem']);
		$parent = stripslashes($list2['parent']);
		$topid = $_GET['id'];
		
		if ($topid == $id){		
		$class = 'class="active"';
		} else {
		$class = '';
		}
		
		
		
	
		echo '<li '.$class.'><a onmouseover="dropdownmenu(this, event, \'anylinkmenu'.$i++.'\')" href="'.$siteroot.'pages/'.$id.'/'.$pagename.'.html">'.$linktitle.' </a> '.$sub.'</li> ';
		
        } 

  echo '</ul>';

$query3 = $database->query("SELECT * FROM pages WHERE parent = 0 ORDER by pos");  // gets only parent items, no children
while ($list3 = mysql_fetch_array($query3))
{
               
                $id = stripslashes($list3['id']);

                $childQuery = $database->query("SELECT * FROM pages WHERE parent=$id ORDER by pos");
                while($childlist = mysql_fetch_array($childQuery)) {
                     //  do stuff for children here
                }

                $date = stripslashes($list3['date']);
                $title = stripslashes($list3['title']);
                $linktitle = stripslashes($list3['linktitle']);
                $pagename = stripslashes($list3['pagename']);
                $description = stripslashes($list3['description']);
                $menuitem = stripslashes($list3['menuitem']);
                $parent = stripslashes($list3['parent']);
                       
       
                $items =  '<div id="anylinkmenu'.$h++.'" class="anylinkcss"><a  href="'.$siteroot.'pages/'.$id.'/'.$pagename.'.html">'.$linktitle.' </a></div>';
                echo $items;
        } 

?>

Posted: Tue May 23, 2006 12:14 pm
by TheMoose
Ahh, gotcha. I just mistook your child code for being your parent code. My fault. You had it basically right at the beginning, the only part you missed was you put

Code: Select all

$query3 = $database->query("SELECT * FROM pages WHERE parent = id  ORDER by pos");
when it should be

Code: Select all

$query3 = $database->query("SELECT * FROM pages WHERE parent = $id  ORDER by pos");
The first query does not return anything unless the parent id is the current record's ID. If you are on record ID #2, and ID #2 had a parent ID of 2, it would return that row (which doesn't make sense, something can't be it's own parent). The second one puts the current parent ID in the query and will then return the children for that item.

Cheers
- Moose

Posted: Tue May 23, 2006 12:48 pm
by sublimenal
Ahh ok i see what your saying, lol dont you hate when your SO close to a solution yet SO far away? hehe

Thanks allot mate.