How to Create Table in MySQL Using PHP

In this tutorial, we will learn how to create a table in MySQL using PHP. Creating tables is one of the most important tasks when working with MySQL database in PHP.

In MySQL, a table is a structured format used to store data in rows and columns, similar to an Excel sheet. In the table, the column represents a field, and the row represents a record (one complete entry).

For example, a Students table may look like this:

id	name	email	age
1	John	john@gmail.com	20
2	Amit	amit@gmail.com	22

Here, we have created a table named “Students”, consisting of four columns and two rows. Each column represents a data type and row contains actual data.

Table name → Students
Columns → id, name, email, age
Rows → actual records

To insert such data into the table, you will have to create a table using SQL command.

Basic Syntax to Create Table in MySQL

The SQL provides the CREATE TABLE statement to create a new table in a MySQL database. This statement defines the table name and its columns along with their data types and optional constraints. The basic syntax to create a table in MySQL database is:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint
);

In the above syntax:

CREATE TABLE is a keyword used to create a new table in MySQL.
table_name specifies the name of the table.
column1, column2, column3 represents the names of columns.
datatype specifies the type of data, such as INT, VARCHAR, DATE, etc.
constraint are optional rules like NOT NULL, PRIMARY KEY, UNIQUE, etc.

Example: Simple Table for Students

CREATE TABLE students (
   id INT PRIMARY KEY,
   name VARCHAR(100),
   email VARCHAR(150),
   age INT
);

In this example, we have created a new table in the database using the CREATE TABLE statement.
[blocksy-content-block id=”12371″]

1. CREATE TABLE students

CREATE TABLE is a keyword is used to create a new table in the database.
students is the table name, which will store student-related data.

2. Column Definitions

Inside the parentheses ( ), we define the columns (fields) of the table. Each column has:

Column name
Data type
Optional constraints

3. id INT PRIMARY KEY

id is the column name. It will store the unique ID of each student.
INT is a data type, which stores integer numbers.
PRIMARY KEY is a constraint that uniquely identifies each row. It does not allow duplicate values as well as NULL values.
This means that every student must have a unique ID. No two students can have the same id.

Example of Valid Data:

id	name
1	Rahul
2	Aman

Example of Invalid Data:

id	name
1	Rahul
1	Aman

4. name VARCHAR(100)

name specifies the column name to store student’s name.
The data type VARCHAR(100) is a variable-length string, which can store names up to maximum 100 characters.
By default, this column allows duplicate name values and NULL values (empty values).

[blocksy-content-block id=”12121″]

5. email VARCHAR(150)

The email column stores the student’s email address.
The data type VARCHAR(150) is a variable-length string that can store email addresses up to a maximum of 150 characters.
By default, this column allows duplicate email values and NULL values (empty values).
This means multiple students can have the same email, and the email field can also be left empty.
If you want to store only unique email addresses, you should use the UNIQUE constraint: email VARCHAR(150) UNIQUE.
This statement will not allow to store duplicate email addresses. Each email value must be unique.
If you want to prevent both duplicate and NULL values, use email VARCHAR(150) UNIQUE NOT NULL.

6. age INT

The age column stores student age.
The data type INT allows you to store whole numbers.
By default, this column currently does not allow NULL and duplicate values.

Common MySQL Data Types

Here are some commonly used MySQL data types:

Numeric Data Types
- INT – Integer numbers
- FLOAT – Decimal numbers
- DOUBLE – Large decimal values2
String Data Types
- VARCHAR(size) – Variable-length string
- CHAR(size) – Fixed-length string
- TEXT – Long text
Date & Time
- DATE
- TIME
- DATETIME
- TIMESTAMP

How to Create a Table in MySQL Database Using PHP?

To create a table in MySQL database using PHP, first you will have to connect PHP with MySQL database. After connecting, you can create a new table in the database.
[blocksy-content-block id=”12153″]

Example 1: PHP Code to Create Table in MySQL (Object-Oriented MySQLi)

Inside the htdocs folder, create a new file with name create_table.php and paste this code in it.

<?php
// Include database connection.
require_once "connect.php";

// SQL query to create table.
$sql = "CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

// Execute query.
if ($conn->query($sql) === TRUE) {
    echo "Table created successfully. <br>";
} else {
    echo "Error creating table: " . $conn->error;
}

// Close connection.
$conn->close();
?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/create_table.php. If database connected, you will see the following output in your browser.

Output:

Table created successfully.

Check Created Table in Database Using phpMyAdmin

To check a created table in database using phpMyAdmin, follow all these steps:

Open your browser and type: http://localhost/phpmyadmin
Press Enter.
phpMyAdmin dashboard will open in your browser.
In the left-side panel, you will see a list of databases.
Click on your database name (Example: scientecheasy_db).
After clicking the database name, you will see all tables inside it.
Click on the table name. (Example: students)
You can now see:
- Structure tab → Shows columns (id, name, email, etc.)
- Browse tab → Shows inserted data
- Insert tab → Add new record
- SQL tab → Run queries

Note:

You do not need to click “New” to check an existing table. The “New” option is used to create a new database or table.

Explanation of PHP Code:

The line require_once "connect.php"; includes another PHP file named connect.php. This file contains the database connection code.
The require_once ensures the file is included only one time. If the file is missing, the script will stop execution with a fatal error.
Then, we have created a SQL statement and stored it inside the variable $sql.
The SQL statement CREATE TABLE IF NOT EXISTS students creates a table named students.
The IF NOT EXISTS clause in a CREATE TABLE statement prevents an error from being raised if a table with the specified name already exists.
The SQl statement created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP stores date and time automatically.
The DEFAULT CURRENT_TIMESTAMP clause automatically inserts the current date and time into a TIMESTAMP or DATETIME column when a new record is created, provided no specific value is given.
The line $conn->query($sql) executes the SQL command. Here, $conn is the database connection object and the query() is a MySQLi method.
If the table is successfully created, it will print a message “Table created successfully.”.
If an error occurs, it will display “Error creating table: Table ‘students’ already exists”.

Example 2: PHP Code to Create Table in MySQL (Using PDO)

<?php
// Include database connection.
require_once "config.php";

try {
// SQL query to create table.
   $sql = "CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      email VARCHAR(150) UNIQUE NOT NULL,
      age INT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   )";
// Execute query.
   $conn->exec($sql);
   echo "Users table created successfully. <br>";
} catch (PDOException $e) {
     echo "Error creating table: " . $e->getMessage();
}
// Close connection.
$conn = null;
?>

Explanation of PHP Code

The line require_once "config.php"; includes another PHP file named config.php. This file contains the PDO database connection code.
The require_once ensures that the file config.php is included only once. If the file is missing, the script stops execution with a fatal error.
We are using exception handling. If any error occurs inside try, it will be caught in catch block.
We have called exec() method using PDO connection object $conn.
The exec() method executes an SQL statement and returns the number of affected rows as an integer.
If an error occurs, it returns FALSE. However, if PDO is set to exception mode (PDO::ERRMODE_EXCEPTION), it throws a PDOException instead of returning FALSE.