PHP : MySQL Create Table

Data in a MySQL database is stored in tables. A table has a unique name, and it is made of columns (fields) and rows (records). In this chapter we create the users table in the student database.


Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints
);

Common data types

Data typeUse
INTWhole numbers like 1, 25 or 1000
VARCHAR(n)Short text up to n characters, like a name or email
TEXTLong text like a description
DECIMAL(10,2)Numbers with decimals, like a price
DATE / DATETIMEA date or a date with time
TIMESTAMPDate and time, can be filled automatically

Common column settings

  • PRIMARY KEY: a unique value for each row, usually the id.
  • AUTO_INCREMENT: MySQL gives the next number by itself (1, 2, 3…).
  • NOT NULL: the column can not be empty.
  • UNIQUE: no two rows can have the same value, for example the email.

Create a table using PHP

We use the db-connection.php file from the connect chapter, so the student database is already selected.

<?php
require_once('db-connection.php');

$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
)";

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

mysqli_close($conn);
?>

Output:

Tutorials Class - Output Window

Table users created successfully.

What the code does

  • It includes the connection file and stores the CREATE TABLE query in $sql.
  • mysqli_query() runs the query and returns true if the table is created.
  • IF NOT EXISTS avoids an error when the table is already there.

The mobile is saved as VARCHAR (text), not INT, because a phone number can start with 0 or have a + sign, and you never calculate with it.

Note: You can see the structure of the table in phpMyAdmin, or with the SQL command DESCRIBE users;.

Summary

  • CREATE TABLE creates a table with its columns and data types.
  • Use a primary key with AUTO_INCREMENT for the id.
  • Next chapter: PHP : MySQL INSERT Statement.

Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
Display MySQL data in an HTML table using mysqli_fetch_array()
Interview Questions & Answers
What is the difference between mysqli_fetch_array() and mysqli_fetch_object()?
How to Fix Incorrect format parameter error when importing large database in PHPMyAdmin?
In how many ways can we fetch data from a MySQL result set in PHP?
How to encrypt and decrypt data stored in a MySQL table?
What is the difference between mysqli and PDO in PHP?