In this unit, we will learn how to create a MySQL table.
To create a table with the given name, we must have the create privilege for the table.
By default, tables are created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables.
Index
MySQL Create Table
- A table organizes the data records into rows and columns.
Read Also: PHP MySQLi Connect
What is a table?
- A table consists of one or more tables; each table is made up of rows & columns.
- A table is responsible for storing data.
- The SQL create table statement is used to create a table in the database.
Syntax
create table user ( user_id INT(6) unsigned AUTO_INCREMENT PRIMARY KEY, firstname varchar(30) NOT NULL, lastname varchar(30) NOT NULL, email varchar(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
After the data type, we can specify other attributes for columns:-
- NOT NULL: It is used when we do not want this field to be NULL. If a user tries to create a record with a NULL value, MySQL will raise an error.
- DEFAULT VALUE: Set a default value that is added when no other value is passed
- UNSIGNED: Used for number types, limits the stored data to positive numbers and zero
- AUTOINCREMENT: Tells MySQL to go ahead and add the next available number to the ID field.
- PRIMARY KEY: The PRIMARY KEY attribute is used to identify each record uniquely. You can use multiple columns separated by a comma to define a primary key.
Each table has a primary key column. Its value must be unique for record in the table.
The following examples show how to create a table in PHP.
1. MySQL Procedural Method
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection){
die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE user (
user_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if (mysqli_query($connection, $sql)) {
echo "Table user created successfully";
} else {
echo "Error creating table: " . mysqli_error($connection);
}
mysqli_close($connection);
?>2. Object-Oriented Method
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$ connection = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($connection->connect_error) {
die("Connection failed: " . $connection->connect_error);
}
// sql to create table
$sql = "CREATE TABLE user (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($connection->query($sql) === TRUE) {
echo "Table user created successfully";
} else {
echo "Error creating table: " . $connection->error;
}
$conn->close();
?>3. PDO Method
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
try {
$ connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to create table
$sql = "CREATE TABLE user (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
// use exec() because no results are returned
$connection->exec($sql);
echo "Table user created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$ connection = null;
?>Read More: PHP MySQL Insert Data
Conclusion
In this article, we learned all about the MySQL Create Table. A table organizes the data records into rows and columns. A table consists of one or more tables; each table is made up of rows & columns. We have also uploaded the basics of PHP under the section, PHP Tutorial.
Must Read: How to Delete Database in MySQL
We hope you found this article helpful 🙂
