PHP MySQL Create Table

There is an individual name for a database table consisting of columns and rows.

To build a table in MySQL, use the CREATE TABLE statement.

We'll name the table "Employee" and give it three columns : "id", "name", and "city" :

CREATE TABLE Employee (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(30)
)

Notes on the table above :

The kind of data indicates which data can hold a column. Please refer to our data type reference for a detailed reference on all possible data types.

You can choose additional optional attributes for each column after data type:

• NOT NULL - Null values are not permitted in rows that have a value for that column.
• DEFAULT - Specify a default value added if no other value is given.
• UNSIGNED - Limits the data stored to positive integers and zeroes when used for number types.
• AUTO INCREMENT - When a new record is created, MySQL automatically increments the value of the field by 1.
• PRIMARY KEY - To identify the rows of a table in a unique way. The PRIMARY KEY column generally has an ID and is typically used with AUTO INCREMENT.

A main key column should be present in every table (in this case: the "id" column). For each record in the table, its value must be different.

Example (MySQLi Object-oriented)

The query() method from mysqli class is used to execute a SQL statement or query and it will not return any results from database table.

The query() gets two arguments which is a connection object and a string should contain a sql query.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// sql to create table
$sql = "CREATE TABLE Employee (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(30)
)";
if ($conn->query($sql) === TRUE) {
  echo "Table Employee created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}
// Close connection
$conn->close();
?>

Example (MySqli Procedural)

The mysqli_query() method used to execute a SQL statement or query and it will not return any results from database table.

The mysqli_query() gets two arguments which is a connection object and a string should contain a sql query.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE Employee (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(30)
)";
if (mysqli_query($conn, $sql)) {
  echo "Table Employee created successfully";
} else {
  echo "Error creating table: " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>

Example (PDO)

The exec() method from PDO class is used to execute a SQL statement and it does not return any results from database.

The exec() takes an argument which is a sql query as string.

<?php
try {
  // Create connection
  $conn = new PDO("mysql:host=localhost;dbname=myDB", "username", "password");
  // sql to create table
  $sql = "CREATE TABLE Employee (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(30)
)";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Table MyGuests created successfully";
} catch(PDOException $e) {
  echo $sql . "<br />" . $e->getMessage();
}
// Close connection
$conn = null;
?>