What best practices should be followed when creating tables in a MySQL database using PHP, especially when defining primary keys and data types?

When creating tables in a MySQL database using PHP, it is important to define primary keys to uniquely identify each row in the table. Additionally, specifying appropriate data types for each column ensures data integrity and efficient storage. It is recommended to use the mysqli extension in PHP for database operations. 

<?php
$servername = "localhost";
$username = "username";
$password = "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 query to create a table with primary key and data types
$sql = "CREATE TABLE users (
  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 created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Keywords

MySQL PHP tables primary keys data types

Related Questions