Create & Drop MySQL Database Using PHP and Command Line

In this tutorial, we will show you how to create and delete/drop MySQL database using the command line interface and PHP Script.

How to Create and Drop MySQL Database using PHP Script and Command Line

Here are ways:

1. Create & Drop MySQL Database Using Command Line or CMD

Here are steps:

LogIn As Root User

On the command line, log in to MySQL as root user

mysql -u root -p

Next step, type mysql root password, and then press Enter.

Create User

To create a database user, type the following command. Replace username with your username, and replace password with your password :-

GRANT ALL PRIVILEGES ON . TO 'username'@'localhost' IDENTIFIED BY 'password';

After created a new user just type \q to exit the mysql program.

LogIn With New Created User

To log into MySQL as a user you just created, type the following command. Replace the user name you created in step 3 with the name of :-

mysql -u username -p

Next step, type user’s password, and then press Enter.

Create Database

To create a database, use the below following command. Replace dbname with the name of the database you want to create :-

CREATE DATABASE dbname;

Use Databse

To work with new databases, use the following command. Replace Dbname with the name of the database that you created in the preview phase: –

USE dbname;

Create Table and Insert Data

Now you can work with new databases. For example, the following commands demonstrate how to create a base table for example, and how to insert some data in it :-

CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Test data' );

Drop MySQL Database

Now you can use the following command for deleting MySQL database from the mysql> prompt. Replace database name with your database name :-

DROP DATABASE dbname;

Drop MySQL Table

Now you can use the following command for deleting MySQL table from datbase from the mysql> prompt. Replace table name with your database table name :-

DROP TABLE  tablename;

PHP Script to Create and Drop MySQL Database

There are two methods available for that:

• First is MySQLi
• The second one is PDO

Create Database

Syntax:-

CREATE DATABASE database_name

The CREATE DATABASE statement is used to create a database in MySQL.

Example of CREATE DATABASE using MySQLi

In this MySQL CREATE DATABASE examples create a new database named “testDB”. This example is (MySQLi Object-oriented)

<?php
$host= "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($host, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE testDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}
$conn->close();
?>

Example of (MySQLi Procedural)

In this MySQL CREATE DATABASE examples create a new database named “testDB”. This example is (MySQLi Procedural)

<?php
$host= "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($host, $username, $password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
// Create database
$sql = "CREATE DATABASE testDB";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Example CREATE DATABASE using PDO

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

DROP Database

The DROP DATABASE statement delete all tables in the database and deletes the database permanently.

Syntax :-

DROP DATABASE database_name

The DROP DATABASE statement is used to drop a database in MySQL.

Example of DROP DATABASE using (MySQLi Procedural)

<?php
    $host = "localhost";
    $username = "username";
    $password = "password";
    // Create connection
    $conn = mysqli_connect($host, $username, $password);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'DROP DATABASE testDB';
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not delete database testDB: ' . mysql_error());
   }

   echo "Database deleted successfully\n";

   mysql_close($conn);
?>

Recommended Tutorials

1. To Remove Elements or Values from Array PHP
2. PHP remove duplicates from multidimensional array
3. Remove Duplicate Elements or Values from Array PHP
4. How to Convert String to Array in PHP
5. Array Push and POP in PHP | PHP Tutorial
6. PHP Search Multidimensional Array [key and value and return key]
7. Get month name from date in mysql
8. mysql get first day of the current month
9. mysql last day of previous month
10. Get month number from month name in mysql
11. mysql get day of week number from date
12. Mysql Get Data Of Current Date, Week, Month, YEAR
13. Get year of week in MySQL