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.

MySQL Tables

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 emailemail 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.

Similar Reads

MongoDB CRUD with PHP: Create, Read, Update, and Delete

PHP gives us the ability to work with MongoDB, highlighting the importance of CRUD (Create, Read, Update, Delete) operations for…

PHP $GLOBALS: Access Global Variables with Examples

When you start working with PHP, you’ll soon need access to variables from multiple places in your code. For this,…

PHP Logical Operators | Understanding the 6 Logical Operators

The PHP logical operators are operands that can be used to check two or more expressions together. For examples (…

PHP fread Function: How to Read Files in PHP

In some cases, you need to handle a lot of data or simply try to open a file, or read…

PHP array_diff: How it Works with Examples

The array_diff function compares arrays and returns values that exist in the first array but not in the others in…

PHP array_intersect_ukey Function: How it Works with Examples

The array_intersect_ukey is used to compare arrays by keys with a custom function in PHP. It returns matches by keys…

PHP Global Variables & Superglobals: A Complete Guide

Variables in PHP are general elements used for data storage and other manipulations. Global variables belong to a particular category…

PHP rtrim Function: Remove Trailing Characters or Whitespace

You deal with user input, file reads, or formatted strings. PHP needed a way to remove extra characters from the…

PHP strpos Function: How it Works with Examples

Sometimes the string you expect is there, but strpos function gives a surprise in PHP. It may show the result…

PHP MySQL LIMIT Data: How to Optimize PHP Queries?

There are situations where you only need a specific number of rows returned. This is where the "LIMIT" clause in…

Previous Article

PHP MySQL Create Database: MySQLi & PDO Examples

Next Article

What Is PHP Used For? Meaning of PHP Programming Language

Write a Comment

Leave a Comment

Your email address will not be published. Required fields are marked *


Subscribe to Get Updates

Get the latest updates on Coding, Database, and Algorithms straight to your inbox.
No spam. Unsubscribe anytime.