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

Similar Reads

PHP Float: Understanding Float Precision in PHP

A PHP float is a kind of number that has a decimal point, like 2.45 or 0.11. It’s also called…

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 While Loop: Iterating Through Code Blocks

The PHP “while” loop is a simple tool that repeatedly runs a block of code as long as a certain…

PHP Boolean: Assigning True or False to a Variable

You can assign the boolean data type to PHP variables or use it as a direct value. This enables you…

PHP OOP Programming: A Complete Tutorial

A complex web application needs proper organization and maintenance to keep your code structured. This is where PHP Object-Oriented Programming…

Abstract Class in PHP: How It Works & Examples

Abstract class in PHP appeared to provide a way to define a structure for related classes and don't allow direct…

PHP array_diff_assoc: How to Compare Arrays with Keys

Array values may look the same, but keys can differ. The array_diff_assoc in PHP finds differences by both values and…

PHP implode Function: Join Array Values into a String

The implode function in PHP appeared to solve a common need—joining array elements into a single string. It helps format…

How to Update Documents in MongoDB Using PHP

Updating documents in MongoDB with PHP involves using the updateOne or updateMany methods from the MongoDB PHP library. These methods allow for precise updates,…

What Is PHP Used For? Meaning of PHP Programming Language

When you first step into web development, one name seems to pop up everywhere: PHP. The PHP programming language has…

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.