How To Use SQL ALTER STATEMENT With Example In PHP

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.

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