MySQLi Create Table

CREATE Command is used to create new objects in the database, by using this we can create a new table or a new database. Let's us how to create a new table in a database using CREATE command in both procedural and object oriented ways.

Syntax of Create Table

CREATE TABLE table_name (column_name1 column_type, column_name2 column_type, ....);

table_name - It specifies name of the table.
column_name - It specifies name of the column, each column name is separated by comma.
column_type - It is the type of data that the column hold.
databasename - It specifies the database name.

The following example creates a new table of name 'Employee', with five columns (emp_id, emp_name, email, phone and address). 'IF NOT EXISTS' is an optional part of the statement and is used to check the already existence of 'employee' table in the database. If this table already exists then this query will not create a new table employee. The table fields are listed with specific data type and size limit and separated by comma.

Object Oriented PHP MySQLi Create Table

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$query = "CREATE TABLE IF NOT EXISTS `employee` (
        `emp_id` int(11) NOT NULL AUTO_INCREMENT,
        `emp_name` varchar(50) NOT NULL,
        `email` varchar(50) NOT NULL,
        `phone` int(11) NOT NULL,
        `address` varchar(100) NOT NULL,
        PRIMARY KEY (`emp_id`)
        )";
if($conn->query($query)){
 echo 'Employee table created successfully';
} else {
 echo 'Error creating table employee '.$conn->error;   
}
?>

Procedural PHP MySQLi Create Table

<?php
$conn = mysqli_connect('hostname', 'username', 'password', 'databasename');
//Check for connection error
if(mysqli_connect_error()){
  die("Error in DB connection: ".mysqli_connect_errno()." - ".mysqli_connect_error());
}
$query = "CREATE TABLE IF NOT EXISTS `employee` (
        `emp_id` int(11) NOT NULL AUTO_INCREMENT,
        `emp_name` varchar(50) NOT NULL,
        `email` varchar(50) NOT NULL,
        `phone` int(11) NOT NULL,
        `address` varchar(100) NOT NULL,
        PRIMARY KEY (`emp_id`)
        )";
if(mysqli_query($conn, $query)){
 echo 'Employee table created successfully';
}
else {
 echo 'Error creating table employee : '.mysqli_error();
}
?>