PHP & MySQL - Create Table

A database consists of one or more tables. These tables contain data using model of columns and rows. To create a table in MySQL database, the table name should be unique within the database system.

The CREATE TABLE statement is used to create a table in MySQL. Creating a table involves providing a name to the table and defining name and data type (e.g. varchar, integer, date, etc.) of each column. For example, to create a table named "Employee", with five columns: "EmpID", "Name", "City", "Age" and "Salary", the following query can be used:

CREATE TABLE Employee (
  EmpID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
)

In the above query, the data type specifies what type of data the column can hold. After the data type, optional attributes are provided for each column. The description of the attributes used here are as follows:

• NOT NULL - Ensures that a column cannot have a NULL value.
• UNIQUE - Ensures that all values in a column are different.
• PRIMARY KEY - Uniquely identifies each row/record in a column of a table. A combination of a NOT NULL and UNIQUE constraints.
• UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
• AUTO_INCREMENT - Automatically increases the value of the field by 1 each time a new record is added.

To connect to the MySQL database, mysqli_connect() function can be used. After establishing the connection to the database, mysqli_query() function can be used to perform the query on the database to create a new table.

Create a MySQL Table - Object-oriented style

The example below demonstrates how to create a table named "Employee" in object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//query for creating table
$sql = "CREATE TABLE Employee (
  EmpID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
)";

//executing the query
if (!$mysqli->query($sql)) {
  echo "Error creating table: ". $mysqli->error;
} else { 
  echo "Table Employee created successfully.";
}

//closing the connection
$mysqli->close();
?>

Create a MySQL Table - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//query for creating table
$sql = "CREATE TABLE Employee (
  EmpID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
)";

//executing the query
if (!mysqli_query($mysqli, $sql)) {
  echo "Error creating table: ". mysqli_error($mysqli);
} else { 
  echo "Table Employee created successfully.";
}

//closing the connection
mysqli_close($mysqli);
?>

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.