Don't Miss These Great Genealogy Sites!!!!

Genealogy Resources

Sheppard, Cash and Hairston families of GA, SC & VA

Hancock Genealogy

Pioneer Families of South Georgia

Genealogy Resources Online

Sponsors

Tiny Designs by Sarah

Doolebugs

UCAN

Descendants of Adam

Better Built Buildings

Georgia Life

Sell it in Georgia

eDirectory



	Find lost family, friends, and loved ones.

	Enter a name for immediate results:

	First Name

	Last Name



"I found my brother! It's like we've always known each other!"
- Pam from CA

Doye Tech Students' Page

Using PHP and MySQL Together:

Creating a Simple Online Address Book

From Steven Simmons,,
Your Guide to PHP / MySQL.

About this Tutorial: What You'll Need

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.

If you don't, there is a quick tutorial on PHP basics to help you out. Also, you should know some basic MySQL syntax as well as the bare essentials of HTML.

You will also need a text editor, FTP client, and a server with both PHP and MySQL. You can get PHP/MySQL tools online for free.

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. If you don't have these, refer to the requirements page.

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.

Or, another way to do the same thing as the code above:

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

   // create the table
   $create_query = "CREATE TABLE `contacts` ( lastName VARCHAR(20),
      `firstName` VARCHAR(50), `email` VARCHAR(20),
      `id` INT AUTO_INCREMENT, UNIQUE (`id`) )";

   if( mysql_query( $create_query ) )
      echo( "Table created successfully.<br>" );
   else
      die( "Error! Could not create table: " . mysql_error() );

   // put in dummy values
   $insert_query = "INSERT INTO `contacts`
      ( `firstName` , `lastName`, `email` ) VALUES
      ( 'Jon' , 'Doe', 'jon.doe@server.com' ) ,
      ( 'Suzie' , 'Cue', 'suz.cue@server.com' ),
      ( 'Joe', 'Somebody', 'joe.som@server.com' ) ";

   if( mysql_query( $insert_query ) )
      echo( "Values inserted successfully." );
   else
      die( "Error! Could not insert values: " . mysql_error() );

?>

</body>
</html>

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.

// create the table
   $create_query = "CREATE TABLE `contacts` ( lastName VARCHAR(20),
      `firstName` VARCHAR(50), `email` VARCHAR(20),
      `id` INT AUTO_INCREMENT, UNIQUE (`id`) )";

   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.

// put in dummy values
   $insert_query = "INSERT INTO `contacts`
      ( `firstName` , `lastName`, `email` ) VALUES
      ( 'Jon' , 'Doe', 'jon.doe@server.com' ) ,
      ( 'Suzie' , 'Cue', 'suz.cue@server.com' ),
      ( 'Joe', 'Somebody', 'joe.som@server.com' ) ";

   if( mysql_query( $insert_query ) )
      echo( "Values inserted successfully." );
   else
      die( "Error! Could not insert values: " . mysql_error() );

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.

>> Next: Part 2: Outputting an index of your MySQL table

Current Projects

Hot Listt

Georgia Genealogy Home Page

Genealogy & Family History Resources

Back to Top

Contact Information
Electronic mail address
ucanusa@yahoo.com
Web address
http://go.to/taylorhistory
Back to Top

Comments and Suggestions

Student Name

Message

Back to Top

Copyright John R. Taylor 1999 - 2004
Last revised: Nov 21, 2005 .

Table of Contents

Doye Tech Students' Page

Contents:

Using PHP and MySQL Together:

Creating a Simple Online Address Book

About this Tutorial: What You'll Need

Creating the MySQL Tables

Current Projects

Hot Listt

Back to Top

Contact Information

Back to Top

Comments and Suggestions

Back to Top

Copyright John R. Taylor 1999 - 2004
Last revised: Nov 21, 2005 .

Table of Contents

<a target="_blank" href="http://v1.nedstatbasic.net/stats?AAaM0QpDXFlLKBrm3+9RHhRxCVNA"><img src="//m1.nedstatbasic.net/n?id=AAaM0QpDXFlLKBrm3+9RHhRxCVNA" border="0" nosave width="18" height="18"></a>

Doye Tech Students' Page

Contents:

Using PHP and MySQL Together:

Creating a Simple Online Address Book

About this Tutorial: What You'll Need

Creating the MySQL Tables

Current Projects

Hot Listt

Back to Top

Contact Information

Back to Top

Comments and Suggestions

Back to Top

Copyright John R. Taylor 1999 - 2004 Last revised: Nov 21, 2005 .

Copyright John R. Taylor 1999 - 2004
Last revised: Nov 21, 2005 .