Trying to make site map

I am trying to create a ui for a page management script. During this
step the user chooses which existing page the new page will link
under. Each record has a field called Page_Above, which references the
primary key number (id) of the page above it. Currently I have 4
records in the database:
(id, name, Page_Above)
1, Page1, 0
2, Page2, 1
3, Page3, 2
4, Page4, 1

Here is the pertinent snippet:


include "config.php";
$conn = mysql_connect($server, $DBusername, $DBpassword);
mysql_select_db($database,$conn);
$sql = "SELECT * FROM $Gen WHERE Category = '$Cat' AND Page_Above
= 0";
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($pageArray = mysql_fetch_array($result)) {

$prime_id = $pageArray['id'];
$Name = $pageArray['Name'];
print ("$Name align=left valign=top>");


$sql = "SELECT * FROM $Gen WHERE Page_Above = $prime_id";
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($secpageArray = mysql_fetch_array($result)) {
// give a name to the fields
$second_id = $secpageArray['id'];
$Name = $secpageArray['Name'];
print ("$Name
");


$sql = "SELECT * FROM $Gen WHERE Page_Above = $second_id";
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($thpageArray = mysql_fetch_array($result)) {
// give a name to the fields
$third_id = $thpageArray['id'];
$Name = $thpageArray['Name'];
print ("");
print ("$Name
");


}

}

}

The results I am getting are incomplete, it only pulls one page per
level instead of all the pages per level, Like this:
"Page1, Page 2, Page3"
it skips Page4.

When I remove the request for the third level, then I get:
"Page1,Page2,Page4" Which is correct up to that point. It breaks
apart when I try to go on the third level.

Any ideas how I can get this to work? In the end there will be 5 levels.
Thanks


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to make site map

>It breaks apart when I try to go on the third level.

On the second iteration of $secpageArray, it fetches Row4 from the=
database. Because there is no Page_Above that equals 4 (that is, no child=
of Page 4 in your map), the level 3 query might be coming back as False=
(no records returned), which might be triggering the die() function. This=
might be the cause were it not for the fact that...

You are using the variable $result as the reference object for all three=
queries. Once the third level uses $result, you've lost the reference to=
the second level query's recordset.




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to make site map

>It breaks apart when I try to go on the third level.

=Sorry, my email client didn't word-wrap.==

On the second iteration of $secpageArray, it fetches Row4 from the
database. Because there is no Page_Above that equals 4 (that is, no
child of Page 4 in your map), the level 3 query might be coming back
as False (no records returned), which might be triggering the die()
function. This might be the cause were it not for the fact that...

You are using the variable $result as the reference object for all
three queries. Once the third level uses $result, you've lost the
reference to the second level query's recordset.




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to make site map

On Mon, 21 Dec 2009 14:53:58 -0700
Zach Hicken wrote:
You are going to end up with alot of repetitous code if you repeat the process for every level.

I would instead select all page data, and then construct a nested array to show the page structure, and then work off of that.

example:

$pages = array();
$sql = "SELECT * FROM $Gen WHERE Category = '$Cat' ORDER BY Page_Above desc"; //start from the lower levels, build up
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set
while ($pageArray = mysql_fetch_array($result)) {
if(isset($pages[$pageArray['id']])){
ksort($pages[$pageArray['id']]);
$pageArray['children'] = $pages[$pageArray['id']];
unset( $pages[$pageArray['id']]);
}
$pages[$pageArray['Page_Above']][$pageArray['id']] = $pageArray;
}
$pages = $pages[0]; // remove unnamed top-level (nodes not properly set as children)
sort($pages);
function showpages($p, $level=0){
$line = "%d\t%s\t%d\r\n";
printf($line, $p['id'], $p['name'], $p['Page_Above']);
if(isset($p['children'])) foreach($p['children'] as $child) showpages($child);
}
foreach($pages as $page) showpages($page);



> I am trying to create a ui for a page management script. During this
> step the user chooses which existing page the new page will link
> under. Each record has a field called Page_Above, which references the
> primary key number (id) of the page above it. Currently I have 4
> records in the database:
> (id, name, Page_Above)
> 1, Page1, 0
> 2, Page2, 1
> 3, Page3, 2
> 4, Page4, 1
>
> Here is the pertinent snippet:
>
>
> include "config.php";
> $conn = mysql_connect($server, $DBusername, $DBpassword);
> mysql_select_db($database,$conn);
> $sql = "SELECT * FROM $Gen WHERE Category = '$Cat' AND Page_Above
> = 0";
> $result = mysql_query($sql, $conn) or die(mysql_error());
> //go through each row in the result set and display data
> while ($pageArray = mysql_fetch_array($result)) {
>
> $prime_id = $pageArray['id'];
> $Name = $pageArray['Name'];
> print ("$Name > align=left valign=top>");
>
>
> $sql = "SELECT * FROM $Gen WHERE Page_Above = $prime_id";
> $result = mysql_query($sql, $conn) or die(mysql_error());
> //go through each row in the result set and display data
> while ($secpageArray = mysql_fetch_array($result)) {
> // give a name to the fields
> $second_id = $secpageArray['id'];
> $Name = $secpageArray['Name'];
> print ("$Name
");
>
>
> $sql = "SELECT * FROM $Gen WHERE Page_Above = $second_id";
> $result = mysql_query($sql, $conn) or die(mysql_error());
> //go through each row in the result set and display data
> while ($thpageArray = mysql_fetch_array($result)) {
> // give a name to the fields
> $third_id = $thpageArray['id'];
> $Name = $thpageArray['Name'];
> print ("");
> print ("$Name
");
>
>
> }
>
> }
>
> }
>
> The results I am getting are incomplete, it only pulls one page per
> level instead of all the pages per level, Like this:
> "Page1, Page 2, Page3"
> it skips Page4.
>
> When I remove the request for the third level, then I get:
> "Page1,Page2,Page4" Which is correct up to that point. It breaks
> apart when I try to go on the third level.
>
> Any ideas how I can get this to work? In the end there will be 5 levels.
> Thanks
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
Simcha Younger

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to make site map

Thank you very much

On Jan 3, 2010, at 11:42 AM, Simcha Younger wrote:

> On Mon, 21 Dec 2009 14:53:58 -0700
> Zach Hicken wrote:
> You are going to end up with alot of repetitous code if you repeat
> the process for every level.
>
> I would instead select all page data, and then construct a nested
> array to show the page structure, and then work off of that.
>
> example:
>
> $pages = array();
> $sql = "SELECT * FROM $Gen WHERE Category = '$Cat'
> ORDER BY Page_Above desc"; //start from the lower levels, build up
> $result = mysql_query($sql, $conn) or
> die(mysql_error());
> //go through each row in the result set
> while ($pageArray = mysql_fetch_array($result)) {
>
> if(isset($pages[$pageArray['id']])){
>
> ksort($pages[$pageArray['id']]);
>
> $pageArray['children'] = $pages[$pageArray['id']];
>
> unset( $pages[$pageArray['id']]);
> }
>
> $pages[$pageArray['Page_Above']][$pageArray['id']] = $pageArray;
> }
> $pages = $pages[0]; // remove unnamed top-level (nodes not properly
> set as children)
> sort($pages);
> function showpages($p, $level=0){
> $line = "%d\t%s\t%d\r\n";
> printf($line, $p['id'], $p['name'],
> $p['Page_Above']);
> if(isset($p['children']))
> foreach($p['children'] as $child) showpages($child);
> }
> foreach($pages as $page) showpages($page);
>
>
>
>> I am trying to create a ui for a page management script. During this
>> step the user chooses which existing page the new page will link
>> under. Each record has a field called Page_Above, which references
>> the
>> primary key number (id) of the page above it. Currently I have 4
>> records in the database:
>> (id, name, Page_Above)
>> 1, Page1, 0
>> 2, Page2, 1
>> 3, Page3, 2
>> 4, Page4, 1
>>
>> Here is the pertinent snippet:
>>
>>
>> include "config.php";
>> $conn = mysql_connect($server, $DBusername, $DBpassword);
>> mysql_select_db($database,$conn);
>> $sql = "SELECT * FROM $Gen WHERE Category = '$Cat' AND Page_Above
>> = 0";
>> $result = mysql_query($sql, $conn) or die(mysql_error());
>> //go through each row in the result set and display data
>> while ($pageArray = mysql_fetch_array($result)) {
>>
>> $prime_id = $pageArray['id'];
>> $Name = $pageArray['Name'];
>> print ("$Name >> align=left valign=top>");
>>
>>
>> $sql = "SELECT * FROM $Gen WHERE Page_Above = $prime_id";
>> $result = mysql_query($sql, $conn) or die(mysql_error());
>> //go through each row in the result set and display data
>> while ($secpageArray = mysql_fetch_array($result)) {
>> // give a name to the fields
>> $second_id = $secpageArray['id'];
>> $Name = $secpageArray['Name'];
>> print ("$Name
");
>>
>>
>> $sql = "SELECT * FROM $Gen WHERE Page_Above = $second_id";
>> $result = mysql_query($sql, $conn) or die(mysql_error());
>> //go through each row in the result set and display data
>> while ($thpageArray = mysql_fetch_array($result)) {
>> // give a name to the fields
>> $third_id = $thpageArray['id'];
>> $Name = $thpageArray['Name'];
>> print ("");
>> print ("$Name
");
>>
>>
>> }
>>
>> }
>>
>> }
>>
>> The results I am getting are incomplete, it only pulls one page per
>> level instead of all the pages per level, Like this:
>> "Page1, Page 2, Page3"
>> it skips Page4.
>>
>> When I remove the request for the third level, then I get:
>> "Page1,Page2,Page4" Which is correct up to that point. It breaks
>> apart when I try to go on the third level.
>>
>> Any ideas how I can get this to work? In the end there will be 5
>> levels.
>> Thanks
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
>
> --
> Simcha Younger
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php