PHP MySQL create Database

In the previous MySQL tutorial, we saw how to select a database at the time of establishing a connection or after the connection is established by using 'mysqli_select_db()' function. But what if we don't have a database and we don't want to go to the 'phpmyadmin' dashboard to create a MySQL database. In this case, we can use PHP's 'mysql_query()' function to create a MySQL database right from a PHP website's dashboard.

What is a database?

A Database is a collection of inter-related data stored under one name. It is used to efficiently store, retrieve and delete data whenever needed. In MySQL, the database is a Relational DBMS (RDBMS) which stores the data in the form of tables. Infinite tables can be created inside a database and can be managed with ease in MySQL DBMS. For example, for a school's database, the tables can have data about students, teachers, staff, etc.


In SQL, any kind of operation in the database is implemented through a query. So, for every task, we should know the query to execute it. This query will be passed as a parameter to the 'mysqli_query()' function and then it will get executed.

Remember that the first and foremost requirement is to establish a connection with a database connection. Only after that, any function related to the MySQL database will work.

The basic steps to create a MySQL database using PHP are:

  • Establish a connection with the MySQL server.
  • Write an SQL query to create a database and store it in a variable.
  • Execute the query using 'mysql_query()' function or by 'exec()' funtion in PDO approach.

SQL CREATE DATABASE Query

Let's see the query to create a database in SQL. It is very simple and as SQL is a High Level language it is easy to understand also. We're assuming that you already have an SQL Server available to use, if not, please check out the "getting started guide".

The SQL "CREATE DATABASE" statement is used to create a database. Let's see the full syntax:

Syntax

 CREATE DATABASE database_name;

This SQL statement creates a database named mydb:

CREATE DATABASE mydb;

Just remember that only creating a database is not sufficient you have to select the database, create tables inside it, in order to insert, retrieve and delete data from it. We will see all these operations but right now we will just focus on how to create a database in MySQL.


Create Database Using MySQLi Procedural approach:

To execute a query using the procedural approach we will use 'mysqli_query()' function. We will pass the SQL's 'CREATE DATABASE' command in the 'mysqli_query()' function along with the connection variable you have set(Most Important) as the first parameter. Remember that, you can directly pass the query as a 2nd parameter to the function or you can first store it as a string in a PHP variable and then can pass that variable as the second parameter. The mysql create database examples given below will clarify things for you.

Example

<?php /* MySQL server connection */ $link = mysqli_connect("localhost", "root", ""); /* Check connection */ if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } /* Attempt create database query execution */ $sql = "CREATE DATABASE mydb"; if(mysqli_query($link, $sql)){ echo "Database created successfully"; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } /* Close connection */ mysqli_close($link); ?>

Output

Database created successfully

Create Database Using MySQLi Object-Oriented Approach

To execute a query using the MySQli object oriented approach, we will use the 'query()' function of MySQli class.Obviously we will access this function using the connection object. This function only has one parameter in which we can pass the query directly or also pass a string variable containing the query. TThe mysql create database examples given below will clarify things for you.

Example

<?php /* MySQL server connection. */ $mysqli = new mysqli("localhost", "root", ""); /* Check connection */ if($mysqli === false){ die("ERROR: Could not connect. " . $mysqli->connect_error); } /* Attempt create database query execution */ $sql = "CREATE DATABASE demo"; if($mysqli->query($sql) === true){ echo "Database created successfully"; } else{ echo "ERROR: Could not able to execute $sql. " . $mysqli->error; } /* Close connection */ $mysqli->close(); ?>

Output

Database created successfully

Create database Using PDO Extension

In the PDO extension approach although making a connection is a bit complicated as compared to MySQLi or MySQL extension but executing a query is quite simple. The 'exec()' function is used in PDO approach to execute the queries. Same as the previous two extensions, we will pass the query as a parameter to 'exec()' function. Look at the example below to see how you can use this function.

Example

<?php /* MySQL server connection */ try{ $pdo = new PDO("mysql:host=localhost;", "root", ""); // Set the PDO error mode to exception $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e){ die("ERROR: Could not connect. " . $e->getMessage()); } /* Attempt create database query execution */ try{ $sql = "CREATE DATABASE demo"; $pdo->exec($sql); echo "Database created successfully"; } catch(PDOException $e){ die("ERROR: Could not able to execute $sql. " . $e->getMessage()); } /* Close connection */ unset($pdo); ?>

Output

Database created successfully

In the example above we have set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION, it tells PDO to throw exceptions whenever a database error occurs. The exception handling feature is a great benefit of PDO.

If in case, any exception is thrown within the 'try{ }' block, the script stops executing and flows directly to the first 'catch(){ }' block. In the example above, the catch block echoes the SQL Query and then generates an error message.




Color Picker

List of color names supported by all browser »



Character Code

List of character codes to display special character »



Language Code

List of all language supported by all browser »



Country Code

List of Country code supported by all browser »

Latest Updates

PHP Introduction

How to Run a PHP program in Xampp?

How can I create a phpinfo.php page?

How to Define a Variable in PHP

PHP Echo and Print Statements

Working with PHP Operators

Understanding the PHP Data Types

How to Write Comments in PHP

How to Define a Constant in PHP

PHP if...else...elseif Statements