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

RSS Fardin in Bremen

  • 19th century Market on the Grand Place of Brussels June 14, 2009
    by: Ibu ClaireThe main market of Brussels has been used as a market, since its creation beginning 10th century.Vegetables and poultry were sold, but also birds maintained into cages for their singing...At the end of 17th century, King Louis the 14th of France willing to conquer the Low Countries(name of Belgium at that time), bombed […]
  • Manneken-Pis June 14, 2009
    by : Ibu ClaireManneken-Pis (a 100m. away from the Grand-Place)This little 17th C. boy is supposed to be the oldest inhabitant of the city of Brussels. He is supposed to have gotten lost, and when found back, he was in this positon !His family was so happy that they offered a water-fontain to the inhabitants […]
  • Place Royale Brussels May 30, 2009
    by: Ibu ClaireThis square was built on the ruins of a previous royal palace, that burned out around 1715. Around 1780, huge works started in the "classical" style inspired by the castle of Versailles (France). It was supposed to be used for military parades,as the new palace was built "around the corner"Archeological researches were made […]
  • Atomium of Brussels 1958 May 21, 2009
    by: Ibu ClaireWas erected in 1958 for the World Exhebition. It represents a molecule of cristal iron and symbolizes the 9 provinces of Belgium. Each sphere has a diameter of 18 m.It is 102m. high and weighs 2400 tons. The designer was Ingenior André Waterkeyn.For its 5Oth anniversary, it was completely restaured,with partly by private […]

RSS Laravel

  • Pumps – My Most Valuable Tips
    Laravel Pumps – My Most Valuable Tips Where to Find the Best Male Reproductive Organ Enlargers There are a lot of products that are labeled for male reproductive organ enlarger. With these male reproductive organ enlarger products, the main purpose is to help the man get a bigger and better erection for intercourse. The male […]
  • aboard united parcel service and goblet professionals really get the job done each and every time i need these phones board up a building or my company.
    Laravel aboard united parcel service and goblet professionals really get the job done each and every time i need these phones board up a building or my company. Preparing repairs and improvements in an office which will continuously be working with the course of action could actually be a difficulty simply because one, accidents may […]
  • A Simple Plan: Services
    Laravel A Simple Plan: Services 3 Benefits to Massage Therapies There are some people who do not like being massaged at all, while there are other people who enjoy it very much; the truth is, massage therapies are actually very beneficial to people. It does not matter how old you are, you will definitely be […]
  • Professionals – Getting Started & Next Steps
    Laravel Professionals – Getting Started & Next Steps Rights of an Employee in Workers’ Compensation Law To start with, this is an insurance that covers the workers who get injured or fall sick during work. This law clearly outlines the duties and responsibilities of a worker. Other than that, it covers the employer’s obligations to […]

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

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:

<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.

The copyright of the article Creating a MySQL Database for a PHP Application in PHP Programming is owned by Mark Alexander Bain. Permission to republish Creating a MySQL Database for a PHP Application in print or online must be granted by the author in writing.


2 Comments

Comments are closed.

RSS Geospatial

  • ArcPy and ArcGIS: Geospatial Analysis with Python
    Geospatial ArcPy and ArcGIS: Geospatial Analysis with Python ArcGIS allows for complex analyses of geographic information. The ArcPy module is used to script these ArcGIS analyses, providing a productive way to perform geo-analyses and to automate map production. This book will guide you from basic Python scripting to advanced ArcPy script tools. This book starts […]
  • Geospatial Semantic Web
    Geospatial Geospatial Semantic Web This book covers key issues related to Geospatial Semantic Web, including geospatial web services for spatial data interoperability; geospatial ontology for semantic interoperability; ontology creation, sharing, and integration; querying knowledge and information from heterogeneous data source; interfaces for Geospatial Semantic Web, VGI (Volunteered Geographic Information) and Geospatial Semantic Web; challenges of […]
  • What are geospatial technologies
    Geospatial What are geospatial technologies About the technologies Geospatial technologies is a term used to describe the range of modern tools contributing to the geographic mapping and analysis of the Earth and human societies. These technologies have been evolving in some form since the first maps were drawn in prehistoric times. In the 19th century, […]
  • Google Maps JavaScript API Cookbook
    Geospatial Google Maps JavaScript API Cookbook Day by day, the use of location data is becoming more and more popular, and Google is one of the main game changers in this area. The Google Maps JavaScript API is one of the most functional and robust mapping APIs used among Geo developers. With Google Maps, you […]