Latest web development tutorials

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
)

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 ();
?>


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);
?>


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;
?>