How To Use SQL CREATE TABLE STATEMENT With Example In PHP

Posted By Bajarangi soft , Posted On 18-09-2020

CREATE TABLE statement is used to create a new columns and datatype of each column for table in a database.

Syntax SQL CREATE TABLE Statement

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( columns )
);

Different data type should give for each column while creating table in database

Example(1)

CREATE TABLE Employee(
EmployeeID   INT              NOT NULL,
FirstName  VARCHAR (20)     NOT NULL,
LastName   VARCHAR (20)     NOT NULL,
AGE  INT              NOT NULL,
ADDRESS   VARCHAR (20)     NOT NULL,
SALARY   DECIMAL (18, 2),
PRIMARY KEY (EmployeeID))

SQL CREATE TABLE Statement creates a table called "Employee" in the management database that contains different datatype columns:

EmployeeID and AGE with integer datatype.
FirstName , LastName,ADDRESS with varchar datatype.
SALARY with decimal datatype.
EmployeeID will be auto incremented on the insertion of data to Employee table that done by PRIMARY KEY (unique identification for each row).

Use DESC command that display the "Employee" table view as below so that we can verify that table after created in database

Field	Type	Null	Key	Default
EmployeeID	int(11)	NO	PRI	NULL
FirstName	varchar(20)	NO		NULL
LastName	varchar(20)	NO		NULL
AGE	int(11)	NO		NULL
ADDRESS	varchar(20)	NO		NULL
SALARY	decimal(18,2)	YES		NULL

Example to display Employee table

DESC Employee;

We can also copy data from another table using CREATE TABLE.
Syntax for coping data from another table using CREATE TABLE

CREATE TABLE new_tablename AS
SELECT column list
FROM existing_tablename
WHERE ....;

Example(2)

CREATE TABLE Employee_details AS
SELECT FirstName, ADDRESS
FROM Employee;

SQL statement creates a new table called "Employee_details " for this table data copied from Employee

2.Complete code of SQL CREATE TABLE Statement in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use  SQL CREATE TABLE Statement</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <br>
    <br>
    <div class="text-center">
        <h1>SQL CREATE TABLE Statement</h1>
    </div>
    <br>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "management");
        if($connect === false){
            die("ERROR: Could not connect. " . mysqli_connect_error());
        }
        $sql_1 = "CREATE TABLE Employee(
                        EmployeeID   INT              NOT NULL,
                        FirstName  VARCHAR (20)     NOT NULL,
                        LastName   VARCHAR (20)     NOT NULL,
                        AGE  INT              NOT NULL,
                        ADDRESS   VARCHAR (20)     NOT NULL,
                        SALARY   DECIMAL (18, 2),
                        PRIMARY KEY (EmployeeID))";
       $sql_2="CREATE TABLE Employee_details AS SELECT FirstName, ADDRESS FROM Employee;"

        if(mysqli_query($connect, $sql_1)){
            echo "<br><h1 class='text-success'>Employee table created successfully.</h1>";
        } else{
            echo "ERROR: Could not able to execute $sql_1. " . mysqli_error($connect);
        }
        mysqli_close($connect);
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post