Adding Columns To A MySQL Table Using PHP.

This is a tutorial on how to add columns to a MySQL table using PHP. Please note that we will be using the PDO object in this tutorial (although the SQL queries will remain the same, regardless of whether you’re using PDO, mysql or mysqli).

For this tutorial, I will be using the following table structure:

1 2 3 4 5

CREATE TABLE IF NOT EXISTS `reports` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `title` varchar(120) COLLATE utf8_unicode_ci NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

As you can see, the table has two columns: A Primary INT column called “id” and a VARCHAR column called “title”.

Let’s add a simple DATE column to the end of our “reports” table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?php   //Connect to our MySQL database using PHP's PDO extension. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');   //The name of the table we want to alter. $tableName = 'reports';   //The name of the column that we want to create. $columnName = 'report_date';   //Our SQL query that will alter the table and add the new column. $sql = "ALTER TABLE  `$tableName` ADD  `$columnName` DATE NOT NULL";   //Execute the query. $pdo->query($sql);

In the code above, we connected to MySQL using the PDO object before executing our ALTER TABLE SQL query. In this case, we added a DATE column called “report_date” to the end of the table.

But what if we wanted to add our column before another existing column? What if we don’t want our new column to be placed at the end of the table?

1 2 3 4 5 6 7 8 9 10

<?php   //Connect to our MySQL database using PHP's PDO extension. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');   //This SQL query adds our new column after the first column (id). $sql = "ALTER TABLE  `reports` ADD  `report_date` DATE NOT NULL AFTER  `id` ;";   //Execute the query. $pdo->query($sql);

In the example above, we altered the table and added the new column AFTER the first column, which is “id”.