Parent / Child sub-page menu

colin · October 23, 2012, 3:15am

Hi all, I have written some php code to display my pages + can add parent id numbers to the row in the database - but what I can’t do is create some code to show the sub-pages directly under the parent page in a menu.

Here is the sql code:

CREATE TABLE IF NOT EXISTS `pages` ( `id` int(20) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `intro` varchar(255) NOT NULL, `entry` longtext NOT NULL, `creation_date` datetime NOT NULL, `parent_id` int(11) NOT NULL, PRIMARY KEY (`id`)

and here is the php code to display the pages:

[php]

// Make the query:
$q = “SELECT id, title, intro, DATE_FORMAT(creation_date, ‘%M %d, %Y’) FROM pages ORDER BY creation_date ASC LIMIT $start, $display”;
$r = mysqli_query ($dbc, $q); // Run the query.

// Table header.
echo ’

';

// Fetch and print all the records:
$bg =’#eeeeee’; // Set the initial background colour.

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

$bg = ($bg==’#eeeeee’ ? ‘#ffffff’ : ‘#eeeeee’); // Swich the bacground color.

echo ’

';[/php]

I suppose I need a second query and while loop. But I am not sure what to put and how to make a search of sub-pages to the page that has been displayed.

Also I have tried searching via Google etc but can’t find a clear answer - or not one that I recognise as such.

Any advice or direction would be appreciated.

Cheers,
Colin

Title	Intro	Edit	Delete
' . stripslashes($row['title']) . '	' . stripslashes($row['intro']) . '	Edit	Delete

colin · October 23, 2012, 5:05am

This looks useful:

colin · October 23, 2012, 9:27pm

I have got this far and changed this line:

[php]$q = “SELECT id, title, intro, parent_id, DATE_FORMAT(creation_date, ‘%M %d, %Y’) FROM pages WHERE parent_id = 0 ORDER BY creation_date ASC LIMIT $start, $display”;[/php]

but I am not sure what to do to then show the related child pages.

Any ideas?

Cheers,
Colin

m11 · October 23, 2012, 9:33pm

I’m not entirely sure what you need, but have you tried building an array using the parent_id? For example:

[php]
$menu = array();
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$menu[$row[‘parent_id’]][] = $row[‘id’];
}
print_r($menu);
[/php]

This would give you an array of children for each parent_id.

colin · October 23, 2012, 10:39pm

Hi M@tt, thanks for this but I’m not sure how to put this in my script + where. All I get when I do is this:

Array ( [0] => Array ( [0] => 1 [1] => 2 ) [1] => Array ( [0] => 3 ) )

could you advise further?

thank you for your help.

Cheers,
Colin

m11 · October 23, 2012, 11:09pm

That array does not look right at all, did you copy & paste my code?

Perhaps posting your table data will give us a better idea of what you are trying to do.

colin · October 24, 2012, 12:18am

I did copy and paste the code fully.

[php]// Make the query:
$q = “SELECT id, title, intro, DATE_FORMAT(creation_date, ‘%M %d, %Y’) FROM pages ORDER BY creation_date ASC LIMIT $start, $display”;
$r = mysqli_query ($dbc, $q); // Run the query.

$menu = array();
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

$menu[$row[‘parent_id’]][] = $row[‘id’];
}
print_r($menu);[/php]

Here is the sql code - is that what you wanted?

[code]CREATE TABLE IF NOT EXISTS pages (
id int(20) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
intro varchar(255) NOT NULL,
entry longtext NOT NULL,
creation_date datetime NOT NULL,
parent_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

NSERT INTO pages (id, title, intro, entry, creation_date, parent_id) VALUES
(1, ‘Home’, ‘Welcome’, ‘entry’, ‘2012-02-06 06:28:16’, 0),
(2, ‘Bio’, ‘The history of me’, ‘Some text
’, ‘2012-10-16 13:24:26’, 0),
(3, ‘Videos’, ‘Videos’, ‘Some text
’, ‘2012-10-16 13:27:28’, 1);[/code]

Thank you for your help.
Cheers,
Colin

m11 · October 24, 2012, 12:21am

So based on your table data, you want “Videos” to be a child of “Home”?

colin · October 24, 2012, 12:26am

Yes - just for eg

m11 · October 24, 2012, 12:39am

Sorry I knew that’s what you wanted but I didn’t think it through in my initial response Also, I noticed parent_id was missing from your query.

Try this:

[php]
// Make the query:
$q = “SELECT id, title, intro, DATE_FORMAT(creation_date, ‘%M %d, %Y’), parent_id FROM pages ORDER BY creation_date ASC LIMIT $start, $display”;
$r = mysqli_query ($dbc, $q); // Run the query.

$menu = array();
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
if (!empty($row[‘parent_id’])) {
$menu[$row[‘parent_id’]][‘children’][] = $row; // If parent_id is defined, this is a child
}
else {
$menu[$row[‘id’]] = $row; // If no parent_id is defined, this is a parent
}
}
print_r($menu);
[/php]

colin · October 24, 2012, 12:56am

What am I doing wrong? I pasted you code and got this

Array ( [1] => Array ( [id] => 1 [title] => Home [intro] => Welcome [DATE_FORMAT(creation_date, ‘%M %d, %Y’)] => February 06, 2012 [parent_id] => 0 [children] => Array ( [0] => Array ( [id] => 3 [title] => Videos [intro] => Videos [DATE_FORMAT(creation_date, ‘%M %d, %Y’)] => October 16, 2012 [parent_id] => 1 ) ) ) [2] => Array ( [id] => 2 [title] => Bio [intro] => The history of me [DATE_FORMAT(creation_date, ‘%M %d, %Y’)] => October 16, 2012 [parent_id] => 0 ) )

Thank you for your efforts.

m11 · October 24, 2012, 1:04am

Array ( 
	[1] => Array ( 
		[id] => 1 
		[title] => Home 
		[intro] => Welcome 
		[DATE_FORMAT(creation_date, '%M %d, %Y')] => February 06, 2012 
		[parent_id] => 0 
		[children] => Array (
			=> Array ( 
				[id] => 3 
				[title] => Videos 
				[intro] => Videos 
				[DATE_FORMAT(creation_date, '%M %d, %Y')] => October 16, 2012 
				[parent_id] => 1 
				) 
			) 
		) 
	[2] => Array ( 
		[id] => 2 
		[title] => Bio 
		[intro] => The history of me 
		[DATE_FORMAT(creation_date, '%M %d, %Y')] => October 16, 2012 
		[parent_id] => 0 
		) 
)

That looks right. Aside from the DATE_FORMAT which you would probably want to modify your query to use

DATE_FORMAT(creation_date, '%M %d, %Y') AS `creation_date`

I can’t do all the work for you. You have an array now with your child elements under each parent. Here is an example of how you could use the array.

[php]
echo “

”;

foreach($menu as $item) {

echo $item[‘title’] . PHP_EOL;

// Look for children of parent

if (isset($item[‘children’]) && !empty($item[‘children’])) {

// Loop children

foreach($item[‘children’] as $child) {

echo “\t” . $child[‘title’] . " is a parent of " . $item[‘title’] . PHP_EOL;

}

}

}

echo “

”;
[/php]

colin · October 24, 2012, 1:11am

That’s fantastic. Thank you for your help and patience.

Cheers,
Colin

colin · October 24, 2012, 1:14am

That’s more than enough for me to be getting on with and I’m now heading in the right direction to understanding it too - which is most important.

Cheers,
Colin