How to Create a MySQL Database with PHP

As a programmer, I would typically use the MySQL control panel or PHPmyAdmin if I wanted to create a database. But occasionally you may have a need to create a database on the fly. This simple tutorial will show you how to create a database from within PHP.

First, MySQL is smart enough not to allow you to create a database if one by that name already exists. We can avoid that potential error by simply checking to see if one already exists. For demonstration purposes, we’ll use a simple if/else conditional statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<?PHP 
 
mysql_connect ("localhost", "fred", "fredpw") or die ('error: ' . mysql_error());
 
$new_db = "bozo"; // the name of our new database
 
$exists = mysql_select_db($new_db); // check to see if it exists
 
if ($exists) { // if database already exists
  echo ("<p>Database ". $new_db. " already exists.");
  } else {
  echo ("<p>Database " . $new_db . " does not exist.");
}
 
?>

As with a lot of my test scripts, Line 3 just connects to MySQL with a username and password. I run MySQL and PHP locally on my laptop which makes testing scripts much simpler. Fred is my test user and his password is “fredpw.” Yep, that’s original.

Line 5 defines a variable with the name of the new database we’re going to create. In this case, we’re going to create a database called “bozo.” That’s right, as in Bozo the Clown.

Line 7 defines a variable to the result of a MySQL query. The query we’re sending is simply, “mysql_select_db” which tries to select the database. In this case, a database called bozo. If the query is successful, a result will be stored in the $exists variable. If the query is unsuccessful (ie., it could not select it), it won’t return anything and nothing will be stored in the $exists variable.

We use this fact in line 9 to see if the variable exists (ie., if a database was selected). So in English, lines 9-12 say something like, “If $exists, do something. Otherwise, do something else.”

If you run the above code through your browser, you’ll get a simple one line response saying:

Database bozo does not exist.

Unless, for some crazy reason you already had a database called bozo. Most people don’t.

The obvious next step is to use the IF/ELSE statement to create the database if it doesn’t exist. The MySQL command to create a database is “CREATE DATABASE DATABASE_NAME“. Simple enough. Let’s add this to a MySQL executable line inside our IF/ELSE statement.

With the two new lines (12 and 13), our code now looks like the below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<?php 
 
mysql_connect ("localhost", "fred", "fredpw") or die ('error: ' . mysql_error());
 
$new_db = "bozo"; // the name of our new database
 
$exists = mysql_select_db($new_db); // check to see if it exists
 
if ($exists) { // if database already exists
  echo ("<p>Database: ". $new_db. " already exists.");
  } else {
  $query="CREATE DATABASE $new_db";
  mysql_query($query) or die ('error: ' . mysql_error());
  echo ("<p>Database " . $new_db . " has been created.");
}
 
?>

If you run this code, you should now get a response saying:

Database bozo has been created.

If you run it again (refresh your page), the response should now be:

Database bozo already exists.

There you have it. A simple PHP script to check and see if a database exists, and if it doesn’t, create it. Of course, there’s not much you can do with a database like this yet. You’re going to need to create tables for storing your data.

See the next simple tutorial How to Create MySQL Database Tables with PHP to actually make use of the database you just created.