PHP MySQL Create Table

mysqli_query() function is used to create table in PHP.

What is a table?

A database table has its own unique name. Table consists of columns and rows.

A table has a specified number of columns, but can have any number of rows.

A table organizes the information into rows and columns.

Each table should have a primary key column. Its value must be unique for each record in the table.

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

Syntax

CREATE TABLE table_name (column_name column_type);

Creating tables in three different versions are described below.

Example – MySQLi Procedural

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

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

// sql to create table
$sql = "CREATE TABLE Students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

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

mysqli_close($conn);
?>

Notes

The PHP code in the above example creates a table named Students with five columns id, firstname, lastname, email and reg_date inside the myDB database.

Notice that each field name is followed by a data type declaration. The data type specifies what type of data the column can hold. After the data type, you can specify other optional attributes (Constraints) for each column.

Constraints define rules regarding the values allowed in columns.

• NOT NULL – Each row must contain a value for that column, null values are not allowed. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.
• DEFAULT value – Set a default value that is added when no other value is passed.
• AUTO INCREMENT – MySQL automatically increases the value of the field by 1 each time a new record is added.
• UNSIGNED – Used for number types, limits the stored data to positive numbers and zero.
• PRIMARY KEY – Used to uniquely identify the rows in a table. You can use multiple columns separated by a comma to define a primary key.

The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT.

Example – MySQLi Object-oriented

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// sql to create table
$sql = "CREATE TABLE Students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
  echo "Table Students created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Example – PDO

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // sql to create table
  $sql = "CREATE TABLE Students (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Table Students created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>