SQL Queries During Component Install, Uninstall and Update

During the install, uninstall or update phase of a component, you can execute SQL queries through the use of SQL text files.

1. Create Table

There may be some common fields in each table - id, title, alias, description, published, catid, ordering, image, params, note, created_by, created, modified, access.

Create new table

CREATE TABLE IF NOT EXISTS `#__table_name` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(400),
`alias` VARCHAR(400),
`description` TEXT,
`image` VARCHAR(400),
`catid` INT(11),
`published` TINYINT(3),
`params` MEDIUMTEXT,
`note` VARCHAR(255),
`ordering` INT(11),
`access` TINYINT(4),
`created_by` INT(10) UNSIGNED,
`created` DATETIME,
`modified` DATETIME,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=0
DEFAULT CHARSET=utf8mb4
DEFAULT COLLATE=utf8mb4_unicode_ci;

Create new table similar to existing table

CREATE TABLE IF NOT EXISTS `#__table_name` LIKE `#__existing_table`;

Then, you can copy data

INSERT `#__table_name` SELECT * FROM `#__existing_table`;

2. Alter Table Structure

Add new column

ALTER TABLE `#__table_name` ADD `created` DATETIME AFTER `created_by`;

Drop or delete column

ALTER TABLE `#__table_name` DROP `column_name`;

Change column data type

ALTER TABLE `#__tfm_mail` MODIFY `column_name` VARCHAR(400);

Rename column and also change its data type 

ALTER TABLE `#__table_name` CHANGE `old_column` `new_column` MEDIUMTEXT;

3. Update Table Data

Set Column to some value

UPDATE `#__table_name` SET `column_name` = NULL;

Copy one column to another

UPDATE `#__table_name` SET `created` = `modified`; 

4. Drop or Delete Table

DROP TABLE `#__table_name`;

 

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive