MySQL Create Table

PHP MySQL Create Table

Once your database is created, you need to create a Table to hold your data. A database table has its own unique name and consists of columns and rows.

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in MySQL.

When creating a table, you must specify the columns and the data type each column can hold (e.g., INT for numbers, VARCHAR for text, TIMESTAMP for dates).

Let's create a table named "MyGuests" with five columns: id, firstname, lastname, email, and reg_date:

<?php
$servername = "localhost";
$username = "root";
$password = "your_password";
$dbname = "myDB"; // Select the specific database!

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
  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 (mysqli_query($conn, $sql)) {
  echo "Table MyGuests created successfully";
} else {
  echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Important Table Constraints:

• PRIMARY KEY: Uniquely identifies each record in a database table.
• AUTO_INCREMENT: Automatically generates a unique number for each new record, starting at 1.
• NOT NULL: Ensures that a column cannot have a NULL (empty) value.