PHP : MySQL Create Table

To create a table in a MySQL database using PHP, you can execute an SQL CREATE TABLE query. Below are examples using both mysqli and PDO to create a table in a MySQL database.

Using mysqli :

Here's an example using mysqli to create a table named users with three columns: id, name, and email.

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query to create a table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
)";

// Execute the query
if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

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

Using PDO:

Here's the same example using PDO :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query to create a table
    $sql = "CREATE TABLE IF NOT EXISTS users (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        email VARCHAR(50) NOT NULL
    )";

    // Prepare and execute the query
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    echo "Table 'users' created successfully";
} catch (PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Replace "your_username", "your_password", and "your_database" with your actual database credentials.
• The IF NOT EXISTS clause ensures that the table is created only if it doesn't already exist, preventing errors if the table has already been created.
• The id column is defined as an auto-increment primary key, which means it will automatically assign a unique value to each row.
• The name and email columns are defined as VARCHAR with a maximum length of 50 characters and cannot be NULL.
• Always sanitize user input and use prepared statements to prevent SQL injection attacks.

Running this script will create the users table in your specified database. If the table already exists, it will not be recreated, ensuring that existing data is preserved.