PHP MySQL Create Tables

Previous Page Next Page

Creating Tables inside MySQL Database Using PHP

In the previous section, we learned how to establish a database on a MySQL server. Now, our focus shifts to creating tables within that database to store actual data. A table organizes data into rows and columns, providing structure to our stored information.

The SQL CREATE TABLE statement is essential for defining a new table in a MySQL database.

To create a table, we construct a SQL query using the CREATE TABLE statement. This query is then executed using the PHP mysqli_query() function, enabling us to successfully create our desired table structure.

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt 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.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Attempt 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.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt 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.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

The PHP code provided above creates a table named persons within the demo database. This table includes four columns: id, first_name, last_name, and email.

Each column in the table is accompanied by a data type declaration, specifying the type of data it can hold—whether it's an integer, string, date, and so forth.

Additionally, the SQL statement includes constraints (or modifiers) such as NOT NULL, PRIMARY KEY, AUTO_INCREMENT, which impose rules on the values allowed in these columns.

For a more detailed understanding of the syntax, data types, and constraints available in MySQL, refer to the tutorial on SQL CREATE TABLE statement.

Note: SQL statements can span multiple lines, but keywords, values, and expressions should not be interrupted by line breaks.

Tip: By setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION, PDO will throw exceptions whenever a database error occurs, aiding in error handling.

Previous Page Next Page