MySQL Create Table

PHP: Creating MySQL Tables

Database tables are the fundamental structures for storing data in a relational database like MySQL. Each table has a unique name and consists of columns (defining the data fields) and rows (representing individual records).

Creating a MySQL Table using PHP (MySQLi and PDO)

The SQL CREATE TABLE statement is used to define and create a new table.

Example CREATE TABLE Statement:

Let’s create a table named MyGuests with columns for an ID, first name, last name, email, and registration date:

CREATE TABLE MyGuests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50) UNIQUE, -- Added UNIQUE constraint
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Explanation of Column Definitions:

• id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY:
• firstname VARCHAR(30) NOT NULL:
• lastname VARCHAR(30) NOT NULL: Similar to firstname.
• email VARCHAR(50) UNIQUE:
• reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP:

Choosing Data Types:

Selecting the correct data type (e.g., INT, VARCHAR, TEXT, DATE, DATETIME, TIMESTAMP, DECIMAL, BOOLEAN) is crucial for data integrity and efficiency. Refer to the MySQL documentation for a complete list and descriptions.

The following examples show how to execute this CREATE TABLE statement using PHP with MySQLi and PDO.

Example 1: MySQLi (Object-Oriented Style)

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB";           // Replace with your DB name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    error_log("Connection failed: " . $conn->connect_error);
    die("Database connection error. Please try again later.");
}

// Set character set (recommended)
$conn->set_charset('utf8mb4');

// SQL to create table
$sql = "CREATE TABLE MyGuests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50) UNIQUE,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

// Execute query
if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    // Handle error - check if table already exists, or other SQL error
    error_log("Error creating table: (" . $conn->errno . ") " . $conn->error);
    echo "Error creating table: " . $conn->error;
}

// Close connection
$conn->close();

?>

Example 2: MySQLi (Procedural Style)

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    error_log("Connection failed: " . mysqli_connect_error());
    die("Database connection error.");
}

// Set character set
mysqli_set_charset($conn, 'utf8mb4');

// SQL to create table
$sql = "CREATE TABLE MyGuests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50) UNIQUE,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

// Execute query
if (mysqli_query($conn, $sql)) {
    echo "Table MyGuests created successfully (Procedural)";
} else {
    error_log("Error creating table: (" . mysqli_errno($conn) . ") " . mysqli_error($conn));
    echo "Error creating table: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);

?>

Example 3: PDO

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDBPDO"; // Assuming a different DB for PDO example

try {
    // Create connection using PDO
    // Note: Database must exist before creating tables in it.
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);

    // Set the PDO error mode to exception (recommended)
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL to create table
    $sql = "CREATE TABLE MyGuests (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL,
        email VARCHAR(50) UNIQUE,
        reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";

    // Use exec() because no results are returned from CREATE TABLE
    $conn->exec($sql);

    echo "Table MyGuests created successfully (PDO)";

} catch(PDOException $e) {
    // Handle PDO errors (e.g., table already exists - SQLSTATE[42S01])
    error_log("PDO Error: " . $e->getMessage());
    echo "Database error: " . $e->getMessage(); // Show appropriate message
} catch(Exception $e) {
    error_log("General Error: " . $e->getMessage());
    echo "An unexpected error occurred.";
}

// Close connection
$conn = null;

?>

Important Considerations:

• Permissions: The database user specified in your PHP script must have the necessary privileges (e.g., CREATE) to create tables in the target database.
• Error Handling: Implement robust error handling. Check if the table already exists (MySQL error code 1050, SQLSTATE 42S01) or if other syntax errors occurred.
• Idempotency: Scripts that create tables might be run multiple times. Consider adding IF NOT EXISTS to your CREATE TABLE statement (CREATE TABLE IF NOT EXISTS MyGuests (...)) to prevent errors if the table already exists.
• Database Design: Carefully plan your table structure, relationships, and data types before creating tables. Good database design is crucial for application performance and maintainability.