One of the first tasks you will likely encounter is creating a table in databases. Tables are the main part of any database, giving your data a clear and organized structure. So building a blog, launching an e-commerce site, or working on a custom project needs you to understand how to create MySQL tables with PHP.
Table of Content
following sections, I will show you how to create a MySQL table and also you will be able to design structures of database tables in PHP.
Understanding MySQL Tables
The MySQL table is a structured format for storing data in rows and columns. It is something similar to a spreadsheet where:
- Columns are things like vertical containers that represent the data types (e.g., name, age, email).
- Rows store individual entries or records.
You just need to define the structure of the data when you create the tables, such as column names and data types. You also must define constraints such as primary keys or unique values.

Let’s take a look at the following section to see how to create a MySQL table.
Create a Table in MySQL using PHP
The first thing you have to do is to create a table then you can establish the connection to your MySQL database. You can use the mysqli_connect() function to link PHP with MySQL.
Here is an example:
$server = "localhost";
$username = "root";
$password = "";
$database = "my_database";
// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
Now, it is time to write the SQL query to do that. For example, let us say you want to create a table called users with the following columns:
id: A unique identifier (Primary Key).name: A string for the user’s name.email: A string for the user’s email address.created_at: A timestamp for when the user was added.
Here is the SQL query:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
But that will only work for SQL! You must use PHP to execute the SQL query. This can be done using the mysqli_query() function.
$sql = "CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table 'users' created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
// Close the connection
mysqli_close($conn);
You can also verify if the table was created or not. Just use phpMyAdmin or MySQL Workbench to check the structure of the users table in your database.
You should see the table with its columns and structure if everything is correct.
So, creating a basic table is only the beginning. You can improve your tables with additional constraints and data types. Here are some examples:
- Add Unique Keys: Ensure unique values for specific columns like
email:email VARCHAR(100) UNIQUE. - Default Values: Set default values for columns, such as a status:
status VARCHAR(20) DEFAULT 'active'. - Foreign Keys: Link tables together for relational data:
FOREIGN KEY (role_id) REFERENCES roles(id).
Let’s take a look at the following section to see an example.
Create a Products Table Example
Let us create a more complex table for an e-commerce project. The table products will have the following columns:
product_id: Primary Keyname: Product nameprice: Product pricestock: Number of items in stockcreated_at: Timestamp
Here is an example:
$sql = "CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table 'products' created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
As you see in this example, it shows you how to handle different data types like DECIMAL for prices and INT for stock levels.
Wrapping Up
Creating tables in MySQL using PHP is a basic skill every developer needs to learn. It is the first step to building dynamic applications with structured data.
In this article, you learned how to:
- Write and execute SQL queries to create tables
- Improve tables with constraints and additional features
Now that you know how to create tables, go ahead and start building your own database structures.
Similar Reads
PHP gives us the ability to work with MongoDB, highlighting the importance of CRUD (Create, Read, Update, Delete) operations for…
When you start working with PHP, you’ll soon need access to variables from multiple places in your code. For this,…
The PHP logical operators are operands that can be used to check two or more expressions together. For examples (…
In some cases, you need to handle a lot of data or simply try to open a file, or read…
The array_diff function compares arrays and returns values that exist in the first array but not in the others in…
The array_intersect_ukey is used to compare arrays by keys with a custom function in PHP. It returns matches by keys…
Variables in PHP are general elements used for data storage and other manipulations. Global variables belong to a particular category…
You deal with user input, file reads, or formatted strings. PHP needed a way to remove extra characters from the…
Sometimes the string you expect is there, but strpos function gives a surprise in PHP. It may show the result…
There are situations where you only need a specific number of rows returned. This is where the "LIMIT" clause in…