Creating a MySQL Database for a PHP Application: How to Automate the Creation of MySQL Databases with PHP

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:

<form method=post>
Enter root password
<input name=root_password type=password>
<input type=submit value="Create Database" name=create_database>
</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.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---