Home » MySql » MySQL Create Table

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.

1. PHP MySQL Create Table
2. Create Table Using Procedural procedure
3. Create Table Using Object Oriented procedure
3. Create Table Using PDO procedure

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.

Web Developement Courses
BootStrap Snippets
JS Keywords
Form Events
CSS Navbar
JS Objects
Cookies
Forms
Icons
Buttons
Progress
Footer
Errors

PHP MySQL Create Table

What is a table?

Contents

MySQL Create Tables

The general syntax for creating a table is:

Syntax for Creating Table

Before going forward you must remember these points:

MySQL Create Table using Procedural Method

Output

MySQL Create Table using Object Oriented Method

Output

PHP MySQLi Create Table using PDO Method

Output