PHP MySQL Create Tables

This is a guide that describes how to PHP MySQL Create Tables in database.

This article covers the basics of creating a table, including selecting the table name, specifying the column names and the data types they will contain, and setting up foreign keys and primary keys.

The article also shows how to use the mysqli and PDO extensions to run SQL queries in PHP.

Before we get started, it is important to note that you will need access to a MySQL database to create tables. You can either set up your own local database or use a hosted solution.

To create a tables in MySQL using PHP, follow below steps.

Table Of Content-

PHP MySQL Create Tables:

Connect MySQL Database Using PHP

The first step is to establish a connection to the MySQL database using PHP.

You can use the mysqli_connect() function to connect to the database.

Here’s an example:

$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Create connection
$conn = mysqli_connect($host, $user, $password, $database);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

In above example, we are connecting to a MySQL database on the local machine using the provided username and password.

We also specify the name of the database we want to connect to.

Define Table Structure

Once you have established a connection to the database, the next step is to define the structure of the table you want to create.

This includes specifying the name of the table, as well as the names and data types of the columns.

Below is an example of how to define a simple table structure:

// Define table structure
$table_name = "users";
$columns = array(
"id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY",
"first_name VARCHAR(30) NOT NULL",
"last_name VARCHAR(30) NOT NULL",
"email VARCHAR(50) NOT NULL",
"password VARCHAR(255) NOT NULL"
);

Above, we are defining a table named “users” with five columns: id, first_name, last_name, email, and password.

The id column is specified as an INT with a length of 6, and is set as the primary key.

The other columns are specified as VARCHAR data types with varying lengths.

PHP MySQL Create Table

Once you have defined the table structure, the next step is to create the table using SQL.

You can use the mysqli_query() function to execute SQL queries on the database.

Here’s an example of how to create the table using SQL:

// Create table
$mysqltable = "CREATE TABLE $table_name (" . implode(", ", $columns) . ")";
if (mysqli_query($conn, $mysqltable)) {
echo "Table created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

Important: Once you have created the table, you should close the database connection using the mysqli_close().

Create a MySQL Table Using MySQLi and PDO

A table can be created in MySQL by using the statement CREATE TABLE.

With below example, we will be creating a table called “Users“, which will have seven columns: “id“, “firstname“, “lastname“, “email“, “age“, “gender” and “reg_date“.

CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
age INT,
gender VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Above Code Summary:

Each column of the table can be specified with additional optional attributes after specifying the data type:

NOT NULL – There is a requirement that each row in the table contains a value for that column, no null values are permitted
DEFAULT value – This is used to set a default value that will be used when there is no other value passed in
UNSIGNED – This type is used for number types and limits the amount of data it stores to positive numbers and zeroes
AUTO INCREMENT – Once a new record is added to MySQL, the value of the field is automatically increased by 1 each time a new record is added to it.
PRIMARY KEY – This key is used to uniquely identify rows within a table by defining a primary key. When a column is set with a PRIMARY KEY value, it is often an ID number, and it is often used in conjunction with an AUTO_INCREMENT trigger.

Generally, every table should have a column that is assigned to the primary key (in this case, the column “id“).

Each record in the table must have a unique value for this column.

MySQLi Object-Oriented

Below is an example using MySQLi’s Object-Oriented method:

<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// checking connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to creating table
$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),
age INT,
gender VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table Users created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>

MySQLi Procedural

Below is an example using MySQLi’s Procedural method:

<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// Creating 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 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
age INT,
gender VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table Users created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

PDO Method

Below is an example using MySQL PDO(PHP Data Objects) method:

<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// Specify the exception error mode in the PDO
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to creating table

$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),
age INT,
gender VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)"; // Due to the fact that no results are returned, use exec() instead
$conn->exec($sql);
echo "Table MyGuests created successfully";
}
catch (PDOException $e) {
echo $sql . " " . $e->getMessage();
}
$conn = null;
?>

Example Explanation

Above code creates a table named “Users” with seven columns: “id“, “firstname“, “lastname“, “email“, “age“, “gender“, and “reg_date“.

The “id” column is set to be an auto-incrementing integer and used as the primary key.
The “firstname” and “lastname” columns are required and cannot be empty.
The “email” column is set to allow up to 50 characters, and the “age” and “gender” columns are of type integer and string, respectively.
The “reg_date” column is a timestamp that defaults to the current timestamp when a row is inserted, and updates to the current timestamp whenever the row is updated.

The code uses PDO to connect to the database, and if the connection is successful, it executes the SQL query to create the table.

If an error occurs, it catches the exception and displays the error message along with the SQL query that caused the error.

Once the query is executed, it displays a success message.

We value your feedback.