PHP MySQL Create Table
A database table has its own unique name and consists of columns and rows. After creating a database, you need to create tables to store your data.
1. The CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in MySQL. You must define the column names and the type of data each column can hold (e.g., integers, strings, dates).
Common Column Constraints:
- NOT NULL: Each row must contain a value for that column.
- DEFAULT: Sets a default value if none is provided.
- UNSIGNED: Used for number types, limits data to non-negative numbers.
- AUTO_INCREMENT: Automatically increases the value by 1 for every new record (used for IDs).
- PRIMARY KEY: Uniquely identifies each row in the table.
2. Create Table using MySQLi (Object-Oriented)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>
3. Create Table using PDO
<?php
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL
)";
$conn->exec($sql);
echo "Table MyGuests created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Pro Tip: Always include an ID column with AUTO_INCREMENT PRIMARY KEY. This makes it much easier to update or delete specific records later on.