How to Create a Web Database

Web databases make sites dynamic.

Using a web database makes updating and maintaining a website more efficient. You will also need to use a server side programming language such as PHP or ASP to connect to your database, and use the resulting data to build your site for visitors to view. Dynamic websites have considerable advantages over static sites that do not use databases, and they are ideally suited to many sites, including those marketing or selling products. The code in this guide demonstrates the use of MySQL and PHP.

Difficulty:

Moderate

Instructions

1. Acquire a web-hosting package that includes database provision. Depending on your package, you may be able to administer your web databases using a program such as phpMyAdmin. In this case, setting up and maintaining a web database will be significantly easier, as the user interfaces make common tasks straightforward. Whatever interface you are using for your database, you will likely need to learn at least a small amount of SQL to handle your data.

2. Work out how you want to model the data for your site. The structure of your database will naturally depend on the purpose of your website. For example, if your site is selling products, you may have tables in the database to store the details of each product available, as well as information relating to stock levels, purchases and transactions. To begin with, it might be best to try creating a simple test database to get used to the technologies. Many tools and techniques can help data modeling, such as Entity Relationship diagrams, through which you explore the objects within your data, and the relationships between them.

3. Create your database, defining the structure either in SQL or using a web interface such as phpMyAdmin. Using your web host control panel, create a new database with an appropriate name. Create and define whatever tables you need within the database by choosing a name for each table, and at least one field. For each field in your database tables, choose a name and data type (e.g., text or numerical types), as well as any additional requirements. A sample excerpt of SQL for creating a table within a database might be:

   CREATE TABLE IF NOT EXISTS 'product' (
   'ID' smallint(6) NOT NULL auto_increment,
   'name' varchar(30) NOT NULL,
   'description' varchar(10) NOT NULL,
   'price' smallint(6) NOT NULL,
   PRIMARY KEY ('ID')
   );

   In this code, "NOT NULL" indicates that any product entered must have a value for the field in question. The data types are specified using "smallint" and "varchar" and the range of values indicated within the brackets. The Primary Key of your database is the field that every table must contain to make each of its entries unique. In this case, the Primary Key is simply an "ID" number assigned automatically each time a new entry is placed within the table.

4. Enter some data. If your database contains more than one table and you want the data within these to be linked, you can use Foreign Key relationships. You should model these when you define the structure of your database. For example, if your site had the ability to accept reviews from customers on particular products, you could store the reviews submitted in a table linked to the "product" table. Your "review" table could contain the text and any other review information, plus a field holding the "ID" number of the product that is being reviewed, that is, it would contain the "ID" number from the "product" table as a Foreign Key.

5. Connect your database to your website using a server side language such as PHP or ASP. A connection script in PHP might be:

   <?php
   mysql_connect("localhost", "username", "password");
   mysql_select_db("database_name");
   ?>

   Query the database to get the data you want to display on the site pages, for example:

   <?php
   $query="select * from product";
   $result=mysql_query($query);
   while($row=mysql_fetch_array($result))
   {
   $name=$row['name'];
   $desc=$row['description'];
   $price=$row['price'];
   echo "<p>".$name."</p>";
   echo "<p>".$desc."</p>";
   echo "<p>".$price."</p>";
   }
   ?>

   This script simply gets all of the data from the "product" table and outputs the details of each entry in basic HTML.

Tips & Warnings

• Make use of the many Internet resources for SQL and your chosen server side scripting language, as you will find that most of the queries you are using have been done many times before.

• Try to make your database structure efficient by not storing data more than once. The biggest advantage to using a web database is that you only have to enter or change the data in one place, so make the best use of this and save yourself time in the process.

References

• W3 Schools: SQL Introduction

Resources

• PHP: Hypertext Preprocessor
• MySQL: The World's Most Popular Open Source Database
• phpMyAdmin: About phpMyAdmin