Eight Weeks of Prototype: Week 8, A Complete Prototype Example

Creating a Database and Connecting To It

Before we begin, you will need to create a new MySQL database in which to save the contacts in your application. For the purpose of this article we will use a database called prototype8.

Assuming you are able to login via the command using a super-user account, you might use the commands shown in Listing 1 to create the database setup a user to access it. You may want to use a different username and password. 

mysql> create database prototype8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on prototype8.* to prototype8@localhost identified by 'myPassword';
Query OK, 0 rows affected (0.05 sec)

Once you have created the database and connected to it (if you're still logged in from creating the database, just enter the statement use prototype8;), you must create the database tables. For this article we only use a single table, shown in Listing 2.

This database table is fairly simplistic: we will only be saving their name, email and location but if you were to use this for a real-world application you would likely want a different set of fields. 

create table contacts (
    contact_id      serial          not null,
    name            varchar(255)    not null,
    email           varchar(255)    not null,
    location        varchar(255)    not null,
 
    primary key (contact_id)
);

As mentioned in the previous section, we need to create a PHP file called database.php which we use to connect to the database server and select the prototype8 database. To simplify matters I have not included error handling, even though you should definitely do so in your real-world applications.

Listing 3 shows the code for the database.php file, which we will use in the other PHP scripts we create in this article.

<?php
    mysql_connect('localhost', 'prototype8', 'myPassword');
    mysql_select_db('prototype8');
?>

Now that we are able to connect to the database using the database.php script we need the ability to read and write contacts from the database. To do this, we are going to create a class called ContactManager.

The ContactManager class will contain two methods: getContacts(), which is used to retrieve a list of all contacts in the database; and saveContact(), used to save a new contact to the database. As mentioned when creating the database, the actual contact details we are saving are fairly simplistic, but will hopefully still give you a good idea of the key concepts of developing JavaScript applications.

Listing 4 shows the code used to create the ContactManager class. Firstly, I have defined a constant called TABLE which holds the name of the database table we use to hold contact data.

The getContacts() method retrieves all contacts order by their name then writes them to an array called $contacts which is returned. If no contacts are found then an empty array is returned.

The saveContact() method accepts the name, email and location as arguments and inserts them into the database. The mysql_real_escape_string() method is used to prevent SQL injection from occurring.

<?php
    class ContactManager
    {
        const TABLE = 'contacts';
 
        public function getContacts()
        {
            $query = sprintf('select * from %s order by lower(name)', self::TABLE);
            $result = mysql_query($query);
 
            $contacts = array();
 
            while ($row = mysql_fetch_assoc($result)) {
                $contacts[] = $row;
            }
 
            return $contacts;
        }
 
        public function saveContact($name, $email, $location)
        {
            $query = sprintf("insert into %s (name, email, location) values ('%s', '%s', '%s')",
                             self::TABLE,
                             mysql_real_escape_string($name),
                             mysql_real_escape_string($email),
                             mysql_real_escape_string($location));
 
            mysql_query($query);
 
            $id = mysql_insert_id();
 
            return array(
                'contact_id' => mysql_insert_id(),
                'name'       => $name,
                'email'      => $email,
                'location'   => $location
            );
        }
    }
?>

The saveContact() method returns the details that have been inserted into the database, as well as the contact_id value assigned to the new record in the database. We will be returning this data to an Ajax request as JSON data.

• Next: Ajax Request Handler to Return Contacts