• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Create Table

← Previous Next →

Once you have created a database in MySQL, the next essential step is to create tables. Tables store your actual data in a structured format using rows and columns. In PHP applications, tables act as the foundation for all data operations such as inserting, updating, retrieving, or deleting records.

This tutorial explains how to create tables in MySQL using SQL commands and PHP scripts, including best practices, examples, and tips for error handling.

What is Table?

A table is like a spreadsheet inside your database:

  • Columns (fields) define the type of data stored, such as name, age, or email.

  • Rows (records) contain actual data entries.

  • Primary keys uniquely identify each record, ensuring no duplicates exist.

For example, a students table might look like this:

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com

Creating a Table Using SQL

To create a table manually, use the CREATE TABLE command. Here’s an example:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100)
);

Explanation:

  • id INT AUTO_INCREMENT PRIMARY KEY: Creates a unique identifier for each student.

  • name VARCHAR(50) NOT NULL: A required text field for the student’s name.

  • age INT NOT NULL: A required number field for age.

  • email VARCHAR(100): Optional text field for email.

Key Points When Creating Tables

  1. Choose meaningful column names – Use names that reflect the type of data.

  2. Define data types correctly – INT for numbers, VARCHAR for text, DATE for dates.

  3. Use constraints – PRIMARY KEY, UNIQUE, NOT NULL to enforce rules.

  4. Set character sets – Use utf8mb4 for multilingual support:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
) CHARACTER SET utf8mb4;

Creating Tables Using PHP (MySQLi)

You can create tables directly from PHP using MySQLi.

Procedural Style Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100)
)";

if (mysqli_query($conn, $sql)) {
    echo "Table created successfully!";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Object-Oriented Style Example

<?php
$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100)
)";

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

$conn->close();
?>

Creating Tables Using PDO

PDO is another secure and flexible method:

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        age INT NOT NULL,
        email VARCHAR(100)
    )";

    $conn->exec($sql);
    echo "Table created successfully!";
} catch(PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
}

$conn = null;
?>

Viewing Tables

After creating a table, you can check it using:

SHOW TABLES;

To see the structure of a table:

DESCRIBE students;

This shows column names, data types, and constraints.

Best Practices When Creating Tables

  1. Use IF NOT EXISTS – Prevent errors if the table already exists.

  2. Define primary keys – Every table should have a unique identifier.

  3. Use NOT NULL for mandatory fields – Avoid incomplete records.

  4. Use meaningful data types – Prevent wasted space and optimize queries.

  5. Set proper character sets – Ensure support for special characters.

  6. Plan for scalability – Anticipate future fields and relationships.

Common Errors

Error Cause Solution
Table already exists Duplicate creation Use IF NOT EXISTS
Access denied Incorrect user privileges Grant appropriate privileges
Syntax error Typo in SQL Check column names, commas, brackets
Unknown database Database not created Create the database first

Summary of the Tutorial

  • Tables are essential components of a database, storing data in rows and columns.

  • Use SQL commands or PHP scripts (MySQLi or PDO) to create tables.

  • Always define primary keys, data types, and constraints to ensure data integrity.

  • Best practices include using IF NOT EXISTS, proper naming conventions, and character sets.

  • After creating tables, use SHOW TABLES and DESCRIBE to verify structure.

Proper table creation sets a solid foundation for inserting, updating, and retrieving data, ensuring your PHP applications run smoothly and efficiently.

Practice Questions

  1. Write a SQL command to create a table named teachers with columns id, name, subject, and email.

  2. Modify the SQL command to include id as PRIMARY KEY with AUTO_INCREMENT.

  3. Create a table named courses with columns course_id, course_name, and duration using MySQLi procedural style in PHP.

  4. Write a PHP script using MySQLi object-oriented style to create a table students with columns id, name, age, and email.

  5. Write a PDO PHP script to create a table attendance with columns id, student_id, date, and status.

  6. Add a table named books with columns book_id, title, author, and price, ensuring it only creates the table if it doesn’t exist.

  7. Explain the difference between VARCHAR(50) and TEXT when defining a column in a table.

  8. Write a SQL command to show all tables in a database named school_db.

  9. Write a SQL command to describe the structure of a table named students.

  10. Create a reusable PHP file create_table.php that connects to MySQL and creates a table library_members with appropriate columns, then include it in another script to verify the table creation.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top