How to Build Your Own Database Driven Website

Difficulty: Moderately Challenging

Instructions

Things You'll Need:

Text editor like SourceEdit Document uploading software like FileZilla Space on a Server with PHP and MySQL installed A working knowledge of PHP, XHTML, MySQL The ability to create a MySQL table on a server

Planning

Step 1

Decide what type of content to present through your website. A virtual art gallery database-driven website will require a different structure than a website selling e-books.
Step 2

Create a structure for your website. Draw a square in the center of a piece of paper representing your home page and, using pencil lines, connect this to other squares or circles representing the other pages of your website.
Step 3

Divide each circle or square that connects to your home page into a section. Each section needs a folder for headers, footers, images, and external style sheets.
Step 4

Draw out the design of each section, making sure that there is some similarity amongst the design of each section. For example, place the header in the same position on each section, or design a menu system that is consistent throughout each section.
Step 5

Decide on two or three colors that identify you or what you want to sell. These colors will help unify your entire design and help people associate your identity with those colors. People are visual creatures and visual elements are just as important as intellectual elements.

Build the Infrastructure

Step 1

Create a series of database tables in MySQL to hold your information. Each table will be unique to what you are presenting to your audience, but all tables require and id column. For example, if I have a blog website, I would set up the MySQL table as follows:
Column One -- blog_id
Column Two -- title
Column Three -- article
Column Four -- date
Column Five -- keywords
Column Six -- photo
Step 2

Assign "blog_id" as the primary key, establish the column as an "INT" or integer column type, set the column to "auto_increment", NOT NULL, and Primary Key. When data is entered into the column, the next record number in sequence will be automatically assigned.
Step 3

Establish "title" as a "TEXT" field. Text fields can hold thousands of characters of data. A title of an article does not need thousands of characters, but don't limit yourself to a finite number of characters, otherwise, you may spend much of your time adjusting the database to compensate for extra characters.
Step 4

Establish "article" as a "TEXT" field.
Step 5

Establish "date" as either a "TEXT" field, if you know that you will be recording non-numerical date values, such as the month or day of the week. Otherwise, establish "date" as a "DATE" column, which requires the format "yyyy-mm-dd".
Step 6

Establish "keywords" as a "TEXT" field in order to accommodate as many keywords to fit your needs.
Step 7

Establish "photo" as a "TEXT" field, which will contain the file name of the image included in the article.
Step 8

Repeat the process for all sections of your website, creating tables that will contain the content of your website.

Administration

Step 1

Build a series of forms to enter data into the MySQL tables. Using our blog example above, type the following code to create a standard XHTML webpage and save it as administration.php:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Administration</title>
</head>
<body>
</body>
</html>
Step 2

Type the following form between the beginning <body> and ending </body> tags:
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
<input type="hidden" name="blog_id" />
Title: 
<input type="text" name="title" size="60" /> 
Article: 
<textarea name="article" cols="80" rows="10"></textarea> 
Keywords: 
<input type="text" name="keywords" size="60" /> 
Picture: 
<input type="hidden" name="MAX_FILE_SIZE" value="64000000" />
<input type="file" name="photo" size="80" /> 
<input type="submit" name="submit" value="Add Blog" />
</form>
Explanation:
Set the "action" attribute to "<?php $_SERVER['PHP_SELF']; ?>", directing the server to follow the instructions written in PHP at the top of the document. Add the "enctype" attribute with the value "multipart/form-data" in order to upload the file to the server and process the rest of the form data. Set the "method" attribute to "post". Create text boxes for the article's title and keywords. Create a "textarea" form element to contain the article. Create a "file" form element to upload a photo to the server. Create two hidden form elements -- one as a placeholder for the "blog_id" and the other to regulate the size of the file to 64MB.
Step 3

Validate all fields within the form to ensure that all fields have an entry. Establish contingencies for each blank field. Decide if all articles require a photo and make a contingency for a blank field. Type the PHP validation code at the very top of the document above the DOCTYPE statement, as shown:
<?php
if (isset($_POST['submit'])) {
if (empty($_POST['title'])) {
$title = FALSE;
echo "Please enter the title!";
} else {
$title = $_POST['title'];
echo "" . $title . " has been successfully entered into the database.";
}
if (empty($_POST['article'])) {
$article = FALSE;
echo "Please type an article!";
} else {
$article = $_POST['article'];
echo "The article has been successfully submitted!";
}
$date = date('Y-m-d');
if (empty($_POST['keywords'])) {
$keywords = FALSE;
echo "Please enter keywords!";
} else {
$keywords = $_POST['keywords'];
echo "The following keywords have been entered: " . $keywords . ".";
}
if (is_uploaded_file ($_FILES['photo']['tmp_name'])) {
if (move_uploaded_file($_FILES['photo']['tmp_name'], "images/{$_FILES['photo']['name']}")) {
$photo = $_FILES['photo']['name'];
echo "" . $photo . " has been uploaded! ";
} else { // Couldn't move the file over.
echo "The picture could not be moved. ";
}
} else {
}
}
?>
Step 4

Add an "if/else" statement to make specific areas required before the information is entered into the database. If the form is filled out properly, connect to the database and insert all of the data into the proper columns. If the form is not filled out properly, display an error message.
if ($title != FALSE && $article != FALSE && $keywords != FALSE) {
// Connect to the database
$dbh=mysql_connect ("SERVERNAME", "USERNAME", "PASSWORD")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("DATABASENAME");
$query = "INSERT INTO tablename (blog_id, title, article, date, keywords, photo) VALUES
('$blog_id', '$title', '$article', '$date', '$keywords', '$photo')" or die(mysql_error());
$result = @mysql_query($query);
if ($result) {
echo = "The query was successful.";
} else {
echo "Query FAILED, see above.";
echo $query;
}
} else {
echo "Errors have occurred see above.";
}
Step 5

Save your file. Upload it to your server. Enter your data. Repeat for each section.
Step 6

Create another MySQL table to hold the username and password of those granted access to the administration section:
CREATE TABLE `members`.`admin` (
`user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` TEXT NOT NULL ,
`password` TEXT NOT NULL
) ENGINE = MYISAM
Step 7

Add a login page to your administration section, along with validation, as shown:
<?php
$dbh=mysql_connect ("SERVERNAME", "USERNAME", "PASSWORD") or die
('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("members");
if (isset($_POST['submit'])) {
if (empty($_POST['username'])) {
$username = FALSE;
echo "Please enter your username!";
} else {
$username = $_POST['username'];
}
if (empty($_POST['password'])) {
$password = FALSE;
echo "Please enter your password!";
} else {
$password = $_POST['password'];
}
if ($username != FALSE && $password != FALSE) {
$query = "SELECT user_id FROM admin WHERE username='$username' AND password='$password'";
$result = @mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
if ($row) {

$_SESSION['user_id'] = $row[0];
echo "Go to Member Directory Pages <a href='administration.php'>here</a>.";

} else {
echo "Login Attempt has FAILED, please try again!";
mysql_close();
}
echo "You have successfully logged in!";
} else {
echo "You could not be logged in at this time. See above error(s).";
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title></title>
</head>
<body>
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
<table border="1">
<tr>
<td>Username:</td>
<td><input type="text" name="username" size="60" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="text" name="password" size="60" /></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Login" /></td>
</tr>
</table>
</form>
</body>
</html>
Explanation:
The login page checks to see if the username and password fields have been filled out. If they have, it will check to see if those usernames and passwords are in the database. If not, it will display an error code. Upon a successful login, the page will establish a session and allow the user to access any page containing the function "session_start()" without having to log in again.
Step 8

Type the following code after the opening tag for PHP on administration.php to protect it from unauthorized access:
session_start();
if (!isset($_SESSION['user_id'])) {
header("Location: http://www.yoursite.com/login.php");
exit();
} else {
echo "Welcome to the Member Directory!";
}
Explanation:
This page will check to see if a session has been created, (in step 6) if it has not been created, the page will redirect to the login page. If the session has been established, the page will welcome the member

Retrieve the Data

Step 1

Build your external website structure according to the designs in section one. Create all of the images and logos needed in your website to establish your identity.
Step 2

Connect to the database with the following script, replacing your data with the capital letters:
$dbh=mysql_connect ("SERVERNAME", "USERNAME", "PASSWORD")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("DATABASENAME");
Step 3

Type the "SELECT" query to retrieve the data out of the database:
$getblog = mysql_query("SELECT * FROM tablename ORDER BY date ASC");
while($r=mysql_fetch_array($getblog)){
extract($r);
}
Step 4

Type the column names as variables between the "extract($r);" line and the right curly brace }.
extract($r);
echo $title;
echo $date;
echo $article;
}
Step 5

Surround the variables with HTML tags to enhance their presentation. Make sure that the HTML is surrounded by double quotes and connected to the PHP variables with a space, a period, and a space, as shown:
extract($r);
echo "<h2>" . $title . "</h2>";
echo "" . $date . " ";
echo $article;
}
Follow the same process for all of your public webpages.

How to Build Your Own Database Driven Website

Things You'll Need:

Planning

Build the Infrastructure

Administration

Retrieve the Data

References

Post a Comment

Related Ads