PHP
creates a MySQL table
After we have created the database in PHP, we need to create one or more data tables in the database.
A data table has a unique name and consists of rows and columns.
Create MySQL tables with MySQLi and PDO
Create TABLE statements are used to create MySQL tables.
Before creating a table, we need to use use myDB to select the database to operate on:
use myDB;
We'll create a table called "MyGuests" with 5 columns: "id," "firstname," "lastname," "email" and "reg_date":
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)
Notes in the table above:
The data type specifies what type of data a column can store.
For complete data types, please refer to
our data type reference manual.
After you set the data type, you can specify properties for each column with other options:
-
NOT NULL - Each row must contain a value (cannot be empty), and the null value is not allowed.
-
DEFAULT value - Set the default value
-
UNSIGNED - Uses unsigned numeric types, 0 and positive numbers
-
AUTO INCREMENT - Set the value of the MySQL field to automatically increase by 1 each time a new record is added
-
PRIMARY KEY - Set a unique identity for each record in the data table.
Typically, the PRIMARY KEY of a column is set to an ID value AUTO_INCREMENT with the user.
Each table should have a primary key (this list as "ID" column), and the primary key must contain unique values.
The following example shows how to create a table in PHP:
Instance (mysqli - object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Test connection
if ($conn->connect_error) {
die("Connection failed: " .
$conn->connect_error);
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " .
$conn->error;
}
$conn->close();
?>
Instance (mysqli - the oriented process)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create a connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Test connection
if (!$conn) {
die("Connection failed: " .
mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " .
mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;
dbname=$dbname", $username, $password);
set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
use exec() because no results are returned
$conn->exec($sql);
echo "Table MyGuests created successfully";
}
catch(PDOException $e)
{
echo $sql . "
<br>" .
$e->getMessage();
}
$conn = null;
?>
Now that your MySQL database has been created and has the tables you need, that's not enough, because there's no data in the data table, and the table is just a shelf, so we need to continue to refine the data table and insert some data for it!