PHP MYSQL - ALTER to change data type of Column

Let’s discuss how to change the data type of a column in a 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 CHANGE DATA TYPE - Syntax

 

ALTER TABLE TABLE_NAME MODIFY COLUMN name_of_the_column new_datatype;

name_of_the_column refers to the column name that is existing in the table in which datatype of this column is changed.

 

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 MODIFY COLUMN name_of_the_column new_datatype;

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

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

Example 1:

Let's change the datatype of the column - Quantity from int(1) to varchar(20).

<?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 MODIFY COLUMN Quantity varchar(20);";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Datatype of the Quantity column is updated.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

Output:

Datatype is changed to varchar(20). Let's check in XAMPP Server.

 

 

 

Example 2:

Let's change the datatype of the column - Cost from int(1) to varchar(220).

<?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 MODIFY COLUMN Cost varchar(220);";


if ($connecting_data->query($my_query) === TRUE) {
  echo "Datatype of the Cost column is updated.";
} else {
  echo "Error: " . $connecting_data->error;
}

$connecting_data->close();
?>

Output:

Cost column Datatype is changed to varchar(220). Let's check in XAMPP Server.

 

 

 

Conclusion

In this article we seen how to change the datatype of  the existing  column using ALTER command in PHP-MySQL in XAMPP Server.