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:
use myDB;
We will create a table called "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 on the table above:
The data type specifies what type of data the column can store. For complete data types please refer to our Data Types Reference Manual.
After setting the data type, you can specify additional options for each column's properties:
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 the MySQL field to automatically increase by 1 each time a new record is added
PRIMARY KEY - Set a unique identifier for each record in the data table. Usually the PRIMARY KEY of the column is set to the ID value, used with AUTO_INCREMENT.
Every table should have a primary key (this column is the "id" column), and the primary key must contain unique values.
The following example shows how to create a table in PHP:
Example (MySQLi - Object Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = new mysqli ( $servername , $username , $password , $dbname ) ; // Detect connection if ( $conn -> connect_error ) { die ( " Connection failed: " . $conn -> connect_error ) ; } // Use sql to create a data table $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 " Error creating data table: " . $conn -> error ; } $conn -> close ( ) ; ?> Example (MySQLi - Procedure Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = mysqli_connect ( $servername , $username , $password , $dbname ) ; // Detect connection if ( ! $conn ) { die ( " Connection failed: " . mysqli_connect_error ( ) ) ; } // Use sql to create a data table $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 " The data table MyGuests was created successfully " ; } else { echo " Error creating data table: " . mysqli_error ( $conn ) ; } mysqli_close ( $conn ) ; ?> Example(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 ) ; // Use sql to create a data table $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 ) " ; // Use exec( ), no result is returned $conn -> exec ( $sql ) ; echo " The data table MyGuests was created successfully " ; } catch ( PDOException $e ) { echo $sql . " <br> " . $e -> getMessage ( ) ; } $conn = null ; ?>
