1. Home
  2. Php
  3. PHP MySQL Create Table Example

 PHP MySQL Create Table Example PHP

PHP MySQL Create Table Example  


PHP MySQL Create Table Example

PHP MySQL Create Table Example

A database table has its own unique name and consists of columns and rows.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named "MyGuests", with five 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 on the table above:

The data type specifies what type of data the column can hold.

After the data type, you can specify other optional attributes for each column:

  • NOT NULL - Each row must contain a value for that column, null values are not allowed
  • 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
  • AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
  • PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the "id" column). Its value must be unique for each record in the table.

Example

  1. <?php  
  2. $host = 'localhost:3306';  
  3. $user = '';  
  4. $pass = '';  
  5. $dbname = 'test';  
  6.   
  7. $conn = mysqli_connect($host, $user, $pass,$dbname);  
  8. if(!$conn){  
  9.   die('Could not connect: '.mysqli_connect_error());  
  10. }  
  11. echo 'Connected successfully<br/>';  
  12.   
  13. $sql = "create table emp5(id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,  
  14. emp_salary INT NOT NULL,primary key (id))";  
  15. if(mysqli_query($conn, $sql)){  
  16.  echo "Table emp5 created successfully";  
  17. }else{  
  18. echo "Could not create table: ". mysqli_error($conn);  
  19. }  
  20.   
  21. mysqli_close($conn);  
  22. ?>  

Output:

Connected successfully
Table emp5 created successfully

 

The following examples shows how to create the table in PHP:

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check 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();
?>
 

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check 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;
?>

Download free E-book of PHP

#askProgrammers
Learn Programming for Free

Join Programmers Community on Telegram

Talk with Experienced Programmers

Just drop a message, we will solve your queries