Creating a MySQL Database for a PHP Application

How to Automate the Creation of MySQL Databases with PHP

Mar 24, 2009
Mark Alexander Bain

Create a MySQL Database with PHP - Mark Alexander Bain

When a new PHP application is installed it will probably need access to a MySQL database - a database that can be automatically created by the PHP application itself.

The distribution of a PHP application is always very easy. It is just a matter of compressing the application files, transferring the compressed file to a user's web server, and then decompressing the files again. In just a few minutes the application can be up and running. However, it's not quite as easy with the MySQL supporting the application. The MySQL database needs more than a few files to be transferred. Fortunately the programmer can create the database automatically by using some additional PHP code.

The Prerequisites of Working with a MySQL Database and PHP

Before creating the database itself it is worth nothing that PHP comes with its own libraries and objects for working with MySQL (the mysql and mysqli objects and libraries). However, these are not installed by default – this must, therefore, be done before PHP can communicate with MySQL. It’s also worth noting that PHP’s preferred method is currently to use mysqli and not mysql even though both are still available.

Creating a Blank MySQL Database with PHP

Every MySQL database is created with a default administrator user account. This account is called root and is used to:

Create new databases
Create new user accounts

Obviously the root account will have a password but this cannon be known by the PHP programmer. The PHP application must, therefore, obtain this from the user:

Enter root password

</form>

Once the password has been obtained then the database can be accessed and the database can be created:

if (isset($_REQUEST['root_password'])) {

$user = "root";

$password = $_REQUEST['root_password'];

$host = "localhost";

$database = "mysql";

$new_db = "suite101_demo";

echo "Connecting to the MySQL Database <br>";

$db = new mysqli ($host, $user, $password, $database);

echo "Creating the new database </br>";

$db->query("create database if not exists " . $new_db);

With the database created the application will also needs to create any tables that will store the data and its own database user.

Creating a MySQL Database User with PHP

The root user account should only be used as a one off – to set up the database in the first place. Any other access by the PHP application should be via a user account set for that purpose, for example:

echo "Creating the application user account<br>";

$user_sql = "grant select, insert, update, delete

on ". $new_db . ".*

to suite101_user@localhost

identified by 'suite101_password'";

$db->query($user_sql);

This new user will be able to view, add, modify and remove data stored in the database tables.

Creating MySQL Database Tables with PHP

The database is now in place as is the database user for the application. The final step is, therefore, to create the tables themselves:

echo "Creating the database tables<br>";

$db->select_db($new_db);

$table_sql = "create table if not exists articles

(id int auto_increment,

title varchar(255),

published datetime,

primary key (id))";

$db->query($table_sql);

$db->close();

}

Here the PHP changes to the new database, creates any tables that are required and then closes the connection to the database. This all means that no manual database creation is necessary – it’s all done by the PHP application itself.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...