MySQL Create Table

In this unit, we see how to create a table.

Create a table that creates 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.

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 in the table.
  • 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 being used because 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: It is used to define a column as a primary key. 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 = "user";

// 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 = "user";

// 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 = "user";

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;
?>

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.

I hope you find this article helpful 🙂

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
complete form validation in php
PHP Complete Form Validation

We can create and use forms in PHP. To get form data, we have to use PHP superglobals $_GET and...

Close