In part 1 of how to build a database driven website we looked at creating the database and tables and looked at some simple options for inserting data. In this part we are going to look at connecting to the DB using PHP, creating a simple template as well as discussing a few site architecture issues.
In this part we are going to look at connecting to the DB using PHP, creating
a simple template as well as touching on a few site architecture issues along
the way.
We are going to use a simple PHP connection script to connect to your DB, which
will be saved in a separate file and stored outside of root.The connection script
will contain the username and password for connecting to our database.
It looks like this.
<?
@ $db = mysql_connect(“localhost”, “username”, “password”);
//this line specifies the user and password for the database we intend to access
if (!$db) //if we are unable to connect to the database we tell people {
echo “Error: Could not connect to database. Please try again later”;
}
mysql_select_db(“hotels”);
?>
We save the above code in a file and call it conn.php. We can then use a PHP
include or a require at the top of pages that access the database like so.
<?
require “../conn.php”;
$country=”England”; //set the country for the database
?>
More on connecting to the database further on, for now, lets briefly look at
a typical page and how we intend to output our content and urls.
Page structure and URL formats
Our database will be used to determine our url naming conventions. We will use
the place names and hotel names and hotel id’s to form our linking and navigational
structure.Our database contains a series of places from across a region. For
brevity sake lets assume that our site is specific to England.
England is made up of around 39 specific counties. These counties contain a
number of towns and cities. Our plan is to output hotels specific to each county
and town within.We would aim for a clean url structure so that each section
of our site has a url that is logical to the area it represents, is easy to
read and book mark.
Even though our site is dynamic, we can use a handy little feature of the apache
webserver model to change our urls from ugly difficult to read concepts like.
/filename.php?county=hertfordshire&town=hitchin
By using something called an .htaccess file we can rewrite urls so that the
above can be made to look like this
/hertfordshire-hotels-hitchin.html
The .htaccess entry that enables this might look something like this.
RewriteEngine on
RewriteBase /
RewriteRule (.*)-hotels-(.*)\.html$ /filename.php?county=$1&town=$2 [L]
It uses an apache module called mod_rewrite.
This is very handy indeedy as it allows us to have the clean uniform url file
structure we are seeking to use, enabling naming structures throughout thus.
$county-hotels-$town.html becomes hertfordshire-hotels-hitchin.html
* We can add additional lines for additional pages and files specific to whatwe want to achieve. A link to al the site files will be supplied at the end of the series.
A database query for say, hotels in hertfordshire would then use the $county
variable (referenced as $1 in the .htaccess file above), and return a list of
towns or hotels for that particular area.
Page Template and contents
Our sample page is going to be very simple. It consists of a logo at the top,
with a main body content area.
*Ive omitted <head> content for now. The information that follows concentrates on the content that will appear between the <body></body> tags. Full html code will be supplied at the end of the series.
Header Logo
Our header ‘logo’ is a styled header or Hn tag which uses a background image to sit
behind the text and gives it the appearance of being an image..
The <style> is contained in the <head> of the document and looks
like this.
<style>
H1 { font-size: 12pt; height: 24px; width: 100%; letter-spacing:
4px; vertical-align: bottom; color: #000066; font-weight: 35; background:0 url(/header.jpg)
no-repeat; float: left}
</style>
<h1>Hotels and Accommodation in <?=ucfirst($town);?></h1>
Area related images
The images are sourced via a flickr plug in using the tag aspect of the flickr
url to order area specific images.
<script type=”text/javascript” xsrc=”fullflickurl&tag=<?=$town;?>” mce_src=”fullflickurl&tag=<?=$town;?>” ></script>
Contextual adverts
We may as well accrue some residual income. Not everyone will like the hotels
outputted, so if they click on our ads and find what they want its all good,
we win, they win.
<h2><?=$town;?> Travel Ads </h2>
<script type=”text/javascript” >Contextual
ad code </script>
Outputted Hotel
We are outputting our hotel to give a brief outline of its key points . We will
include high level information such as name, price, star ratings, booking url,
full detail url as well as a teaser of its full description.
We achieve this using the following code.
First our query
<?
$query = “select * from hotelcontent1, hotelcontent2
where hotelcontent1.custid = hotelcontent2.custid and town
= ‘$town’ and county =’$county’ and country = ‘$country’ order by custid limit 0, 1 ” ;
$result = mysql_query($query);
?>
Then we want to do some manipulation on the description element of the returned content.
<?
$row=mysql_fetch_array($result);
$desc = stripslashes($row['description']);
$content = $desc;
$text_length =300;
$add=strlen($row[town]);
$text_length=($text_length + $add);
$stricon=($content);
$all_content=strlen(“$stricon”);
$standard_content=substr($stricon ,$text_length);
$compare=stristr($standard_content ,” “);
$minus_content=strlen(“$compare”);
$result_content=$all_content-$minus_content;
$display_content=substr($stricon ,0, $result_content);
$stripped_content=stripslashes($display_content);
$stripped_content=nl2br($stripped_content);
$description=$stripped_content;
?>
Before finally putting it all together and outputting our hotel.
<h2><?=ucfirst($town);?> Hotel of the Month </h2>
<?
echo”<div class=message><h2>$row[ename]</h2><i class=p2>
$row[country] > $row[region] > $row[county]> $row[town] </i><br><table
width=\”95%\” CLASS=\”hoteltables\”><thead> <tr><td
bgcolor=\”#999999\”> <b><font color=\”#FFFF33\”>Hotel
in $row[PostalTown] </font></b></td> </tr></thead>
<tr><td> <p class=p2><img xsrc=\” /$row[photourl]\”
height=\”68\” width=\”90\” alt=\”$county hotels:$row[ename]\”
align=left id=thephoto> <strong> $row[ename] </strong> – $description
…<br> <a xhref=\”/book.php?id=$row[hotelid]\”>Book</a>
<a xhref=\” /more.php?id=$row[hotelid]\”>More</a>
</font></p></td></tr></table> <br></div>”;
?>
Navigational links
Links are an important aspect of the sites architecture they are used by humans
and bots to give anchored clues to the content of their target pages. Search
engine bots use these anchors or its better known phrase of anchor text to help
weight documents in their search engine databases.
To output our links we used the following sql.
<?
$query = “select Distinct county from hotelcontent2 where country = ‘$country’ ORDER BY county” ;
$result = mysql_query($query);
?>
The query here is very simple, it says give me a set of distinct counties from
the database table named hotelcontent1 where the counties returned are a subset of England. These are then outputted via a loop producing a series of links for our navigation footer.
The for loop for which makes this possible, might look a little like this.
<h3>Regions of <?=$country;?></h3>
<p>
<?
$num_results=mysql_num_rows($result); //number of rows
for ($i=0; $i <$num_results; $i++) {
$row = mysql_fetch_array($result);
$countyname=strtolower($row[county]); //ensure the county name is lower case
echo “<a xhref=\”/$countyname-hotels.html\”>$row[county]
hotels </a> “;
}
?>
</p>
The above loop outputs something like this in our footer
We could also produce other links relative to the county we are in. We are in a page that is a subset of Hertfordshire (Hitchin) . Contextually, it makes sense for our users to see what other towns are in Hertfordshire. It also helps our other pages to get indexed by search engines and has the added bonus of making our pages that little bit different for others within in our site.
Lets output the towns relative to our $county and $town variables referenced from the url for our page about Hitchin hotels in Hertfordshire.
<?
$query = “select Distinct town from hotelcontent2 where county = ‘$county’ and country =’$country’ AND town != ‘NULL’ ORDER BY town Limit 0, 20? ;
$result = mysql_query($query);
?>
The query here is very simple, it says give me a set of distinct towns from
the database table named hotelcontent1 where the town is a subset of a variable
named $county (hertfordshire in this instance) .Which when looped and
outputted produces a maximum of 20 urls, or links for our nav footer.
The for loop for which makes this possible, as in the one outlined aboved might look a little like this.
<h4>Towns within <?=ucfirst($county);?></h4>
<p>
<?
$num_results=mysql_num_rows($result); //number of rows
for ($i=0; $i <$num_results; $i++) {
$row = mysql_fetch_array($result);
$townname=strtolower($row[town]); //ensure the town name is lower case
echo “<a xhref=\”/$county-hotels-$townname.html\”>$row[town]
hotels </a> “;
}
?>
</p>
In the next part we will look at building an individual hotel detail page and look at some of the structural elements that will, given a multitude of other factors, help our pages perform relatively well for our target keywords within the search engines.
) They are updated and modified to take into account both the changing nature of the Internet itself as well as the actions of SEO’s looking to exploit a flaw or two.