PHP MySQLi Create Table

The 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 show how to create a new table in a database using CREATE command in both procedural and object-oriented ways. To create a new table, we need the name of the table, the name of the fields and definitions of each field.

Syntax of Create Table

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

table_name: It specifies the name of the table.
column_name: It specifies the 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). Here, '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

Here is the given object-oriented way to create new table in PHP MySQLi -

<?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

Here is the given procedural way to create new table in PHP MySQLi -

<?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();
}
?>






Read more articles


General Knowledge



Learn Popular Language