PHP MySQL Create Table Query With MySQLi Example Code and MySQLi_query Function

Create a MySQL Table Using MySQLi

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

We will create a table named “User”, with five columns: “id”, “firstname”, “lastname”, “email_id” and “age”:

	CREATE TABLE User (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	email_id VARCHAR(50),
	age INT
          )

Important points of the table above:

The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.

• NOT NULL – Each row must contain a value for that column, null values are not allowed
• DEFAULT value – Set a default value that is added when no other value is passed
• UNSIGNED – Used for number types, limits the stored data to positive numbers and zero
• AUTO INCREMENT – MySQL automatically increases the value of the field by 1 each time a new record is added
• PRIMARY KEY – Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

The following example shows how to create the table in PHP:

 <?php
$server = "localhost";
$user = "root";
$password = "";
$dbname = "elex_tutorial";

// Create connection
$connection = mysqli_connect($server, $user, $password, $dbname);
// Check connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE User (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	email_id VARCHAR(50),
	age INT
)";

if (mysqli_query($connection, $sql)) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . mysqli_error($connection);
}

mysqli_close($connection);
?>