Free Webmaster Help Logo
Tutorials Articles Tools Images Directory Ask Newsletter
*
 

Tutorials
Cookies
ASP
Advanced HTML
Frames & Tables
.htaccess
Rounded Table Corners
Flash 5
FTP
Beginners
Javascript
PHP/MySQL
Site Promotion
Mobile Internet: WML/WAP
Server Side Includes (SSI)
HTML - The Basics
XHTML
Stylesheets
XML
PHP
*

125x125

Related Links
PHP Tutorial
Official PHP Homepage
Official MySQL Homepage
Full Code Of Example
PHP Builder
More PHP/MySQL Sites
*

 
Sitemap | Contact | Link To Us | Advertise
Report A Problem
Home : Tutorials : PHP/MySQL : Part 2

PHP/MySQL Tutorial
Part 2 - Setting Up The Database

Introduction

Before you actually start building your database scripts, you must have a database to place information into and read it from. In this section I will show you how to create a database in MySQL and prepare it for the data. I will also begin to show you how to create the contacts management database.

Database Construction

MySQL databases have a standard setup. They are made up of a database, in which is contained tables. Each of these tables is quite separate and can have different fields etc. even though it is part of one database. Each table contains records which are made up of fields.

Databases And Logins

The process of setting up a MySQL database varies from host to host, you will however end up with a database name, a user name and a password. This information will be required to log in to the database.

If you have PHPMyAdmin (or a similar program) installed you can just go to it to log in with your user name and password. If not you must do all your database administration using PHP scripts.

Creating A Table

Before you can do anything with your database, you must create a table. A table is a section of the database for storing related information. In a table you will set up the different fields which will be used in that table. Because of this construction, nearly all of a site's database needs can be satisfied using just one database.

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. If you are using a PHP script to create your database, the whole creation and setup will be done in one command.

Fields

There are a wide variety of fields and attributes available in MySQL and I will cover a few of these here:
Field TypeDescription
TINYINTSmall Integer Number
SMALLINTSmall Integer Number
MEDIUMINTInteger Number
INTInteger Number
VARCHARText (maximum 256 characters)
TEXTText


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.

Creating A Table With PHP

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.

I will give full an example of using these later in the section.

The Contacts Database

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
idINT6A unique identifier for each record
firstVARCHAR15The person's first name
lastVARCHAR15The person's last name
phoneVARCHAR20The person's phone number
mobileVARCHAR20The person's mobile number
faxVARCHAR20The person's fax number
emailVARCHAR30The person's e-mail address
webVARCHAR30The person's web address


You may be wondering why I have used VARCHAR fields for the phone/fax numbers even though they are made up of digits. You could use INT fields but I prefer to use VARCHAR as 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.

If you are using PHPMyAdmin or a management program you can now create this in a table called contacts.

Creating The Table In PHP

The following code should be used to create this table in PHP. Some of the code has not been covered yet but I will explain it fully in the next part.

<?
$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();
?>

Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above.

Part 3

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

© 1999 - 2001 FreeWebmasterHelp.com