PHP MySQL Create table
A data table has a unique name, and there are rows and columns.
Create a MySQL table using MySQLi and PDO
CREATE TABLE statement to create a MySQL table.
We will create a table named "MyGuests", there are five 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
)
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:
You specify the data type column what type of data can be stored. Complete data types refer to our Data Types Reference Manual .
After setting the data type, you can not specify additional options for column attributes:
- NOT NULL - each row must contain a value (not null), null values are not allowed.
- DEFAULT value - set defaults
- UNSIGNED - Unsigned numeric types, 0 and positive numbers
- AUTO INCREMENT - MySQL field value is set automatically each time when new growth record 1
- PRIMARY KEY - data set that uniquely identifies each record in the table. PRIMARY KEY normally column is set to ID value for use with AUTO_INCREMENT.
Each table should have a primary key (present as "id" column), the primary key must contain unique values.
The following example shows how to create tables in PHP:
Examples (MySQLi - Object Oriented)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test 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 a data table error:" $ conn-> error;.
}
$ Conn-> close ();
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test 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 a data table error:" $ conn-> error;.
}
$ Conn-> close ();
?>
Examples (MySQLi - process-oriented)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = mysqli_connect ($ servername, $ username, $ password, $ dbname);
// Test 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 table MyGuests successfully created";
} Else {
echo "Create a data table error:" mysqli_error ($ conn);.
}
mysqli_close ($ conn);
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = mysqli_connect ($ servername, $ username, $ password, $ dbname);
// Test 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 table MyGuests successfully created";
} Else {
echo "Create a data table error:" mysqli_error ($ conn);.
}
mysqli_close ($ conn);
?>
Examples of (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 for an exception
$ 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
) ";
// Use exec (), no results are returned
$ Conn-> exec ($ sql);
echo "Data table MyGuests successfully created";
}
catch (PDOException $ e)
{
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDBPDO";
try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = $ dbname", $ username, $ password);
// Set the PDO error mode for an exception
$ 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
) ";
// Use exec (), no results are returned
$ Conn-> exec ($ sql);
echo "Data table MyGuests successfully created";
}
catch (PDOException $ e)
{
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>