How To Use SQL ALTER STATEMENT With Example In PHP

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

SQL ALTER TABLE statement is used to add, delete, or modify columns in an existing table.We can also use the ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Statement

Syntax for SQL ALTER TABLE Statement
 
Add Column

ALTER TABLE table_name
ADD column_name datatype;

DROP COLUMN

ALTER TABLE table_name
DROP COLUMN column_name;


MODIFY COLUMN

ALTER TABLE table_name
ALTER COLUMN column_name datatype;


ADD UNIQUE CONSTRAINT

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);


ADD PRIMARY KEY

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);



Lets Learn.
1.Create database and  table.
Student table.

StudentID StudentName Address Country Section Branch Percentage CourseID
1 Liam 23 Andrew road USA A ISE 66 5
2 Andrew 2415 Anthony  market str Germany A ISE     80 6
4 Ryan 18 Christopher sq. INDIA B EE 67 5
5 Charles 878 Eli Berguvsvägen 8 UK C ECE 52 8



Example (1)

ALTER TABLE student
ADD DOB date;

for above student table add new column as DOB 

Example (2)

ALTER TABLE student
DROP COLUMN DOB

dropped or deleted column DOB from student table


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

<!DOCTYPE html>
<html lang="en">
<head>
    <title>SQL ALTER 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>
    <br>
    <div class="text-center">
        <h1>SQL ALTER TABLE Statement</h1>
    </div>
    <br>
    <br>
    <br>
    
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "school_management");
        if($connect === false){
            die("ERROR: Could not connect. " . mysqli_connect_error());
        }
        $sql = "ALTER TABLE student ADD DOB date";
        if(mysqli_query($connect, $sql)){
            echo "<br><h1 class='text-success'>table alert successfully.</h1>";
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($connect);
        }
        mysqli_close($connect);
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post