MySQL Create Database and Tables

MySQL CREATE is used to create Database and its Tables.

Create a Database

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

We have to add the CREATE DATABASE statement to the mysqli_query() function to execute the command.

Example:
<?php
// Database connection establishment
    $con=mysqli_connect("example.com","alex","qwerty");
    
// Check connection
    if (mysqli_connect_errno($con)) {
    echo "MySQL database connection failed: " . mysqli_connect_error();
    }

// Create database
    if (mysqli_query($con,"CREATE DATABASE my_database")) {
        echo "Database created successfully";
    }else {
        echo "Error in creating database: " . mysqli_error($con);
    }
?>

Create a Table in Database

CREATE TABLE statement is used to create a table in MySQL database.

We have to add the CREATE TABLE statement to the mysqli_query() function to execute the command.

Example:
<?php
// Database connection establishment
    $con=mysqli_connect("example.com","alex","qwerty","my_database");
    
// Check connection
    if (mysqli_connect_errno($con)) {
    echo "MySQL database connection failed: " . mysqli_connect_error();
    }

// Create table query 
    $query = "CREATE TABLE users(
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
     name VARCHAR( 25 ) NOT NULL ,
     last_name VARCHAR( 25 ) NOT NULL ,
     age INT NOT NULL 
     )";

// Execute query
    if (mysqli_query($con,$query)) {
        echo "Table created successfully";
    }else {
        echo "Error in creating table: " . mysqli_error($con);
    }
?>
  • You must select the database before creating tables in the database.
  • To create tables in the database, the data type and maximum length of the field should be defined, e.g., varchar(20).
  • Each table should have a primary key field; a primary key is unique and key value cannot occur twice in one table.