Curriculum
Course: PHP Basic
Login

Curriculum

PHP Basic

PHP Install

0/1

PHP Casting

0/1

PHP Constants

0/1

PHP Magic Constants

0/1

PHP Operators

0/1

PHP Reference

0/276
Text lesson

MySQL Create Table

Create a MySQL Table Using MySQLi and PDO

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

We will create a table named “MyGuests” with five columns: “id”, “firstname”, “lastname”, “email”, and “reg_date”.

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
)

Notes on the table above:

  • The data type defines the kind of data each column can store. For a complete list of available data types, refer to our Data Types reference.

  • After specifying the data type, you can add optional attributes for each column:

    • NOT NULL: Ensures that each row must have a value for this column; null values are not permitted.
    • DEFAULT value: Sets a default value for the column if no other value is provided.
    • UNSIGNED: For numeric types, restricts the data to positive numbers and zero.
    • AUTO_INCREMENT: Automatically increments the column’s value by 1 for each new record.
    • PRIMARY KEY: Uniquely identifies each row in the table. Typically used with ID numbers and often combined with AUTO_INCREMENT.

Each table should have a primary key column (in this example, the “id” column) with unique values for each record.

The following examples demonstrate how to create the table in PHP:

Example (MySQLi Object-oriented)

<?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 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 ($conn->query($sql) === TRUE) {
  echo “Table MyGuests created successfully”;
else {
  echo “Error creating table: “ . $conn->error;
}

$conn->close();
?>

Example (MySQLi Procedural)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;

// 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);
?>

Example (PDO)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDBPDO”;

try {
  $conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // 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
  )”
;

  // use exec() because no results are returned
  $conn->exec($sql);
  echo “Table MyGuests created successfully”;
catch(PDOException $e) {
  echo $sql . “<br>” . $e->getMessage();
}

$conn = null;
?>