PHP MySQL Create Table

In the previous tutorial, we learned about how to create a database through a PHP application. Now we will get to the next step, we will look at the MySQL tutorial to create tables in a database through a PHP application. Let's see first, what is a table.

What is a table?

In a relational database, a table is a collection of data elements that store data in the form of rows and columns. The place where a row and a column intersects is called a cell. We will now look at the procedure and the SQL query to create a table in an SQL database and will also analyze the Query and its content. After that we will look at the different ways to implement the table creation Query, using different PHP Database extensions.



MySQL Create Tables

The SQL query is a bit complex than the database one but, it is not very difficult. In the query, you should know all the data types used in the MySQL version for a particular type of data because it is necessary to specify data types of the columns. The data types indicate the type of data to be entered in the column. For example, A Name column will have CHAR data type, date column will have a DATE data type, etc. Other than that, the maximum size of the data to be entered is also specified in the query.

After these data types, if you want to add some CONSTRAINTS to the columns, then you can specify those too. Constraints are some conditions which the data has to follow to get accepted in the table. For example: If the constraint NOT NULL is applied to a column then that column should not be left empty, otherwise, the record will not be stored.

The 'CREATE TABLE' statement is used to create a table in a database.

The general syntax for creating a table is:

Syntax: 
CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ....
);

Let's use this syntax in an example, then you will understand it clearly. Observe the example yourself, and then we will also explain to you all the constraints and data types used in the SQL query:


Syntax for Creating Table

Example

<?php /* Syntax for creating Table */ CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE ); ?>

EXPLANATION: The SQL Query given above has some constraints and datatypes. This query creates a table named persons. This table will have the columns id, name, birth_date, phone. After naming the columns, the size of the data to be stored is specified, like you can see name VARCHAR(50) and phone VARCHAR(15) indicates the maximum characters the column can hold. The fields id and birth_date don't have any size specified because these won't require more than the default size of the data types.

You will learn more about it in the SQL tutorial. Let's see some keywords and constraints used in this example.

Data Type Description
INT The INTEGER data type accepts numeric values. It stores any integer value between -2147483648 to 2147483647.
DECIMAL It stores decimal values with exact precision i.e. with decimals included.
CHAR It stores fixed-length strings of alphabetic characters with a maximum size of 8000 characters.
VARCHAR It holds a variable length string that can contain alphabets, numbers, and special characters with a maximum size of 8000 characters.
TEXT It Stores strings with a maximum size of 2GB data.
DATE It stores date values in the YYYY-MM-DD format.
DATETIME It stores combined date/time values in YYYY-MM-DD HH:MM:SS format.
Constraint Description
PRIMARY KEY This constraint sets the column as the primary key of the table.
NOT NULL It indicates that the value stored in this column can never be NULL.
UNIQUE This constraint indicates that all the values will be distinct from each other.
AUTO_INCREMENT It indicates that the values will be incremented by one with each record entered. It is used with numeric values specially with primary keys.

Now we will see the implementation of SQL table creation query with each extension of MySQL, i.e., MySQLi and PDO extension.

Before going forward you must remember these points:

  • Before creating tables in the database, the database should be selected.
  • Don't forget to specify the data type and maximum length of the field, e.g., varchar(30).
  • Each table should have a primary key.

MySQL Create Table using Procedural Method

Example

<?php /* MySQL server connection. */ $link = mysqli_connect("localhost", "root", "", "demo"); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } /* Attempt create table query execution */ $sql = "CREATE TABLE persons( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(70) NOT NULL UNIQUE )"; if(mysqli_query($link, $sql)){ echo "Table created successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } /* Close connection */ mysqli_close($link); ?>

Output

Table created successfully.


MySQL Create Table using Object Oriented Method

Example
<?php /* MySQL server connection. */ $mysqli = new mysqli("localhost", "root", "", "demo"); // Check connection if($mysqli === false){ die("ERROR: Could not connect. " . $mysqli->connect_error); } /* Attempt create table query execution */ $sql = "CREATE TABLE persons( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(70) NOT NULL UNIQUE )"; if($mysqli->query($sql) === true){ echo "Table created successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . $mysqli->error; } /* Close connection */ $mysqli->close(); ?>

Output

Table created successfully.

PHP MySQLi Create Table using PDO Method

Example
<?php /* MySQL server connection. */ try{ $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", ""); /*Set the PDO error mode to exception */ $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e){ die("ERROR: Could not connect. " . $e->getMessage()); } /*Attempt create table query execution */ try{ $sql = "CREATE TABLE persons( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(70) NOT NULL UNIQUE )"; $pdo->exec($sql); echo "Table created successfully."; } catch(PDOException $e){ die("ERROR: Could not able to execute $sql. " . $e->getMessage()); } /*Close connection */ unset($pdo); ?>

Output

Table created successfully.




Color Picker

List of color names supported by all browser »



Character Code

List of character codes to display special character »



Language Code

List of all language supported by all browser »



Country Code

List of Country code supported by all browser »

Latest Updates

PHP Introduction

How to Run a PHP program in Xampp?

How can I create a phpinfo.php page?

How to Define a Variable in PHP

PHP Echo and Print Statements

Working with PHP Operators

Understanding the PHP Data Types

How to Write Comments in PHP

How to Define a Constant in PHP

PHP if...else...elseif Statements