Chat with us, powered by LiveChat

This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

How to Create, Alter And Drop Table in MySQL

January 19, 2016

5 Min Read
Reading Time: 5 minutes

In the previous installment of this MySQL series, I discussed several common MySQL data types. In continuation from the previous part, we are going to learn about how we can CREATE table for our database, ALTER it and DROP it. For this, you need to start your XAMPP and locate to the folder which we previously created.

manage table in mysql

Create a Function

Add a new file in it and name it cad.php, cad means (create,alter,drop).

Now, first we will create a function in it that will execute the single handed queries like create, alter, drop, delete. Code for this function is:

function ExecuteQuery($sql,$name)
	$conn = OpenCon();

	if ($conn->query($sql) === TRUE) 
        return $name;
		$error = "Error creating table: " . $conn->error;

        return $error;

This function take 2 parameters

  1. $sql -> SQL query which you would like to execute.
  2. $name -> Message which you like to see when your query is executed.

query(sql) executes MySQL queries in PHP. If it is unsuccessful, it generates an error which can be see by $conn->error.

Creating Table in Database

Now open your index.php that we previously created in part 1 and remove require_once and add include ‘cad.php’ in it. Let us write a SQL query to create a table. Markup for creating a table in mysql is:

CREATE TABLE tablename {

column name datatype(size),

column name datatype(size),



Now ExecuteQuery as shown in code below:

include 'cad.php';

$sql = "CREATE TABLE MyGuests(
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP

$result = ExecuteQuery($sql,"Table Created Successfully");

echo $result;

Whenever you run index.php following code runs and creates a table in your database

Once the query is executed without error you will see “Table Created Successfully”. Now go to phpmyadmin and open database “practice” that we have created previously. You will see that your table MyGuests has been created in it.


When you click on MyGuests and go to structures you will see the 5 columns that we have created.


Alter Table

Suppose we have created our table and we have added an additional column in it or we have given a wrong data type to a column or we need to modify the size of data type of our column.  Instead of deleting a table and making a new one, we just make an alteration in our created table by using ALTER query in php. We will use the same ExecuteQuery function to execute our query. Markups for alter table are

  • ALTER TABLE table_name MODIFY COLUMN column_name datatype
  • ALTER TABLE table_name DROP COLUMN column_name
  • ALTER TABLE table_name ADD COLUMN column_name datatype

MODIFY will make alteration in an already created column in table, DROP will delete that column in table and ADD will add a new column in your table. Now open cad.php and add these functions in it.

I have created the separate function for every query so you don’t need to write the whole query every time. When you need, just add the table, column name and data-type and it will make query by itself and execute it. Following are the function:

  1. Modify Column
    function ModifyColumn($table,$columnname,$datatype)
    	$query = "ALTER TABLE ". $table."
    	MODIFY COLUMN ". $columnname ." ".$datatype;
    	$result = ExecuteQuery($query,"Column successfully modified");
    	return $result;

    This function takes three parameters table name, column name, and its datatype which you would like to modify.

  2. Add Column
    function AddColumn($table,$columnname,$datatype)
    	$query = "ALTER TABLE ". $table."
    	ADD ". $columnname ." ".$datatype;
    	$result = ExecuteQuery($query,"Column Added successfully");
    	return $result;
    This function takes three parameters table name, column name, and its data type which you would like to alter. Alter means you will change the datatype of that column.
  3. Drop Column
    function DropColumn($table,$columnname)
    	$query = "ALTER TABLE ". $table."
    	DROP COLUMN ". $columnname;
    	$result = ExecuteQuery($query,"Column deleted successfully");
    	return $result;

    This function takes two parameters table name, column name which you would like to delete from the table.

Now open your index.php file and execute these function one by one and see the changes.

  1. When we run the AddColumn function It will add new column. To see the changes go to your table MyGuests in phpmyadmin and see the new column subject with VARCHAR(30) is added in it.
    $result = AddColumn("MyGuests","subject","VARCHAR(50)");
    echo $result;

    Your new table would be look like this:


  2. When we run the ModifyColumn function it will modify the length of VARCHAR from 30 to 50. Now your table will look like this:
    $result = ModifyColumn("MyGuests","subject","VARCHAR(30)");
    echo $result;

    Your new table would be look like this:


  3. When we run the DropColumn it will drop or you can say delete the column reg_date. And your final table will be look like this:
    $result = DropColumn("MyGuests","reg_date");
    echo $result;

    Your new table would be look like this:


DROP table

Suppose you want to delete just one table from your database which you created completely wrong, for this you use DROP query of mysql. Syntax for DROP query is

DROP TABLE tablename

Now we will create a new function which will handle your queries related to drop. The code for this function is below:

function DropTable($table)
	$query = "DROP TABLE ". $table;
	$result = ExecuteQuery($query,"Table deleted successfully");
	return $result;

It has one parameter which will take table name and execute it using ExecuteQuery function.

Let’s run this function by going to index.php and calling this function and keeping MyGuests as table name:

$result = DropTable("MyGuests");
echo $result;

When the above query is executed, the table MyGuests will be removed from database and you will find your database empty.



In this tutorial, we learned how to create, delete and modify tables. In the next installment of this MySQL series, I will demonstrate how to use MySQL and PHP to create a simple CRUD application. Here is a short introduction and list of major topics in this MySQL series.

Keep checking the blog for more or you can even subscribe to our newsletter to keep yourself updated with the latest happenings at Cloudways.

Share your opinion in the comment section. COMMENT NOW

Share This Article

Launch PHP websites without the worry of Server Management.

Pre-Installed Optimized Stack with Git, Composer & SSH

Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

BFCM 2019