PHP Series – Creating a database structure with MySql

MySQL is one of the most popular open sources databases in the world developed and maintained by Sun Microsystems. It is one of the easier to use databases in the World and most of the servers have it available as a “free” database to use.

If it is the first time that you are reading this article series, then you will need to install MySQL to your development machine. For that you can refer to the 3rd post of the series – Starting with PHP – where it shows detailed information about how to install and what else you will need to use PHP and MySQL.

As the series is almost finished we will create a small database on MySQL using  PHPMyAdmin or the MySQL GUI tools.

In order to keep things simple and direct, we will be creating a database with a small set of tables to control user information and login. After, when working with PHP accessing the database, the goal will be to log a registered user and display a welcome profile page.

Understanding the types of tables. In MySQL you have several table types, or Storage Engines, but usually applications use the MyISAM or the InnoDB Engines. When creating the tables is good to know some few characteristics of these engines because it can make your application faster or slower. For instance the MyISAM has as characteristics table lock level and is fast for reading and inserting; InnoDB in the other hand have row lock level and is fast for updating. This can make the InnoDB more suitable for transactions.

To better understand consider three tables:

  1. A table with the Person information
  2. A table with the Address information
  3. A table with relationship of Person-Address where a person can have several addresses
  1. If you consider this scenario having table 1 and 2 as InnoDB and table 3 as MyISAM can speed up a lot your application.

First let’s start with the database. Create a database called php_series. Make sure to add a user to this database where he can have full control over it.

[note:] As long you have a root user on the MySQL server, you can create a database via PHP scripting by:

<?php
try
{
$conn = mysql_connect(‘localhost’, ‘yourUser’, ‘yourPassword’);
}
catch (Exception $e)
{
echo ‘Unable to connect to the MySQL database – ‘, mysql_error();
}

$createDb = ‘CREATE DATABASE `mysqlExample1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;‘;
try
{
$query = mysql_query($createDb, $conn);
}
catch(Exception $e)
{
echo ‘Unable to create the database. – ‘, mysql_error();
}
$sdb = mysql_select_db(‘mysqlExample1’, $conn);
?>

The next step is to create the user table and before we really do some work with PHP / MySQL scripting, create the tables using your favorite DB Management Tool (phpMyAdmin, MySQLWorkbench, etc).

The user table will have the following fields:

  • userId – interger – autoincrement – unique – key
  • personId – interger – foreign key
  • datetime – datetime
  • username – string
  • password – string (yeah, string, but it will be encrypted, no worries)
  • enabled – boolean – default value true

The goal of this table is to store information about a user account that is related to a given person. This is pretty much the table that will control the login / loggout from our application.

On the end this is how your table should look like:

— —————————————————–
— Table `mydb`.`userAccount`
— —————————————————–
CREATE  TABLE IF NOT EXISTS `mydb`.`userAccount` (
`userId` INT NOT NULL ,
`username` VARCHAR(45) NOT NULL ,
`password` VARCHAR(255) NOT NULL ,
`enabled` TINYINT(1) NULL ,
`created` DATETIME NULL ,
PRIMARY KEY (`userId`) ,
INDEX `personId` () ,
CONSTRAINT `personId`
FOREIGN KEY ()
REFERENCES `mydb`.`person` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

On the table above we have inserted a Foreign key to relate the  user information to the person information. The person information is a simple table where it will store the first name, last name, gender, date of birth, etc. This is a simple script for an example of that table:

— —————————————————–
— Table `mydb`.`person`
— —————————————————–
CREATE  TABLE IF NOT EXISTS `mydb`.`person` (
`personId` INT NOT NULL ,
`firstName` VARCHAR(100) NOT NULL ,
`lastName` VARCHAR(100) NOT NULL ,
`gender` CHAR(2) NULL ,
`dob` DATETIME NOT NULL ,
`created` DATETIME NULL ,
PRIMARY KEY (`personId`) )
ENGINE = InnoDB;

Not big of a database, but it’s the minimal necessary to create a login page and a profile page. We will need some much needed users to be using as example on the next part of the series, but we can’t create a password without encryption, so, using the mysql functions from PhP, let’s add one person and user on the database that we can use on the next part of the series.

<?php

$dbCon = mysql_connect(‘localhost’, ‘yourUser’, ‘yourPassword’) or die (‘Unable to connect to the database server: ‘ . mysql_error());
$sDB = mysql_select_db(‘mydb’, $dbCon) or die (‘Unable to connect to the database: ‘ . mysql_error());
// first insert the person:
$insertQuery = ‘INSERT INTO `person` (`firstName`, `lastName`, `dob`) VALUES (`Nine`, `Roman`, `1977-01-01 00:00:00`)’;
$query = mysql_query($insertQuery, $dbCon);

// now insert the user:
$password = md5(‘nine’);

$insertQuery = ‘INSERT INTO `userAccount` (`username`, `password`, `personId`) VALUES (`nine09`, `’. $password .’`, 1)’;
$query = mysql_query($insertQuery, $dbCon);
?>

With this small script you have inserted the person and user information for our “Nine” person. Therefore there are some small items that need some focus:

  1. Since the fields are auto-increment there is not need to add them on the list of fields to be added, MySQL will automatically add those fields for you
  2. On the second query, note that the personId is already set to 1. Since there is only one person added, we can hard-code that value, but when it’s not the case it’s necessary to get the last_inserted_id to correctly associate the user account with the person account.
  3. Even knowing that when you are adding all fields there is no need to list the fields names that will be inserted, therefore this really does helps on speeding the insertion of the query.

The objective of this post was never to teach the full understanding of a MySQL database and for that a larger application with more Storage Engines and more complexity is necessary, therefore is strongly suggested that you check the MySQL Developer Zone witch has a lot of documentation and information about the MySQL servers.

Have fun.

Advertisements

About mcloide

Making things simpler, just check: http://www.mcloide.com View all posts by mcloide

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: