WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Create MySQL Tables

A data table has a unique name and consists of rows and columns.


Create MySQL tables using MySQLi and PDO

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

Before creating the table, we need to use use myDB to select the database to operate on:

use myDB;

We will create a table named "MyGuests" with 5 columns: "id", "firstname", "lastname", "email" and "reg_date":

CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )

Notes in the table above:

Data type specifies what type of data the column can store. For complete data types, please refer to our Data Types Reference Manual .

After you set the data type, you can specify attributes for other options for each column:

  • NOT NULL-Each row must contain a value (cannot be empty), null values are not allowed.
  • DEFAULT value-set default value
  • UNSIGNED-use unsigned numeric types, 0 and positive numbers
  • AUTO INCREMENT-set the value of a MySQL field to automatically increase by 1 each time a new record is added
  • PRIMARY KEY-Sets the unique ID of each record in the data table. Usually the column's PRIMARY KEY is set to the ID value, and AUTO_INCREMENT together.

Each table should have a primary key (this column is the "id" column). The primary key must contain a unique value.

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

Instance (MySQLi-Object Oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Detect connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Create a data table using sql $sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )"; if ($conn->query($sql) === TRUE) { echo "Table MyGuests created successfully"; } else { echo "Create data table error: " . $conn->error; } $conn->close(); ?>


Instance (MySQLi - Process-oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Detect connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } // Create a data table using sql $sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )"; if (mysqli_query($conn, $sql)) { echo "Data sheet MyGuests created successfully"; } else { echo "Create data table error: " . mysqli_error($conn); } mysqli_close($conn); ?>


Examples (PDO)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set PDO error mode for throwing exceptions $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Create a data table using sql $sql = "CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )"; // Using exec (), no results are returned $conn->exec($sql); echo "Data sheet MyGuests created successfully"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>