How to Create and Understand a Simple Database-Driven Website
Posted by: James Lewitzke in Coding, Crazy IdeasAlright, this had been driving me nuts for months, and all the online tutorials and guides sucked ass. And after hours through trial and error coding, I finally discovered the correct portions and amount of code to use.
So basically, I’m going to explain here exactly what you need to do to create a simple PHP / MySQL backed DB-driven website that draws all of the data straight from the database.
An example URL of a page we’ll create could look like this:
http://www.site.com/index.php?id=1
1) Prepare the Front-End Design and Content
First, you’ll want to make sure that you have the basic design of the site coded. You may use whatever CSS styling you want to, it won’t make any difference on the backend. (One index.html file is fine, but be sure to rename it as index.php once it goes online.) Also all the information for our website needs to be properly entered into the database. Using a tool such as phpmyadmin will work great to enter the data, however you can use SQL if you wish. For the purpose of keeping this tutorial quick, I’m going to assume that you already know how to do this.
To keep things extremely simple, we’ll only need to create one table, I decided to name mine “page”. Also include a minimum of two fields (I’m using three) and two pages to understand what’s going on. Below is an example of a DB table that you could use:
| ID | Title | Content |
|---|---|---|
| 1 | Home | Welcome to the Homepage. |
| 2 | About | A brief description about the site. |
You can use whatever data you wish, however most sites have these pages and that’s what I have decided to use. Also be sure to set the ID to the primary key, so it’ll automatically update later when you decide to add more webpages.
2) Connect to the DB and Format the Website Structure
Alright, next step. After we get all that out of the way, we now need to interact with our database table we just created, so we need to use PHP to configure a connection. You can include this in a few different ways, what I did was create a config.php file and wrote a require_once function to include the data from the PHP file onto my main index.php page.
There’s plenty of other sites that go into more depth on this, but I’ll give you an example here. For the config.php file:
<?php
$username="DB_USERNAME";
$password="DB_PASSWORD";
$database="DB_NAME";
$url="localhost";
$link = mysql_connect($url,$username,$password);
mysql_select_db($database) or die("Unable to select database");
mysql_close();
?>
And as for the index.php page, I know this probably could have been more securely coded (aka placing the DB retrieval info before the DOCTYPE), but for simplicity’s sake, here’s an example file:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<?php require_once ("config.php"); ?>
<?php require_once ("functions.php"); ?>
<title>
</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<?php require_once ("header.php"); ?>
<?php require_once ("content.php"); ?>
<?php require_once ("footer.php"); ?>
</body>
</html>
Note this also includes other files, like the header, function, and footer documents, but they aren’t required to have, they’re just apart of the design structure.
3) Write the PHP Script
Now all we need to do is get the database to actually read the different DB rows as separate webpages. To do this we will be using the PHP superglobal associative array $_GET, and a mysql_fetch function. If you caught the additional function in the above index.php file, we also included a file called content.php. This is where the script will be stored. First, we need to write some PHP variables:
$id = $_GET['id'];
$query = "SELECT * FROM Page WHERE id='$id'";
$results = mysql_query($query, $link) or die(mysql_error());
If you noticed, we assigned the $id variable to the PHP superglobal $_GET, where we want it to “get” the ID from each column and return all the information in that row based upon it’s ID, and what we tell it to later, which you can tell by the SQL query I wrote for the $query variable. The $results variable basically just combines everything we want it to do and kill it in case there’s an error, (You can see what we’re getting from the $link variable in the config.php file).
The $_GET superglobal is also useful for passing variables onto the end of a URL string. Let me try to explain. Since the script is asking for the ID from the page table we created earlier, we can now apprehend a result to the end of the URL via a question mark (?). For example, say we want to fetch the data contained in the first row, we can call upon the ID to fetch us everything we ask for (which is what we’re going to write next). I know, it sounds confusing, but with more practice, you’ll eventually get the hang of it.
Now to actually display that data on the webpage, we need to write a while loop using a mysql_fetch_array PHP function:
while($row = mysql_fetch_array($results))
{
echo "<div id='title'>" . $row['title'] . "</div> <div id='content'>" . $row['content'];
echo "</div><br />";
}
?>
The while loop basically tells us that “while” we are fetching the DB row info, here’s what we’re doing with it. In this instance, just echoing the data onto the page. Since the first thing we are asking for is the data contained within the title column, the PHP will display this for us via the $row variable, because it “fetches” this specific instance of the array (whatever we ask for when we type the URL address in).
You could also base the URL off of other portions of the DB table if you wanted to, like “title” for example. You’d just have to get the title data via the $_GET superglobal and ask for it when you assign the value to an array via a variable (aka $title = $_GET[’title’];) along with altering the other variables appropriately. You can name your variables whatever you wish, I just used $title for convenience.
In addition to the PHP, I’ve also echoed some div elements which help style the content appropriately through the stylesheet (they’re not a portion of the tutorial, but you can use whatever CSS properties you desire to apply).
Now after understanding the technical aspects behind the PHP code, we just need to put it all together. Save the following code as content.php:
<?php
$id = $_GET['id'];
$query = "SELECT * FROM Entry_Page WHERE id='$id'";
$results = mysql_query($query, $link) or die(mysql_error());
while($row = mysql_fetch_array($results))
{
echo "<div id='title'>" . $row['title'] . "</div> <div id='content'>" . $row['content'];
echo "</div><br />";
}
?>
4) Place the Finishing Touches on the Site
And now upload everything to the server, all in the same folder: index.php, config.php, and content.php (also header.php, footer.php, and style.css if you bothered to edit / create them yourself, so the site doesn’t look like crap).
Now you can access the different pages you created by calling upon the MySQL database via the various URL variables, in essence “creating the webpages based upon whatever information is stored within the field’s (ID’s) row”.
And there you have it, your very first Database-Driven Website!
No comments yet.