PHP MySQL: Create a New Table (original) (raw)

Skip to content

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=newPDO("mysql:host=conn = new PDO("mysql:host=conn=newPDO("mysql:host=host;dbname=$dbname", username,username, username,password); conn−>exec(conn->exec(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", <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">username, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.625em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">u</span><span class="mord mathnormal" style="margin-right:0.02778em;">ser</span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mpunct">,</span></span></span></span>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?