PHP MySQL Create Table

In this tutorial, we will learn to create a table in MySQL using PHP. You might have created a table from the MySQL CLI or other GUI tools such as MySQL workbench or phpMyAdmin. But in this tutorial, we will create a PHP program that will create a table when we run that script.

We can create a table by using either MySQLi or PDO. We will see both methods in this tutorial so that you can use any method that you find suitable for you.

Also read: Connecting PHP to MySQL Database

Create a MySQL Table Using MySQLi and PDO

We can create a table using the “CREATE TABLE” statement. Let’s see how to write a PHP script to create a table in MySQL.

We will create the following table-

CREATE TABLE emp(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

In the previous tutorial, we created a database “journaldev” which is used in this tutorial to create a table.

Creating a Table Using MySQLi (Procedural)

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = mysqli_connect($server, $username, $password, $dbname);
if (!$conn) {
    die("Connection Failed " . mysqli_connect_error());
}

$query = "CREATE TABLE emp(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
    );";

$res = mysqli_query($conn, $query);
if (!$res) {
    die("Query Failed " . mysqli_error($conn));
} else {
    echo "Table Created Successfully using mysqli procedural method";
}

mysqli_close($conn);
?>Code language: PHP (php)

Here, we have assigned the actual query to the variable ‘query’ and executed it using the mysqli_query() function.

When the query executes, it returns a boolean value which gets stored in the variable ‘res’ as specified in the program.

Then we check if the query returns false then stop the execution of the script using the die() method. If the query executes successfully, we print the message “table created successfully”.

In the end, we close the connection.

Creating a Table Using MySQLi (Object-Oriented)

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = new mysqli($server, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed" . $conn->connect_error);
}
$query = "CREATE TABLE emp(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
    );";

$res = $conn->query($query);
if (!$res) {
    die("Query Failed " . $conn->error);
} else {
    echo "Table Created Successfully using mysqli object-oriented method";
}


$conn->close();
?>Code language: PHP (php)

Creating a Table Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the PDO error mode to exception

    $query = "CREATE TABLE emp(
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        city VARCHAR(100)
        );";
    $conn->exec($query);
    echo "Table created successfully using PDO";
} catch (PDOException $e) {
    echo $e->getMessage();
}

$conn = NULL;
?>Code language: PHP (php)

Note that, we delete the previously created table before executing the new method. If we execute the first method and try to execute the next method, we will get an error that the table already exists.

Conclusion

In this tutorial, we have seen how to create a table from the PHP script in three different ways. You can choose any of the three methods in your project or sample programs. However, if you are a beginner, you won’t need to create a table from a PHP script. But if you take your project to an advanced level, you should implement this feature in your code so that you won’t need to create a table manually if you migrate the project to a new machine.