PHP MySQL: Create a New Table

Summary: in this tutorial, you will learn how to use PHP to create a table in MySQL server by using PDO API.

The following are the steps to show you how to create a new table in MySQL from PHP:

First, connect to the MySQL server.
Second, execute a CREATE TABLE statement to create a new table.

Creating a table

We will create a new table called tasks in the todo database using the following CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    completed BOOL DEFAULT false
);Code language: SQL (Structured Query Language) (sql)

The following create-table.php script creates the tasks table in the todo database:

<?php
require_once 'config.php';

$sql = 'CREATE TABLE IF NOT EXISTS tasks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            completed bool default false
        )';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->exec($sql);
} catch (PDOException $e) {
    die($e);
}Code language: PHP (php)

How it works.

First, include the database configuration from the config.php file:

require_once 'config.php';Code language: PHP (php)

Second, initialize a variable that stores the CREATE TABLE statement:

$sql = 'CREATE TABLE IF NOT EXISTS tasks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            completed bool default false
        )';Code language: PHP (php)

Third, connect to the todo database on the MySQL server:

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);Code language: PHP (php)

Fourth, execute the SQL statement that creates the tasks table:

$conn->exec($sql);Code language: PHP (php)

If any exceptions occur, display the error message in the catch block.

If you run the create-table.php script using a web browser or from the terminal, it’ll create a new table called tasks in the todo database.

Verify the table creation

First, connect to the todo database on the MySQL server using the mysql client tool:

mysql -u root -p -D todoCode language: Shell Session (shell)

Second, show tables in the todo database:

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| Tables_in_todo |
+----------------+
| tasks          |
+----------------+
1 row in set (0.00 sec)Code language: Shell Session (shell)

The output indicates that the tasks table has been created successfully.

Summary

Call the exec() method of the PDO object to execute a query that creates a table in MySQL.

Was this tutorial helpful?