I wrote this script to create a hierarchy site index from a database that has fields for the URL and the Page Title
What is the best query to get just the URL and the Page Title from the PHPDig database?
PHP Code:
<?
#Hierarchy Site Map
#siteindex.php
#3/23/2004
$myisserver="localhost";
$myisusername="your-mysql-db-username";
$myispassword="your-mysql-db-password";
$myisdb="your-mysql-db-name";
#DATABASE INITIALIZATION
$db = mysql_connect($myisserver,$myisusername,$myispassword); # Get Connection Settings From Config File
mysql_select_db($myisdb,$db); # Get Connection Settings From Config File
$sql = "SELECT url,pagetitle FROM sometable GROUP BY url ORDER by url";
# This script assumes urls stored as something like: /some/directory/structure
$result_id = mysql_query($sql);
$num = mysql_num_rows($result_id);
#
print "<ul>"; #Start the bullet list
for ($n = 0; $n<$num; $n++) {
list($path_name,$title)=mysql_fetch_row($result_id); #grab the URL and title from the MySQL result
$paths = explode('/',rawurldecode(rtrim($path_name, "/"))); #Trims any trailing slash. Then creates array of URL
$num_levels = count($paths); #counts directory levels in URL
if ($path_name=="") {
$num_levels = 1; #Kluge for the homepage so it shows up on same bullet level as main categories
}
if ($title!="") { #you can exempt something from the list by not giving it a title
$title = "<li>" . "<a href='".$urlroot . $path_name."'>" . $title . "</a></li>"; #set up the link
for ($a = 1; $a<$num_levels; $a++) {
$title = "<ul>" . $title . "</ul>"; #for every additional directory level down this link is, indent again
}
$title .= "\n"; #add a carriage return to keep the output neat
print $title; #show this bullet
}
}
print "</ul>"; #Close out the bullet list
?>