PHP | MySQL ( Creating Database )

What is a database?
Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retrieval, insertion and deletion of data from it.

We know that in MySQL to create a database we need to execute a query. You may refer to this article for the SQL query to create data-bases.

The basic steps to create MySQL database using PHP are:

• Establish a connection to MySQL server from your PHP script as described in this article.
• If the connection is successful, write a SQL query to create a database and store it in a string variable.
• Execute the query.

We have already learnt about establish a connection and creating variables in PHP. We can execute the query from our PHP script in 3 different ways as described below:

1. Using MySQLi Object-oriented procedure: If the MySQL connection is established using Object-oriented procedure then we can use the query() function of mysqli class to execute our query as described in the below syntax.

   Syntax:

   <?php
   $servername = "localhost";
   $username = "username";
   $password = "password";
   
   // Creating a connection
   $conn = new mysqli($servername, $username, $password);
   // Check connection
   if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
   } 
   // Creating a database named newDB
   $sql = "CREATE DATABASE newDB";
   if ($conn->query($sql) === TRUE) {
       echo "Database created successfully with the name newDB";
   } else {
       echo "Error creating database: " . $conn->error;
   }
   
   // closing connection
   $conn->close();
   ?>
   

   Note:Specify the three arguments servername, username and password to the mysqli object whenever creating a database.

   Output:
   

2. Using MySQLi Procedural procedure: If the MySQL connection is established using procedural procedure then we can use the mysqli_query() function of PHP to execute our query as described in the below syntax.

   Syntax:

   <?php
   $servername = "localhost";
   $username = "username";
   $password = "password";
   
   // Creating connection
   $conn = mysqli_connect($servername, $username, $password);
   // Checking connection
   if (!$conn) {
       die("Connection failed: " . mysqli_connect_error());
   }
   
   // Creating a database named newDB
   $sql = "CREATE DATABASE newDB";
   if (mysqli_query($conn, $sql)) {
       echo "Database created successfully with the name newDB";
   } else {
       echo "Error creating database: " . mysqli_error($conn);
   }
   
   // closing connection
   mysqli_close($conn);
   ?>
   

   Output:
   

3. Using PDO procedure: If the MySQL connection is established using PDO procedure then we can execute our query as described in the below syntax.

   Syntax:

   <?php
   $servername = "localhost";
   $username = "username";
   $password = "password";
   
   try {
       $conn = new PDO("mysql:host=$servername;dbname=newDB", $username, $password);
       // setting the PDO error mode to exception
       $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $sql = "CREATE DATABASE newDB";
       // using exec() because no results are returned
       $conn->exec($sql);
       echo "Database created successfully with the name newDB";
       }
   catch(PDOException $e)
       {
       echo $sql . "
   " . $e->getMessage();
       }
   $conn = null;
   ?>
   

   Note:The exception class in PDO is used to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

   Output: