In this tutorial, we will walk beginners through creating
their first simple online web application programmed in PHP and using a MySQL
database.
A web application is a phrase used to describe a website or
page that functions as an application: it takes user input, processes it, and
often stores it for later use.
Shopping carts, online e-mail sites, eBay and
dating/personals sites are just a few examples of web applications that are used
everyday on the internet.
One of the many uses of PHP is to store, retrieve, and
display simple text information from a database. In this tutorial, we are going
to create a simple online address book, which will store a first name, last
name, and e-mail address for each person we add.
This simple exercise will allow us to discover the basics of
using PHP to create and interact with a MySQL database, and at the end we will
have all the tools we need to learn how to create some more complex PHP/MySQL
solutions.
This tutorial assumes you have a basic knowledge of variables, objects, and
programming syntax.
Using PHP and MySQL Together: Creating a Simple Online Address Book
Part 1: Creating the MySQL structure
Creating the MySQL Tables
For this step , you will need the name of your database, your username and
password, and the location of your database.
We will write a short PHP script that does nothing but create our database
table. Each MySQL database can contain many different tables, or sets of
information.
First of all, create a blank text file and name it create.php.
Start the document out by putting in this text:
<html>
<body>
<?php
/* my first php script */
?>
</body>
</html>
If you were to put this page on a server with PHP and access it from your
browser, your output would be:
<html>
<body>
</body>
</html>
Anything between the php opening tag ( <?php ) and the closing tag ( ?> ) is
parsed as PHP code and not printed to the browser. To print out text from your
PHP code, you can use the functions print(), echo(), exit() and die(). 'Print'
and 'echo' are identical functions that will print any text or variables passed
to them directly to the browser. 'Die' and 'exit' send the text to the browser
and terminate the PHP execution.
PHP variables and arrays start with a dollar sign ($), and can be included
inside of a text string. For example, the following code would print out "My
name is Steven" to the viewer.
<html>
<body>
<?php
/* my first php script */
$myName = "Steven";
echo( "My name is $myName" );
?>
</body>
</html>
Or, another way to do the same thing as the code above:
<html>
<body>
<?php
$myName = "Steven";
?>
My name is <?php echo( $myName ) ?>
</body>
</html>
Both of these would print out the same HTML code to the user's browser:
<html>
<body>
My name is Steven
</body>
</html>
This is the greatest advantage of PHP. The PHP code can be placed anywhere in
a normal HTML document, which makes it easy to include dynamic content in any
page.
Creating the installation script:
<html>
<body>
<?php
// creation.php: installation script
// connect to the server
if( mysql_connect( 'localhost', 'username', 'password' ) )
echo( "Connected to database.<br>\n" );
else
die( "Error! Could not connect to server:
" . mysql_error() );
// select the database
if( mysql_select_db( 'database_name' ) )
echo( "Selected the database.<br>" );
else
die( "Error! Could not select the
database: " . mysql_error() );
Let's break this "installation" script into pieces:
// connect to the server
if( mysql_connect( 'localhost', 'username', 'password' ) )
echo( "Connected to database.<br>" );
else
die( "Error! Could not connect to server:
" . mysql_error() );
Here we use the PHP function
mysql_connect( string server, string username, string password
). This creates a MySQL connection to the database server. This
function returns true if the connection is successful, or
false if it wasn't. So, putting the function in an if / else
statement allows us to print the success or failure to the browser. Putting the
HTML <br> tag at the end of the output will make it easier to read in the
browser
The PHP function
mysql_error() prints out the last MySQL error logged by this PHP
process. Printing out the string returned by mysql_error() will
be very helpful in debugging your scripts.
// select the database
if( mysql_select_db( 'database_name' ) )
echo( "Selected the database.<br>" );
else
die( "Error! Could not select the
database: " . mysql_error() );
The PHP function mysql_select_db( string database_name
) selects the named database on the MySQL server as the active
database. This will return false if the database does not
exist.
if( mysql_query( $create_query ) )
echo( "Table created successfully.<br>"
);
else
die( "Error! Could not create table: " .
mysql_error() );
Now the code above is the real meat of this script. The function
mysql_query( string query )
sends a command query to the MySQL database. MySQL syntax is meant to be easily
readable and intuitive.
In natural English, me might want to tell our script:
" Select rows from contacts where first name is Susan ".
In MySQL, this would be:
" SELECT * FROM `contacts` WHERE `firstName` = 'Susan' ".
Pretty simple. The query for creating a new table goes like this:
CREATE TABLE `table_name` ( `column1` data_type, `column2` data_type, `column3`
data_type ), with as many columns as you need.
About data types: the most commonly used data type in MySQL databases is
VARCHAR, or variable character, i.e. a string. In the syntax lastName
VARCHAR( 20 ), we are creating a column whose values are a string with
a maximum of 20 characters. Other common types include INT
(integer), FLOAT (decimal values) and BINARY ( binary data ).
Its a good idea to initially create VARCHAR with high maximum lengths, as
long as you could possibly need. You can always alter the table later and lower
the maximum value (for database efficiency), but it is best to initially plan
for large string lengths.
So, the MySQL query string in the code above (stored in the
$create_query variable), is telling the MySQL database to create a
table named `contacts` with columns lastName,
firstName, email, and id. The
lastName, firstName, and email columns are pretty self-explanatory, and are all
variable character strings. We have also added an "id" column, which is an INT
(integer number). This id will be used to unambiguously identify each entry into
the database.
A great feature of MySQL database is the AUTO_INCREMENT attribute. Any
integer column with the AUTO_INCREMENT
attribute will automatically be filled in with a numeric value that is unique in
that column. You must also put the syntax UNIQUE( 'id' ) to
define that column as having only unique variables.
So when our MySQL query string is passed to the function
mysql_query(), it will create a table like this:
firstName
lastName
email
id
Inserting values is as simple as creating a database. You
simply pass the write MySQL syntax to
mysql_query(). Using the INSERT command, we can insert
multiple rows of information. For now, we're going to add our friends Jon Doe,
Suzie Cue, and Joe Somebody to our contacts table, in order to have some
information in the database to work with initially.
The MySQL syntax for inserting values is " INSERT INTO `table_name` (
column, column ) VALUES ( value, value ) ". You can define as many
columns in whatever order you want. The code ( columnA, columnB ) VALUES
( valueA, valueB ) will give the same result as ( columnB,
columnA ) VALUES ( valueB, valueA ). You may also insert as many rows
of information as you want, encapsulating each row in parentheses and seperating
by a comma.
So, the query string stored in the variable, when passed to
mysql_query(), will insert our contacts and our table will have this
structure:
firstName
lastName
email
id
Jon
Doe
jon.doe@server.com
1
Suzie
Cue
suz.cue@server.com
2
Joe
Somebody
joe.som@server.com
3
In our query, we did not specify any values for the column 'id'. This was by
design: since we didn't specify values, the MySQL table automatically put in
incremental values for us.
There you go! In just a few lines of code, you have used PHP to log on to a
MySQL server, select a database, create a table on that database, and inserted
three rows of values. To set up your database, you would access this file
only once. If this PHP code is executed again, you will get a
MySQL error saying that the table you are trying to create already exists.
In the next step , we'll learn how to print out the information in your
database to the browser.
