Creating Databases and Tables in MySQL via PHP
1. SQL Data Types — Choosing the Right Type
When designing a table, choosing the correct data type for each column is critical. It affects storage size, performance, and data integrity.
Numeric Types:
| Type | Range / Precision | Use Case |
|---|---|---|
TINYINT | -128 to 127 (1 byte) | Age, boolean (0/1) |
INT | -2.1B to 2.1B (4 bytes) | IDs, general integers |
BIGINT | Very large integers (8 bytes) | Transaction IDs, huge counts |
FLOAT | ~7 decimal digits | Approximate decimal values |
DECIMAL(p,s) | Exact decimal, up to p digits with s decimal places | Money, prices (use DECIMAL, not FLOAT, for money!) |
String Types:
| Type | Description | Use Case |
|---|---|---|
CHAR(n) | Fixed-length string, always n chars (padded with spaces) | Phone numbers, codes |
VARCHAR(n) | Variable-length string, up to n chars | Names, emails, usernames |
TEXT | Long variable-length string | Comments, blog content |
ENUM('v1','v2') | Only allows specified values | Status ('Active','Inactive') |
Date and Time Types:
| Type | Format | Use Case |
|---|---|---|
DATE | YYYY-MM-DD | Birthdates, event dates |
DATETIME | YYYY-MM-DD HH:MM:SS | Created/updated timestamps |
TIMESTAMP | Like DATETIME, auto-updates on changes | Last modified tracking |
TIME | HH:MM:SS | Duration |
2. Table Constraints
Definition: Constraints are rules enforced on columns to ensure data integrity — preventing invalid or inconsistent data from being stored.
| Constraint | Definition | Example |
|---|---|---|
NOT NULL | Column cannot be empty/NULL | name VARCHAR(50) NOT NULL |
PRIMARY KEY | Uniquely identifies each row; cannot be NULL or duplicate | id INT PRIMARY KEY |
AUTO_INCREMENT | Automatically generates the next sequential integer | id INT AUTO_INCREMENT |
UNIQUE | All values in the column must be distinct | email VARCHAR(100) UNIQUE |
DEFAULT | Provides a default value if none is specified | status VARCHAR(10) DEFAULT 'Active' |
FOREIGN KEY | Links to the primary key of another table | FOREIGN KEY (dept_id) REFERENCES departments(id) |
3. Creating a Database
<?php
$conn = new mysqli("localhost", "root", "");
if ($conn->connect_error) die("Connection Failed");
$sql = "CREATE DATABASE CollegeDB";
if ($conn->query($sql) === TRUE) {
echo "Database 'CollegeDB' created successfully!";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>4. Creating a Table (Complete Example)
<?php
$conn = new mysqli("localhost", "root", "", "CollegeDB");
$sql = "CREATE TABLE students (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
course VARCHAR(30) NOT NULL,
marks DECIMAL(5,2) DEFAULT 0.00,
enrolled DATE NOT NULL,
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table 'students' created successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>5. Modifying a Table with ALTER TABLE
After creation, you can modify a table's structure without losing data.
// Add a new column
$sql = "ALTER TABLE students ADD COLUMN phone VARCHAR(15)";
$conn->query($sql);
// Change a column's data type
$sql = "ALTER TABLE students MODIFY COLUMN marks DECIMAL(6,2)";
$conn->query($sql);
// Rename a column (MySQL 8.0+)
$sql = "ALTER TABLE students RENAME COLUMN phone TO mobile";
$conn->query($sql);
// Remove a column
$sql = "ALTER TABLE students DROP COLUMN mobile";
$conn->query($sql);6. Checking if a Table Exists Before Creating
$sql = "CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2)
)";
$conn->query($sql); // Safe — won't error if already exists