PHP MySQL - ALTER to add Column

Let’s discuss how to add a new column to an existing table in MYSQL through PHP  in the XAMPP Server.

Consider the table - Medicine present in the database - hospital snapshot with the following structure:

 

 

We can see that there are only four columns.

ALTER in MySQL is used to add.drop and change the datatype of the column in a table.

 

SQL ALTER ADD COLUMN - Syntax

 

ALTER TABLE TABLE_NAME ADD name_of_the_column dtype;

As we know that columns refers to the column names that exist in the given table (TABLE_NAME).

name_of_the_column refers to the column name to be added to the table with particular datatype(dtype).

 

Approach-

1.Specify the credentials to create a connection object.
The credentials include XAMPP Server localhost name, username, password and database name.
$connecting_data =  new mysqli($server, $user, $pwd, $data);

2.Specify the SQL Query that includes the ALTER
$my_query = ALTER TABLE TABLE_NAME ADD name_of_the_column dtype;

3.Check if the column is added or not.
if ($connecting_data->query($my_query) === TRUE) {
  echo "Column added Succesfully!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

4. Close the Connection.
$connecting_data->close();

Example 1:

Add a new column - Address with varchar(200) as datatype to Medicine table.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "ALTER TABLE Medicine ADD Address varchar(200);";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Address column added Succesfully!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

 

Output:

So the column is added. let's check in xampp server.

Yes, the column  Address with datatype - varchar(200) is added to the Medicine table in hospital database.

 

Example 2:

Add a new column - Number with int as datatype to Medicine table.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "ALTER TABLE Medicine ADD Number int;";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Number column added Succesfully!!.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

Output:

So the column is added. let's check in xampp server.

Yes, the column - Number with datatype - int (Integer) is added to the Medicine table in hospital database.

 

Conclusion

In this article we seen how to add a new column using ALTER command in PHP-MySQL in XAMPP Server.