Are there best practices for handling table creation and database setup in PHP scripts?

When creating tables and setting up a database in PHP scripts, it is recommended to use prepared statements to prevent SQL injection attacks and ensure data integrity. Additionally, it is good practice to check for existing tables before creating new ones to avoid conflicts and errors. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// Check if table exists before creating
$tableName = "users";
$sql = "SHOW TABLES LIKE '$tableName'";
$result = $conn->query($sql);

if ($result->num_rows == 0) {
    // Table does not exist, create it
    $sql = "CREATE TABLE users (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(30) NOT NULL,
        email VARCHAR(50) NOT NULL
    )";

    if ($conn->query($sql) === TRUE) {
        echo "Table created successfully";
    } else {
        echo "Error creating table: " . $conn->error;
    }
} else {
    echo "Table already exists";
}

$conn->close();
?>

Keywords

SQL database design PDO migration schema evolution

Related Questions