How to Create, Alter And Drop Table in MySQL

by Ahmed Khan  January 19, 2016

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:

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:

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.

image08

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

image01

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

    This function takes three parameters table name, column name, and its datatype which you would like to modify.
  2. Add Column
  3. Drop Column

    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.

    Your new table would be look like this:

    image10

  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:

    Your new table would be look like this:

    image03

  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:

    Your new table would be look like this:

    image06

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:

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:

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

image04

Conclusion:

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.

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About 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 ahmed.khan@cloudways.com

Stay Connected:

You Might Also Like...