A Short Course in MySQL
1. Introduction to MySQL
2. MySQL and PHP
3. Database Queries
4. The Contacts Database – HTML, PHP and MySQL code
1. Introduction to MySQL
1.1 Background
MySQL is an open-source relational database management system that is renowned as being
fast, reliable and easy to use. MySQL is most often used in combination with PHP, to provide
data management facilities to Web applications.
This Short Course introduces MySQL through a PHP application that implements a Web-enabled
database of personal contacts. Most MySQL statements are expressed in standard SQL
(Structured Query Language)
Unusually, there are no tasks associated with this Short Course – rather, the completed
application is presented in its entirety at the end of this document.
Note: This is not designed to be an exhaustive treatment of the capabilities of MySQL.
This document should be treated as a primer – enabling you to connect to a databases
server and to construct and manipulate an initial Web-enabled database. A full study of
MySQL and its capabilities can be obtained from the on-line MySQL manual at
www.mysql.com.
1.2 MySQL Tables
Relational databases organise information in tables. MySQL allows us to specify the structure of
tables through the CREATE TABLE command. There are also a number of graphical interfaces
available for the creation and manipulation of MySQL TABLEs, and you should refer to the
supporting documentation of your local installation for the availability of these.
Figure 1 presents the creation of the TABLE for the personal contacts database.
The contacts table consists of 3 fields
�name The name of the contact, defined as a variable character (VARCHAR)
field of up to 50 characters
�phoneNumber The telephone number of the contact, defined as a variable character
field of up to 15 characters
�numOfOrders The number of orders that this contact has made, defined as an integer.
CREATE TABLE contacts (
name VARCHAR(50),
phoneNumber VARCHAR(15),
numOfOrders INTEGER
);
Fig 1. The Contacts Database
Note how commas separate the fields in the table definition, and how the entire field list is
enclosed in parenthesis. Note also how a semicolon terminates the command – this is the case
for each MySQL command entered via a command line interface (although most of our work will
be through a PHP script interface).
MySQL makes available a wide range of field types. The most important of these are presented
in the following table.
INTEGER
Integer, 4-byte range
DECIMAL(x,y)
Floating point, x significant digits, y
digits after the decimal point
TINYINT
Integer1-byte range
SMALLINT
Integer, 2-byte range
MEDIUMINT
Integer, 3-byte range
BIGINT
Integer, 8-byte range
FLOAT
Floating point, 4-byte range
Numeric types
DOUBLE
Floating point, 8-byte range
DATE
Date value in ‘YYYY-MM-DD’ format
TIME
Time value in ‘HH:MM:SS’ format
Date & Time types
DATETIME
Date and Time values together, in
‘YYYY-MM-DD HH:MM:SS’ format
CHAR(x),
VARCHAR(x)
x characters, where 0>=x<=255
Text types
TEXT
Strings longer than 255 characters
NOTE You may prefer to use a graphical environment such as PhpMyAdmin to create
database tables, although you should still be aware of the underlying SQL code.
2. MySQL and PHP
2.1 Establishing connections
PHP provides a rich and easily used interface for establishing and managing connections with the
MySQL database engine. Figure 2 presents PHP code to create the database of Figure 1.
There are 2 steps to connecting to a mySQL database through PHP.
1. The mysql_connect() function takes 3 parameters: the machine on which the MySQL
server is running, the username to be used when connecting to the server, and any
password that must be used. If the MySQL server is running on the same machine as
the Web server (the more usual situation), then “localhost” is the appropriate entry for the
first parameter. The other parameters will depend on your own username and password
combination. (Note that the password parameter is only required if the database in
question requires it). The function returns a variable that is a handle to the database
server ($db, in this case)
2. The mysql_select_db() function selects a particular database on the machine
identified by mysql_connect(). The parameters for this function are the database to
which we are trying to connect, and the handle to the database server returned by step 1
above.
These steps are performed once only for each database-enabled PHP script – regardless of the
number of database operations contained n the script.
2.2 Sending MySQL commands
MySQL commands are issued to the database in the form of a text string, passed as a parameter
to the mysql_query() function. Figure 3 illustrates this function by issuing the commands that
create the database of Figure 1.
<?php
$db=mysql_connect(“localhost”, “userName”, “password”);
$mysql_select_db(“databaseName”, $db);
...
?>
Fig 2. Connecting to the MySQL Database with PHP
Again, 2 stages are involved.
1. The SQL statement (query) is stored in a text string. In this case, the query is the
“CREATE TABLE …” statement identified in Figure 1.
2. The SQL query is transmitted to the database server, where it is presented to the
identified database.
The personal contacts database is now created, and is available for manipulation in the following
sections.
<?php
...
$dbQuery="CREATE TABLE contacts (" .
" name VARCHAR(50), " .
" phoneNumber VARCHAR(15), " .
" numOfOrders INTEGER ) ";
$result=mysql_query($dbQuery, $db);
?>
Fig 3. Issuing MySQL commands
评论0