Part 2 - Setting Up The Database


Before you actually start building your database scripts, you must have a database to place information into and read it from. In this section we will use PHP to create a database in MySQL and prepare it for the data. We will thus begin to create the contacts management database.

We can create and manage the database in a varity of ways, use the mysql terminal client, using PHPMyAdmin , or even using PHP scripts!

Before you can do anything with your database, you must create the table.

Creating a table in PHPMyAdmin is simple, just type the name, select the number of fields and click the button. You will then be taken to a setup screen where you must create the fields for the database. You can also use a PHP script to create your database, where the whole creation and setup will be done in one command (below).


There are a wide variety of fields and attributes available in MySQL and we will cover a few of these here:

Field Type Description
TINYINT Small Integer Number
SMALLINT Small Integer Number
MEDIUMINT Integer Number
INT Integer Number
VARCHAR Text (maximum 256 characters)
TEXT Tex


These are just a few of the fields which are available. A search on the internet will provide lists of all the field types allowed.

To create a table in PHP is slightly more difficult than with MySQL. It takes the following format:

CREATE TABLE tablename {

Fields

}

The fields are defined as follows:

fieldname type(length) extra info,

The final field entered should not have a comma after it.

We will see a full example of using these later in the section.

The contacts database will contain all the conact information for the people you enter and the information will be able to be edited and viewed on the internet. The following fields will be used in the database:

Name

Type

Length

Description

id INT 6 A unique identifier for each record
first VARCHAR 15 The person's first name
last VARCHAR 15 The person's last name
phone VARCHAR 20 The person's phone number
mobile VARCHAR 20 The person's mobile number
fax VARCHAR 20 The person's fax number
email VARCHAR 30 The person's e-mail address
web VARCHAR 30 The person's web address



You may be wondering why we have used VARCHAR fields for the phone/fax numbers even though they are made up of digits. You could use INT fields but if you use VARCHAR it will allow dashes and spaces in the number, as well as textual numbers (like 1800-COMPANY) and as we will not be initiating phone calls from the web it is not a problem.

There is one other thing you should be aware of in this database. The id field will also be set as PRIMARY, INDEX, UNIQUE and will be set to auto_increment (found under Extra in PH PMyAdmin ). The reason for this is that this will be the field identifier (primary and index) and so must be unique. The auto increment setting means that whenever you add a record, as long as you don't specify an id, it will be given the next number.

The following code should be used to create this table in PHP. Note that is is just some PHP code, with SQL code embedded!

<?
$user="username";
$password="password";
$database="database";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
mysql_close();
?>

You will need to change this code to use your database name (in your case your username), your MySQL username and password in the appropriate positions on the first three lines above.


In part 3 we will explore fully how to connect to a database using PHP and I will show you how to add information to your new database.

Part 1 - Introduction
Part 2 - Setting Up The Database
Part 3 - Inserting Information
Part 4 - Displaying Data
Part 5 - More Outputs
Part 6 - Single Records & Error Trapping
Part 7 - Updating & Deleting
Part 8 - Finishing The Script

<<Part 1 Part 3>>

Topic revision: r1 - 2011-11-11 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback