Basic PHP Tutorial

Advanced PHP Tutorial

PHP & MySQL

PHP Reference Manual

PHP MySQL Insert Data PHP MySQL Create Database

PHP MySQL Create Table

In this tutorial, you will learn how to create a table in the MySQL database using PHP.

Create a table in MySQL database using PHP

In the previous chapter, we learned how to create a database on the MySQL server. Now it's time to create some tables within the database, which will actually store the data. Tables organize information into rows and columns.

SQL CREATE TABLEThis statement is used to create a table in the database.

Let us use the CREATE TABLE statement to perform an SQL query, after which we will execute this SQL query to ultimately create the table by passing it to the PHP mysqli_query() function.

Example: Procedural approach

<?php
/*　Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user without password "root")　*/
$link = mysqli_connect("localhost", "root", "");
　
//　Check connection
if($link === false) {
　　　　die("Error: Unable to connect. " . mysqli_connect_error());
}
//Attempt to execute CREATE TABLE query execution
\$sql = "CREATE TABLE persons(
　　　　id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
　　　　first_name VARCHAR(30) NOT NULL,
　　　　last_name VARCHAR(30) NOT NULL,
　　　　email VARCHAR(70) NOT NULL UNIQUE
)";
if(mysqli_query($link, $sql)) {
　　　　echo "Table created successfully.";
}
　　　　echo "Error: Unable to execute $sql. " . mysqli_error($link);
}
　
//Close the connection
mysqli_close($link);
?>

Example: Object-oriented approach

<?php
/*　Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user without password "root")　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//　Check connection
if($mysqli === false) {
　　　　die("Error: Unable to connect. " . $mysqli->connect_error);
}
　
//Attempt to execute CREATE TABLE query execution
\$sql = "CREATE TABLE persons(
　　　　id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
　　　　first_name VARCHAR(30) NOT NULL,
　　　　last_name VARCHAR(30) NOT NULL,
　　　　email VARCHAR(70) NOT NULL UNIQUE
)";
if($mysqli-query($sql) === true) {
　　　　echo "Table created successfully.";
}
　　　　echo "Error: Unable to execute \$sql. ". \$mysqli->error;
}
　
//　Close the connection
\$mysqli->close();
?>

Example: PDO method

<?php
/*　Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user without password "root")　*/
try{
　　　　\$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　\$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
　　　　die("Error: Unable to connect. ". $e->getMessage());
}
　
//Attempt to execute CREATE TABLE query execution
try{
　　　　\$sql = "CREATE TABLE persons(
　　　　　　　　id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
　　　　　　　　first_name VARCHAR(30) NOT NULL,
　　　　　　　　last_name VARCHAR(30) NOT NULL,
　　　　　　　　email VARCHAR(70) NOT NULL UNIQUE
　　　　)";　　　　
　　　　\$pdo->exec(\$sql);
　　　　echo "Table created successfully.";
}
　　　　die("Error: Unable to execute \$sql. ". $e->getMessage());
}
　
//Close the connection
unset($pdo);
?>

The PHP code in the example creates a table named person in the demonstration database, which has four columns: id, first_name, last_name, and email.
Note that there is a data type declaration after each field name; this declaration specifies the data type that the column can store, such as integers, strings, dates, etc.

In the previous SQL statements, some additional constraints (also known as modifiers) were specified after the column names, such as NOT NULL, PRIMARY KEY, AUTO_INCREMENT, etc. Constraints define the rules for the allowed values in the columns.

Please see relatedSQL CREATE TABLE statementtutorial to get detailed information about syntax and the available data types and constraints in the MySQL database system.

Note: Newlines can appear in SQL statements as many times as needed, provided that no newline breaks keywords, values, expressions, and so on.

PHP MySQL Insert Data PHP MySQL Create Database