How To Use SQL CREATE TABLE STATEMENT With Example In PHP

admin_img 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.

SQL CREATE TABLE Statement

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 Extra
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