How to Modify the Definition or Data Type of a Column of a MySQL Table Using PHP

In this article, we show how to modify the definition and/or data type of a column of a MySQL table using PHP.

By definition, we are talking about things about the column such as the length of the column, whether it's NOT NULL, PRIMARY, UNIQUE, etc.

By data type, we are talking about whether it's VARCHAR, CHAR, INT, DATE, etc.

The general format of the MySQL query to modify a column of a MySQL table is shown below.

ALTER TABLE customers MODIFY middlename CHAR(1) NOT NULL;

So the above is the MySQL code to modify the definition or data type of a column named middlename to be of type CHAR(1).

Like, for example, say you had a column named middle name for a user to enter in his or her middle name. You now want to change it so that only the middle initial is stored. So when a user was typing in his or her full name, the data type more than likely was a VARCHAR(50) or something close to that. Now you only want the person's middle initial, so you change it to data type CHAR(1).

This is one example of many why you change the definition and/or data type of a column.

We'll now show how to tie this in with PHP.

<?php $user = "user"; $password = "password"; $host = "host"; $database = "dataabase"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $modifycolumn= mysql_query("ALTER TABLE customers MODIFY middlename CHAR(1) NOT NULL"); if($modifycolumn !== FALSE) { echo("The column has been renamed."); }else{ echo("The column has not been renamed."); } ?>

So, first, we must connect to the database.

After we do this, we create a variable named $modifycolumn. This $modifycolumn variable is set equal to a mysql_query() function that has the parameter "ALTER TABLE customers MODIFY middlename CHAR(1) NOT NULL". This changes the middlename column from whatever it was before to now be of type CHAR(1) and made NOT NULL.

So this is how you can modify the definition and/or data type of a column in a MySQL table using PHP.

Related Resources

How to Add a Column to a MySQL Table Using PHP

How to Add a Primary Key to a Column of a MySQL Table Using PHP

How to Make a Column of a MySQL Table Unique Using PHP

How to Rename a Column of a MySQL Table Using PHP

How to Delete a Column of a MySQL Table Using PHP

How to Set the Default Value of a Column of a MySQL Table Using PHP

How to Drop the Default Value of a Column Of a MySQL Table Using PHP