Chapter 14:
Working With Databases

Definitions:

Database - a collection of data items

Database Management System (DBMS) - a computer program that manages one or more databases.

Relational Database Management System (RDBMS) - a computer program that manages one or more databases and allows you to get groups of data items from the whole database in which the items are related in some way

Column - a name for all the data items of the same exact kind. One column might be email addresses (of a text type) and another might be IDs (could be a number)

Row - a set of data items containing one from each column. Sometimes a particular column in a particular row contains 0 or NULL (no value at all; not the same as 0 or "")

Table - a set of one or more Rows, where the Columns in each Row all hold the same kind of item in exactly the same order as every other Row in the Table

Database types

Two broad categories of databases are flat-file and relational. Flat-file databases are usually made of ASCII text files with one entry per line. They are easy to program but they have no special methods of indexing the data. For a very small database they may be acceptable.

The problem with flat-file databases is that they don't "scale" well. That means that the larger the database, the longer it takes to locate something specific. If you're just reading big chunks of the data in the same way over and over, this may not be a bad way to go. But for general-purpose work, especially with a lot of data, stick with a relational database.

Entries in a flat-file database may contain more than one data item. Separate the "fields" in the entry with a character such as a colon or a pipe. The choice of field separator (also called a delimiter) is up to you. For best results pick a character that won't occur in any field. PHP can take this entry as a string and split it up into separate fields. Those fields would then be stored as an array. Use the explode() function to do it. It's possible to make one field behave as an index and do some fairly decent searching.

Relational databases have one or more index tables that make it easy for the code to zero in on exactly what you want to find. Searching is fast because your code doesn't have to read everything in the file until it gets to the item(s) you're seeking. This is all managed by the database engine and the indexes (also called hash tables). MySQL is a popular DBMS of this type. It's fast, low-cost (or free) and it's available online. You can download MySQL from:

http://www.mysql.com/

Requirements

You'll need a DBMS for anything beyond a very small database application. There are several of these to pick from: SyBase, Oracle, Microsoft SQL Server, MySQL and others. You'll need a way to send them commands they can process. They have different code, features and behavior but they speak a common standardized language called Structured Query Language (SQL). This common language makes it easy for you to work with them in PHP.

Check the information on your Web host to see if you have access to a DBMS. If you do, be sure to find out which one is available. This info should be available from technical support in one way or another. Often the type of database server you can use is listed with the feature set for your hosting package.

Creating a database

Before you can do anything with a database, you must create one. Relax: it's very simple. There are two methods for creating a database. The first one is really easy. Let's look at that process step by step:

If you haven't already created a database on your web server, you must do that now. If your web host provides cPanel, just log into the control panel and click on the icon or link marked MySQL databases.

Notes: A database can contain as many tables as you want it to have. These tables may or not not have any relationship to each other. That's part of what makes a database so flexible. Your host may only allow you to create one database - or it may allow you to have a number of them. If you can only have one, you can still add tables to it and not cause any problem with the application that created it.

Also, if any databases have already been created, cPanel will show you a command that makes a connection to the database. This command will be shown in both PHP and Perl. You can copy and paste the connection command into your script. The only thing you'll need to edit is your password. The other bits of information will already be in the command but your password will not be shown - for security reasons. However, the instructions in this chapter for creating your table must still be followed.

If you have any databases already created, you'll see them at the top of this screen. (Some programs such as forum controllers automatically create a database during installation.) If not, you'll have to create one. Just scroll down to where you see a text box labeled "Db:" followed by a small button marked "Add Db". Type in a name. Click the "Add Db" button.

The screen will refresh and your new database will be listed near the top of the page. Next you have to create at least one user for this new database. Scroll down to where it says "Users:" There's a small form there. If you already have a database and a user with access to it, you can skip the next step...

Fill in a username and a password in the two text boxes. WRITE DOWN YOUR USERNAME AND PASSWORD! Click the Add User button. Now you've created a user but you have to give this user access to your database. Scroll up to where you see two pull-down menus and a bunch of checkboxes.

Copyright © 2004 Steve Humphrey