Articles

On daily basis I work with SQL server and doing simple alterations to table, stored procedure, etc... are very simple. But on the other hand MySQL makes it more challenging when it comes to altering the table for example. In Joomla or PHP applications there are instances where we need to alter the table without dropping and creating a new table. Here is a procedure to achieve that.

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_package_table $$
CREATE PROCEDURE upgrade_package_table()
BEGIN

IF NOT EXISTS ( (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
AND COLUMN_NAME='id' AND TABLE_NAME = 'jos_jlpackages' )) THEN
ALTER TABLE jos_jlpackages ADD ID tinyint(3) unsigned NOT NULL AUTO_INCREMENT;
END IF;

END $$

CALL upgrade_package_table() $$

DELIMITER ;