How to build a data driven website part 1

Building a simple database driven website.

This is part one of a multi part yet to be determined ‘how to’ aimed at showing you how to build a database driven website. There are squillions out there already of course, but in this one I’m gonna try and do it a little differently.

*disclamer:I’m not frickin perfect, this is not intended to be the best resource on the topic there is. I’m just sharing the benefits of my experience. Take it in that vein and hopefuly you’ll enjoy!

Ok for this recipe you will need.

  • An idea of what you want to do
  • Basic knowledge of HTML
  • PHP enabled webspace
  • An Apache hosted website
  • A Mysql Database
  • PhpMyAdmin
  • A dataset
  • Will and determination to get it done

Optional extras (desirable but not absolutely necessary)

  • Flashy logo
  • Cool design

An idea of what you want to do

When I built things like this in the past, I had to build from the ground up and learn things as I went. When I first went on the net back in um..1995 I think it was, I recall being vaguelly intrigued thinking, cool, I wonder how they do that.

My 1st ever webpage used the <marquee> tag! It slid a photo of a Ford Mondeo across the screen!Ha, I had this nutty idea that I was gonna create a raffle or draw of some kind and enthuse everybody to enter some ‘win a car’ competition. I never did put it on line but it did get me thinking about how people would find it and that search engines would probably be a tool they’d use. Here is a funny thought. I once thought that when you entered a search term into a search box, the search engine then went and ‘looked’ at all the computers in the world and found pages to give back, ha! I guess I wasn’t so far off, they kinda do that, only that they’ve already been out into the web and grabbed the data beforehand.

Ok, enough of my waffle, on to the meat and potatoes.

The project

In this part we will look at discussing the base concept, creating a database, creating the tables, discussing the data, inserting the data into our database.

Ok, having briefly introduced what this is about in the title of the page. Its time to be a little more specific.We are going to build a hotel brochure website from the ground up. We will discuss the creation of the site from A to Z . From initial site set up through to getting indexed in the search engines. During this process we will look at issues facing site owners and offer up good examples for avoiding the pitfalls. We wil look at adding value to affiliate feeds and offer up solutions to the many mistakes and problems that site owners encounter. Some of the areas we will look at will include.

  • Planning issues
  • Getting started
  • Obtaining and working with a dataset
  • Creating a database
  • Using Mysql and PHP to output content
  • Good site architecture and design
  • Website script security
  • Getting indexed in the search engines
  • Promoting your website
  • Common pitfalls to avoid

Planning issues

Whilst this isn’t a typical real world project its useful nonetheless to look briefly at what we are looking to do. Any good website serves a purpose, in our case we are going to build a hotel brochure website we are going to assume that none exist already, we are going to act like our pages are the only ones in the world and that search engines and users alike are going to love our content and gobble it all up. Our website will give its users what they need in a simple, easy to use way. It will be easy to navigate, quick to load and on topic to its theme. Ordinarily we would all sit around a table in a huddle and discuss our aims and objectives. We would look at our market, look at our competitors, aim towards building a niche with a long term view of steady user growth by way of new sign ups and traffic. Monetisation would be a key factor too. luckily for us, none of these are pressing issues for this project as its purely educational.

Getting started

Our first stop is to grab a dataset. There are two approaches we could take. We could start from scratch. Go out and canvaas lots of hotels and invite them to join our website by sending them our details, or a quicker supplemental route would be to go to a big provider like Venere or Superhotels, or IAN or even individual big hotel chains like Holiday Inn and sign up for one of their affiliate programs.

Most Ive looked at will provide you the data as a direct download. You can choose which fields you want to use, which areas such as countries or regions, you can also choose which informational data you’d like to show too. The data can usually be saved to your computer as a text file, delimited, by tab, comma, or any other delimiter of your choosing which can then be uploaded to a database. Some will even provide for the data to be obtained as an sql file which can then be directly ran through an sql interpreter and inputted direct tot he database. In this instance we will be using a Mysql database to store our data, and will be using PhpMyAdmin as the interface for inputting our data.

Create the database

Most hosts will offer you the facility to use a GUI to create the database. Plesk is one popular GUI as is CPanel. There are others too. Sometimes you might have to request your host to create one for you.Most good hosts provide the tools for you to do so.

If push comes to shove you can use php to create one for you using the mysql_create_db() function, something like.

<?php
$query = “CREATE DATABASE hotels”;
$result = mysql_query($query);
?>

For the purposes of this though, we are going to assume that youve created a database named ‘hotels’ and have opened your PhpMyAdmin interface and are ready to create the tables.

Create the tables

In this example Ive chosen to stick with a basic data set and have placed the data into two logically named tables. The first table Ive called ‘hotelcontent1’ and the second ‘hotelcontent2’

We will need to create all sorts of fields relative to what the hotel provides. Email address field (email), establishment name field (ename) , web address field (www), price field (price) and so on. The code below is the sql that we will use to create our tables.

CREATE TABLE `hotelcontent1` (

`hotelid` int(9) NOT NULL auto_increment,

`custid` int(9) default NULL,

`ename` varchar(50) NOT NULL default '0',

`phone` varchar(60) NOT NULL default '0',

`www` varchar(250) NOT NULL default '0',

`email` varchar(160) NOT NULL default '0',

`etype` varchar(20) NOT NULL default '0',

`price` varchar(10) NOT NULL default '0',

`currency` varchar(10) NOT NULL default '0',

`rating` char(2) NOT NULL default '0',

`numrooms` varchar(4) NOT NULL default '0',

`trainstation` varchar(50) NOT NULL default '0',

`tsdist` varchar(4) NOT NULL default '0',

`airport` varchar(75) NOT NULL default '0',

`adist` char(2) NOT NULL default '0',

`photourl` varchar(250) NOT NULL default '0',

`description` text NOT NULL,

`visa` char(1) NOT NULL default '0',

`mastercard` char(1) NOT NULL default '0',

`diners` char(1) NOT NULL default '0',

`amex` char(1) NOT NULL default '0',

`lifts` char(1) NOT NULL default '0',

`restaurant` char(1) NOT NULL default '0',

`bar` char(1) NOT NULL default '0',

`disabled` char(1) NOT NULL default '0',

`parking` char(1) NOT NULL default '0',

`childrates` char(1) NOT NULL default '0',

`tv` char(1) NOT NULL default '0',

`ensuite` char(1) NOT NULL default '0',

`fax` char(1) NOT NULL default '0',

`computer` char(1) NOT NULL default '0',

`pets` char(1) NOT NULL default '0',

`nonsmoking` char(1) NOT NULL default '0',

`roomservice` char(1) NOT NULL default '0',

`breakfast` char(1) default '0',

`childcare` char(1) NOT NULL default '0',

`attractions` text NOT NULL,

`testimonials` text NOT NULL,

PRIMARY KEY  (`hotelid`),

UNIQUE KEY `hotelid` (`hotelid`)

) TYPE=MyISAM AUTO_INCREMENT=0 ;
CREATE TABLE `hotelcontent2` (

`custid` int(9) NOT NULL auto_increment,

`name` varchar(50) default NULL,

`address1` varchar(50) default NULL,

`address2` varchar(50) default NULL,

`town` varchar(100) default NULL,

`county` varchar(100) default '0',

`region` varchar(50) default 'no address recorded',

`postcode` varchar(12) NOT NULL default '0',

`country` varchar(30) NOT NULL default '0',

`custype` char(2) NOT NULL default '0',

`date` date default NULL,

`ipadd` varchar(30) default '000000000',

`username` varchar(100) NOT NULL default 'user',

`password` varchar(28) NOT NULL default 'pass',

`renewal` varchar(20) NOT NULL default '0000-00-00',

PRIMARY KEY  (`custid`),

KEY `town` (`town`),

KEY `county` (`county`),

KEY `region` (`region`),

KEY `country` (`country`)

) TYPE=MyISAM AUTO_INCREMENT=0 ;

The data

One of the great features that mysql has is that it allows you to insert text files straight in to a table. Provided they are properley formatted, and you specify the correct delimiter used, then it will insert the data into the fields you specify

In this \t or tab delimited example for our ‘hotelcontent1’ table, we would upload it using PhpMyAdmins ‘import’ feature. We would specify csv as the option and enter \t as the delimiter of choice. A sample row of such data might look a little like this;

A typical line from a tab delimited file

"1" "1" "The supergroovynicehotel" "0044(0)*********" "www.supergroovynicehotelofchoice.com"
"info@supergroovynicehotelofchoice.com" "Hotel" "45" "pounds" "3" "32" "Aberdeen"
"5" "Aberdeen" "10" "http://www.supergroovynicehotelofchoice.com/Images/
supergroovynicehotelofchoice.jpg" "The supergroovynicehotelofchoice Hotel is now
under the new management of Rob and Delia Blogs. blah blah blah. " "y" "y" "y" "N" "y" "y" "y" "y" "N" "N" "y" "y" "y" "N" "N" "y" "N" "y" "N" "N"

Each “” enclosed piece of data and corresponding gap (tab) specifies that its to be treated as a seperate field. Multiple insertions would be picked up by way of a \n or new line within the interpreter.

Note: Full sample files will be provided at the end of the piece so don’t worry too much about where the data is at the moment, it’ll be there at the end. 🙂

An alternative way of getting our data into our database, would be via use of an actual .sql file containing the full insert instructions a typical line within would read something like this;

A typical line from an sql file
INSERT INTO `hotelcontent1` VALUES (1, 1, 'The supergroovynicehotel', '0044(0)**********', 'www.supergroovynicehotelofchoice.com',
'info@supergroovynicehotelofchoice.com', 'Hotel', '45', 'pounds', '3"', '32','Aberdeen', '5',
'Aberdeen', '10','http://www.supergroovynicehotelofchoice.com/
Images/supergroovynicehotelofchoice.jpg', 'The supergroovynicehotelofchoice
Hotel is now under the new management of Rob and Delia Blogs. blah blah blah.', 'y', 'y', 'y', 'N', 'y', 'y', 'y', 'y', 'N', 'N', 'y', 'y', 'y', 'N', 'N', 'y', 'N', 'y', 'N', 'N', '');

The outcomes of either method is the same, the route to which all depends on the tools, knowledge and requirements you have to hand.

Some data sets can be very clunky to work with and require all sorts of processing and preparation before use. In the past I’ve used combinations of excel, odbc, and phpmyadmin locally, to prepare my files for the live database. These have been especially useful where the datasets provided were particularly large or in need of adjustment to fit my purposes.

Ok, so having stored our sample data in our database and tables, we are a step nearer the process of retrieving and outputting it to our webpage. In part 2 I’ll be showing you how to connect to the database using PHP choosing a good page template and discussing a few site architecture and design issues.

3 Responses

  1. […] Continued here Filed under: search   |   […]

  2. Super post Rob. You really tackled a big one here. When I saw the title come through my RSS reader, I said I gotta see this! Where were you a few years ago when I was wading through the whole PHP/MySql stuff? 🙂

    Looking forward to the next part.

  3. Cheers, Ive just finished part 2, just in the process of tidying it up and making it readable and as non confusing as possible! Not an easy thing to do for a topic that could have so many strands, hell – if helps someone somewhere, then its all good 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: