Table Views can be used as an unconventional way to avoid cross/multiple database connections sometimes..! Yes, it can be. I came across such case and found that it can be handy in some simple cases when we just want to avoid connection to another database on the same MySQL server and we can't afford to create that table in the same database due to some reasons.
Views

Views - Image courtesy Flickr

It's very simple. You can have your table "contacts" in "my_db" database and you want to access this table (not just read need to write/insert and update too) in another database named "another_db".
You can have your "contacts" table in "my_db" with following syntax: 
CREATE TABLE `contacts` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`first_name` VARCHAR(100) NOT NULL,
	`last_name` VARCHAR(100) NOT NULL,
	`email` VARCHAR(100) NULL DEFAULT NULL,
	`cell_phone` VARCHAR(25) NULL DEFAULT NULL,
	`home_phone` VARCHAR(25) NULL DEFAULT NULL,
	`note` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;
 

And you can create your view in "another_db" database with following syntax:

SELECT * FROM `my_db`.`contacts`
 

View definition will be updated to following automatically:

SELECT `my_db`.`contacts`.`id` AS `id`,`my_db`.`contacts`.`first_name` AS `first_name`,`my_db`.`contacts`.`last_name` AS `last_name`,`my_db`.`contacts`.`email` AS `email`,`my_db`.`contacts`.`cell_phone` AS `cell_phone`,`my_db`.`contacts`.`home_phone` AS `home_phone`,`my_db`.`contacts`.`note` AS `note` FROM `my_db`.`contacts`
 

That's it we are done.
Now we can execute any Insert, Update or Select query on this view, which will in turn change data in "mydb.contacts" table.

This can be a simpler solution if we want to avoid dealing with handling multiple database in PHP or any other server-side programming language.

INSERT INTO `v_contacts` (`first_name`, `last_name`, `email`, `cell_phone`, `note`) VALUES ('Bill', 'Gates', 'bill@msn.com', '7188802201', 'Former Microsoft CEO');
 

This is not something new; we knew and we read this all while we were at college. But it makes us feel good if we can put something like this at work which can be a small thing but can help us saving some time..!

Friends, comment if you liked this post, or share your experiences.

Similar Posts: