PHP MySQL Create Table


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


Create a MySQL Table Using MySQLi and PDO

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;
?>
You can also search for this these topics, php mysql create table, create a mysql table using mysqli and pdo, php mysql create table if not exists, php mysql create table foreignkey, php mysql create table example, php mysql create table uniquekey, php mysql create table with example, php mysql create table insert data, php mysql create table default value, php mysql create table mysqli object-oriented example, php mysql create table mysqli procedural example, php mysql create table pdo.