How to Create Table in MySQL with PHP?

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.

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 Key
• name: Product name
• price: Product price
• stock: Number of items in stock
• created_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.