PHP & MySQL - Create a database table Tutorial

For this tutorial, please start the MySQL module in your XAMPP control panel.

How to create a database table

Now that we have a database, we need a table with rows and columns to store data into. We create a table and specify which columns we want in that table.

The table we will create will be called Users and will have the following columns:

To create a table we use the CREATE TABLE sql query. We specify the table name and the column parameters between parentheses.

CREATE TABLE tableName (
    columnName columnType(columnSize) additional_parameters
)

CREATE TABLE Users (
    userID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50) NOT NULL
)

In the example above, we create a table called Users and specify which columns we want between the parentheses. Let’s go over the query step by step.

userID:

• userID: This is the name we want to give to column.
• INT(6): We want the userID to be an integer and be a maximum of 6 numbers long.
• UNSIGNED: Unsigned is used for numerical types and will limit the numbers to be only positive numbers and zero (no numbers like -1).
• AUTO_INCREMENT: Each time a user is added to the table, this number will automatically increment.
• PRIMARY KEY: A primary key is used to uniquely identify the rows in a table. A table can have multiple users with the name John, but each John will have their own unique number.

The primary key is often an identifying number and often used with auto increment.

name:

• name: The name of the column.
• VARCHAR(50): This field can have a string of various characters up to a maximum of 50.

email:

• email: The name of the column.
• VARCHAR(50): This field can have a string of various characters up to a maximum of 50.
• NOT NULL: This field may not be empty, it must have a value.

Each column definition is separated by a comma. It’s similar to how a regular function would accept parameter values.

Now that we understand the SQL query, let’s use PHP to execute it.

<?php

$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";

// 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 Users (
    userID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50) NOT NULL
)";

// create table and check result
if (mysqli_query($conn, $sql)) {
    echo "Table created successfully";
} else {
    echo "Could not create table. Error: " . mysqli_error($conn);
}

// close connection
mysqli_close($conn);

?>

If we see the message Table created successfully message on the page, we can go into the phpMyAdmin area to see if the table was created.

To access phpMyAdmin, go to localhost/phpmyadmin/index.php in your browser.

In the menu panel on the left, click on trainingDB to open up the database. There will be a table called Users. If we click on it, it will open up and show the three columns we created.

Summary: Points to remember

• We create a table with the CREATE TABLE query.
• MySQL/MariaDB needs one column to be a PRIMARY KEY.
• Column definitions are separated by a comma.