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 type | Use |
|---|---|
| INT | Whole numbers like 1, 25 or 1000 |
| VARCHAR(n) | Short text up to n characters, like a name or email |
| TEXT | Long text like a description |
| DECIMAL(10,2) | Numbers with decimals, like a price |
| DATE / DATETIME | A date or a date with time |
| TIMESTAMP | Date 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 EXISTSavoids 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 TABLEcreates a table with its columns and data types.- Use a primary key with
AUTO_INCREMENTfor 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() |