Bread Crumbs Navigation Script (PDO)

PHP
1

When I upgraded my database queries to PDO, I couldn’t figure out how to modify the queries I was using to create bread-crumbs-style navigation links. So I guess I need to start from scratch.

Consider a table with three columns, for page URL, page Title and Parent, as follows:

URL | Title | Parent
Life | Life | (NULL)
Animalia | Animals | Life
Mammalia | Mammals | Animal
Panthera_leo | Mammalia | Mammalia

What script can I use to display the following if I visit the page MySite/Life/Panthera_leo?

Life > Animals (linked to MySite/Life/Animalia) > Mammals (linked to Mammalia) > Lion

And if I visited the page MySite/Life/Animalia, the navigation links would be Life > Animals, of course.

I found a script that almost does the job…


 $stm = $pdo->prepare("SELECT :MyURL AS node_name, up1.URL AS up1_URL, up2.URL AS up2_URL
  FROM px_ben AS node
  left outer join px_ben as up1 on up1.URL = :MyParent
  left outer join px_ben as up2 on up2.URL = up1.Parent
  GROUP BY up1.URL
  ORDER BY node_name");
 $stm->execute(array(
  'MyURL'=>$MyURL,
  'MyParent'=>$Parent
 ));

 while ($row = $stm->fetch())
 {
  $GP = $row['up2_URL'];
  $Parent = $row['up1_URL'];
  // $URL = $row['URL'];
  // $Title = $row['Title'];
 }

However, it only displays URL’s; I haven’t figured out how to make it display page titles. Also, it doesn’t automatically display the complete series of links; rather, I have to echo $Grandparent ($GP) > $Parent, adding $GreatGrandParent if it has that many links, etc.

Thanks.

2

Maybe something like this will work for you.

<?php
include('PDOconn.php');
/*
// EXAMPLE DATA //
URL | Title | Parent
Life | Life | (NULL)
Animalia | Animals | Life
Mammalia | Mammals | Animal
Panthera_leo | Mammalia | Mammalia
*/

//Set domain name
$domain_name = "MySite.com";

//Get request url
$MyURL = $_SERVER['REQUEST_URI'];	
//for test
//$MyURL = "http://MySite.com/Life/Animalia/Mammalia/Panthera_leo";
///////////////
// In case index.php is in url, remove
$MyURL = str_replace("/index.php", "", $MyURL);
//Explode into array
$path_parts =  explode('/',$MyURL);
// Reverse to get last item	
$path_parts = array_reverse($path_parts, false);
$target = $path_parts[0];


if(isset($target) && !empty($target)){
	try {
		$stm = $pdo->prepare("SELECT
		`a`.`URL` as aURL,
		`a`.`Title` as aTitle,
		`b`.`URL` as bURL,
		`b`.`Title` as bTitle,
		`c`.`URL` as cURL,
		`c`.`Title` as cTitle,
		`d`.`URL` as dURL,
		`d`.`Title` as dTitle,
		`d`.`Parent` as dParent
		FROM `px_ben` AS d
			LEFT JOIN `px_ben` AS c
				ON `c`.`URL` = `d`.`Parent`
			LEFT JOIN `px_ben` AS b
				ON `b`.`URL` = `c`.`Parent`
			LEFT JOIN `px_ben` AS a
				ON `a`.`URL` = `b`.`Parent`					
		WHERE `d`.`URL` = :target");
		$stm->bindParam(":target", $target);
		$stm->execute();		
		$row = $stm->fetch(PDO::FETCH_ASSOC);
	
	} catch (Exception $e) {
	    //die("There's an error in the query!");
	}
}

$breadcrumbs = "";

if(!empty($row['aTitle']) && !empty($row['aURL'])):
	$breadcrumbs .= "<a href=\\"/" . $domain_name ."/".$row['aURL'] . "\\">" . $row['aTitle'] . "</a>";
endif;
if(!empty($row['bTitle']) && !empty($row['bURL'])):
	$breadcrumbs .= " > <a href=\\"/" . $domain_name ."/".$row['aURL']."/".$row['bURL'] . "\\">" . $row['bTitle'] . "</a>";
endif;
if(!empty($row['cTitle']) && !empty($row['cURL'])):
	$breadcrumbs .= " >> <a href=\\"/" . $domain_name ."/".$row['aURL']."/".$row['bURL']."/".$row['cURL'] . "\\">" . $row['cTitle'] . "</a>";
endif;
if(!empty($row['dTitle']) && !empty($row['dURL'])):
	$breadcrumbs .= " >>> <a href=\\"/" . $domain_name ."/".$row['aURL']."/".$row['bURL']."/".$row['cURL']."/".$row['dURL'] . "\\">" . $row['dURL'] . "</a>";
endif;

?>
<html>
<body>
<?php echo $breadcrumbs; ?>
</body>
</html>
3

Just wanted show result of test code above.