PHP MySQLi Create Database

A database consists of tables. A table stores information in the form of rows and columns. Therefore, to store information in your database, you need to create a database first.

We can follow any of the following approaches to create a database in our MySQL database server:

1. Using Manually
2. Using PHP MySQLi Script

Note - A database consists of one or more tables. A table consists of one or more rows (records), where each row consists of one or more columns (fields).

PHP MySQL Create Database - Manually

Since in previous tutorial, I've already mentioned that, I'm going to use XAMPP, that provides MySQL database to work with. Therefore to create MySQL database, manually, follow these steps:

Step No.1: Open XAMPP Control Panel.

Step No.2: Click on Start, next to Apache.

Step No.3: Click on Start, next to MySQL.

Step No.4: Now click on Admin, next to MySQL, as shown in the snapshot of the XAMPP Control Panel Window given below:

Step No.5: The window opened after clicking on the Admin, as directed above, looks like:

Step No.6: Click on the Databases available at top left on the top menus, like shown in the snapshot given below:

Step No.7: Enter the name of the database say codescracker and click on the Create button, like shown in the snapshot given below:

The database named codescracker is created successfully. If you see on the left menu, there codescracker can be seen every time, when you open the MySQL Databases.

Create MySQL Database using PHP MySQLi Object-Oriented Script

To create a MySQL database using PHP MySQLi object-oriented script, follow the example given below:

<?php
   $servername = "localhost";
   $username = "root";
   $password = "";
   
   // Opens a connection to the Database
   $conn = new mysqli($servername, $username, $password);
   
   // Checks the connection
   if($conn->connect_errno)
   {
      echo "Database connection failed!<BR>";
      echo "Reason: ", $conn->connect_error;
      exit();
   }
   
   // Creates a database named student
   $sql = "CREATE DATABASE student";
   $qry = $conn->query($sql);
   if($qry)
   {
      echo "Database created successfully.";
      
      // block of code, to process further...
   }
   else
   {
      echo "Database has not been created!!<BR>";
      echo "Reason: ", $conn->error;
   }
   $conn->close();
?>

The output produced by above PHP example on creating a database using PHP MySQLi script, is shown in the snapshot given below:

Now if you open the database server, then a new database named student, will be available there.

Now let me re-execute the above PHP script again. Okay, here is the output I have got:

Since the database named student was already created, therefore we are seeing this output. But the problem is, the error message displayed, is not as I have written in script. That is because of default error reporting mode.

To get the manual written error message to be displayed, instead of the default one, we need to change the error reporting mode, using either mysqli_driver() (for object-oriented) or mysqli_report() (for procedural). For example:

<?php
   $driver = new mysqli_driver();
   $driver -> report_mode = MYSQLI_REPORT_OFF;
   
   $conn = new mysqli("localhost", "root", "");
   
   if(!$conn->connect_errno)
   {
      if($conn->query("CREATE DATABASE student"))
         echo "Database created successfully.";
      else
         echo "Error Occurred<BR>Reason: ", $conn->error;
   }
   $conn->close();
?>

Now the output should be:

Error Occurred
Reason: Can't create database 'student'; database exists

Important - Since, we only need to provide the first three arguments to the mysqli() function, to connect to the database server. Therefore, if you want to use a specific port, to connect through. Then let me tell you, you need to add/specify that port number always on the fifth parameter. Therefore just give empty string, that is "" as fourth parameter (used for database name), then use fifth parameter to specify the port number. For example:

$conn = new mysqli("localhost", "root", "", "", 67);

to connect to the database server at port number 67.

Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.

Note - The new keyword is used to create a new object.

Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.

Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.

Note - The query() is used to perform query on the MySQL database, in object-oriented style.

Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.

Note - The close() is used to close an opened connection, in object-oriented style.

Note - The mysqli_driver() is used to modify the error reporting mode, in object-oriented style.

Create MySQL Database using PHP MySQLi Procedural Script

To create a MySQL database using PHP MySQLi procedural script, here is an example, you need to follow:

<?php
   mysqli_report(MYSQLI_REPORT_OFF);
   
   $conn = mysqli_connect("localhost", "root", "");
   
   if($conn)
   {
      if(mysqli_query($conn, "CREATE DATABASE student"))
         echo "Database created successfully.";
      else
         echo "Error Occurred<BR>Reason: ", mysqli_error($conn);
   }
   mysqli_close($conn);
?>

This script does the same job as of previous one.

Note - The mysqli_report() is used to modify the error reporting mode, in procedural style.

Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.

Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.

Note - The mysqli_error() is used to return the description of error (if any), by the most recent function call, in object-oriented style.

Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.

PHP Online Test