Creating or deleting a MySQL database - SQL queries

In our previous tutorial about SQL code insertion in PHP, we have seen that there exist two ways to interact with your database server once a connection has been established. The first way is to run SQL queries directly from a database manager's interface such as phpmyadmin (which makes sense when you need to intervene directly because you want to do some database maintainance); another way is to run the queries from within your PHP scripts (which makes more sense when it comes to developing a dynamic website). The latter is achieved via the use of the PHP function mysql_query which serves as an interface between your PHP script and the MySQL server.

Note that in order to run SQL queries from a PHP script using the function mysql_query, you must first have established a connection to your MySQL database server (as indicated in our tutorial about connecting to a MySQL database server).

Creating or deleting a MySQL database - Creating a MySQL database

Once a MySQL connection has been established, you can use the SQL command CREATE DATABASE (note that it is customary to type SQL commands in caps). Its syntax is as follows:

CREATE DATABASE database_name

where database_name is the name of the database to be created (as it is, i.e. not put between quotes).

Learn the PHP & MySQL code:

<?php
$connection = mysql_connect('localhost', 'john', 'secret');
if (!$connection) die('An error has occured during the connection');
mysql_query('CREATE DATABASE my_database');
?>

Remarks:

Upon executing the previous PHP script in your web browser, you will notice that nothing happens. This is perfectly normal: the purpose of that script was simply to create a database, and you can now check in phpmyadmin (or in any other MySQL database manager) that the database was indeed created. Of course, you will then need to fill this database with information, which you will learn how to do in the following tutorial.
The PHP function mysql_query was necessary to run the SQL command CREATE DATABASE from your PHP script. However, if you run the SQL command directly from your phpmyadmin SQL command prompt, all you need to type is CREATE DATABASE my_database; (SQL queries end with a semi-colon). The PHP function mysql_query is here only to guarantee the communication between PHP and the MySQL server.

Creating or deleting a MySQL database - Deleting a MySQL database

A MySQL database can be deleted with the SQL command DROP DATABASE. From your PHP script, the following lines of PHP code allow you to delete the database that was just created in the previous example:

<?php
$connection = mysql_connect('localhost', 'john', 'secret');
if (!$connection) die('An error has occured during the connection');
mysql_query('DROP DATABASE my_database');
?>

In this PHP tutorial we have introduced the two SQL commands CREATE DATABASE and DROP DATABASE and we showed ho the PHP function mysql_query allows to query your MySQL server from within your PHP script. In the following tutorials, we shall see how to manipulate the databases you have just created.

Next tutorial: MySQL database tables
Previous tutorial: Connecting to a MySQL database server

Back to computer forums