by: Mark Alexander Bain
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 in a few minutes. 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:
Once the password has been obtained then the database can be accessed and the database can be created:
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:
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:
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.