What are the best practices for creating MySQL tables using PHP files?

When creating MySQL tables using PHP files, it is important to follow best practices to ensure data integrity and security. One key practice is to properly sanitize user input to prevent SQL injection attacks. Additionally, using prepared statements can help prevent SQL injection and improve performance. It is also recommended to set appropriate data types, lengths, and constraints for each table column to ensure data consistency. 

<?php

// Establish connection to MySQL 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);
}

// Create table with prepared statement
$sql = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL
)";

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

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

?>

Keywords

MySQL PHP files table creation SQL queries data types

Related Questions