It’s Festive Season! Enjoy 30% Off

Use code FESTIVE30 at checkout

Code has been added to clipboard!

MySQL Create Database Functionality: Step by Step Process

Reading time 2 min
Published Aug 8, 2017
Updated Oct 15, 2019

In the previous lesson we already established why database management systems are important. Now, it might be a great time to learn how to use MySQL to create databases and use them correctly.

The first question we should answer is simple: how to create a database in MySQL? This step is necessary to take before you can start saving or accessing your data.

To create MySQL database (in other words, to execute an SQL query) is not hard, and in this tutorial, we will mainly view common MySQL create database examples. As you go through them, you will understand what statements should be used and what steps have to be taken.

MySQL Create Database: Main Tips

  • An SQL database is a collection of data tables which consist of columns (properties) and rows (individual entries).
  • Before you start learning how to create a database in MySQL (or delete it), you should check whether you have create privileges.
  • To create MySQL database, you need to execute an SQL query.
Theory is great, but we recommend digging deeper!

Statement for Creating Database

In these examples, we will be showing you how to use the create database MySQL statement to create a new database named my_data:

Example
<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  // Create connection
  $conn = new mysqli($host, $user, $pass);  	
  // Check connection  	
  if ($conn->connect_error) {      
    die("Failed to connect: " . $conn->connect_error);
  }
  // Create database  	
  $sql = "CREATE DATABASE my_data";
  if ($conn->query($sql) === TRUE) {      	
    echo "Database creation successful";
  } else {
    echo "Failed to create database: " . $conn->error;
  }
  $conn->close();  
?>

When you use MySQL to create databases from scratch, only the first three arguments are required for the MySQLi.

In case you want to use a specific port when you create MySQL database, you should add an empty string as an argument in its place. It should look like this:

new mysqli(host, user, pass, "", port)

Example
<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  // Create connection  
  $conn = mysqli_connect($host, $user, $pass);  	
  // Check connection  	
  if (!$conn) {      
    die("Failed to connect: " . mysqli_connect_error());
  }
  // Create database  	
  $sql = "CREATE DATABASE my_data";  	
  if (mysqli_query($conn, $sql)) {    
    echo "Database creation successful.";
  } else { 
    echo "Failed to create database: " . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

The example below shows how the script for creating database PDO:

Example
<?php
  $host = 'host';
  $user = 'user';  	
  $pass = 'pass';
  try {
    $conn = new PDO("mysql:host=$host;dbname=my_data", $user, $pass);      	
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    $sql = "CREATE DATABASE my_data";    
    // use exec() because no results are returned    
    $conn->exec($sql);      	
    echo "Database creation successful.<br>";
  } catch(PDOException $e)
  { 
    echo $sql . "<br>" . $e->getMessage();
  }
  $conn = null;  
?>

One of the greatest advantages of PDO is its exception class, which allows handling common PHP database query errors. In case we find an exception thrown inside {} segment, the current block of code stops being executed and continues at the first catch() (where we have set up an error alert).

MySQL Create Database: Summary

  • Any SQL database consists of data tables made of columns and rows.
  • Columns depict parameters and every row is an individual entry.
  • To run a query and make MySQL create new database, first make sure you have creation privileges. If you do, use create database MySQL statement