Introduction
In this tutorial I will show you CodeIgniter join example. I will use CodeIgniter’s Query Builder Class to build join queries for fetching data from multiple tables.
The SQL Join clause is used to combine records together from two or more tables in a database. A Join locates related column values in two or more tables. This will display all the records that have matched.
Join and Inner Join are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it. Here we will see Join or Inner Join example.
We will use CodeIgniter’s $this->db->join() that permits you to write the Join portion of your query.
Related Posts:
- Codeigniter Full Outer Join Example
- Codeigniter Right Outer Join Example
- Codeigniter Left Outer Join Example
Prerequisites
Apache HTTP Server 2.4, CodeIgniter 3.1.11, PHP 7.4.3, MySQL 8.0.17
MySQL Tables
Here I am going to create following tables in MySQL server under roytuts database.
CREATE TABLE `blogs` (
`blog_id` int unsigned NOT NULL AUTO_INCREMENT,
`blog_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`blog_content` text COLLATE utf8mb4_unicode_ci NOT NULL,
`blog_date` datetime NOT NULL,
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `blog_comments` (
`comment_id` int unsigned NOT NULL AUTO_INCREMENT,
`comment_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
`comment_date` datetime NOT NULL,
`parent_id` int unsigned NOT NULL DEFAULT 0,
`blog_id` int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Now I am also going to store some sample data into above tables for testing the application.
insert into `blogs`(`blog_id`,`blog_title`,`blog_content`,`blog_date`) values (4,'Sample Blog','Sample Blog Content','2016-05-21 18:24:13'),(5,'Sample Blog','Sample Blog Content','2016-05-21 18:24:13'),(6,'Sample Blog','Sample Blog Content','2016-05-21 18:24:13'),(7,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 08:09:26'),(8,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 00:00:00'),(9,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:58:55'),(10,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:01'),(11,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:02'),(12,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:02'),(13,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:02'),(14,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(15,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(16,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(17,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(18,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(19,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:03'),(20,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(21,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(22,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(23,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(24,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(25,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:04'),(26,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(27,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(28,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(29,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(30,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(31,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:05'),(32,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:06'),(33,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:06'),(34,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:06'),(35,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:06'),(36,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:06'),(37,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(38,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(39,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(40,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(41,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(42,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:07'),(43,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:08'),(44,'Sample Blog','Muffin gummi bears tiramisu pudding pudding halvah oat cake cake. Topping sugar plum tootsie roll tart chocolate bar biscuit pudding. Candy canes chocolate cake powder pastry carrot cake. Jelly-o apple pie icing bear claw oat cake muffin pie. Cake dragée pie. Toffee apple pie candy. Candy cheesecake carrot cake liquorice tootsie roll jelly biscuit applicake. Soufflé jelly beans croissant jelly-o danish liquorice. Cheesecake cotton candy gummi bears cookie bear claw applicake tiramisu gummies bear claw. Icing candy canes bear claw macaroon ice cream jujubes applicake chocolate gingerbread. Topping tootsie roll sweet. Halvah dessert cookie pie jelly beans gummies jelly beans danish. Soufflé caramels tootsie roll croissant halvah. Bonbon cotton candy sweet cake oat cake wafer applicake.','2016-05-25 20:59:08');
insert into `blog_comments`(`comment_id`,`comment_text`,`comment_date`,`parent_id`,`blog_id`) values (1,'good one','2016-05-28 07:30:46',0,4),(2,'nice','2016-05-27 07:34:28',0,5),(3,'great','2016-05-26 07:35:03',0,6),(4,'carry on','2016-05-28 07:35:23',0,7),(5,'good tuts','2016-05-28 07:35:42',0,4),(6,'like it','2016-05-28 07:36:00',0,5),(7,'waiting for next post','2016-05-28 07:36:25',0,9);Example on Join Query
I have already stored sample data into two tables in my previous step. So on executing below query:
SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id;or
SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
FROM blogs INNER JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id;You will get the following results as shown in the below image:
Project Directory
It’s assumed that you have setup Apache, PHP and Codeigniter in Windows system.
Now I will create a project root directory called codeIgniter-join under the Apache server’s htdocs folder.
Now move all the directories and files from CodeIgniter framework into codeIgniter-join directory.
I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project’s root directory.
Configuring Database
Configure your MySQL database to work with your CodeIgniter application. I will configure database connection into application/config/database.php file. Please make sure to change values according to your database settings.
$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'char_set' => 'utf8mb4',
'dbcollat' => 'utf8mb4_unicode_ci',
...
);Configuring Auto-load
I will configure database library and helpers to auto-load to avoid load every time we need to use such things in the application.
Update the below lines in the file application/config/autoload.php.
$autoload['libraries'] = array('database');
$autoload['helper'] = array('url', 'file');Model Class
You will perform database operation to fetch data using join query in the model class.
I will use CodeIgniter’s Query builder class to build the join query.
Create a file JoinModel.php under application/models folder with the below source code.
<?php
/**
* Description of JoinModel
*
* @author https://roytuts.com
*/
class JoinModel extends CI_Model {
private $blogs = 'blogs'; // blog table
private $blog_comments = 'blog_comments'; // blog comment table
function __construct() {
parent::__construct();
}
function join() {
// Produces:
// SELECT * FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id
//$this->db->select('*');
//$this->db->from($this->blogs);
//$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id');
//or
//$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id', 'inner');
//$query = $this->db->get();
// Produces:
// SELECT blogs.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date
// FROM blogs JOIN blog_comments ON blog_comments.blog_id = blogs.blog_id
$this->db->select($this->blogs . '.blog_id,comment_id,blog_title,blog_content,blog_date,comment_text,comment_date');
$this->db->from($this->blogs);
$this->db->join($this->blog_comments, $this->blog_comments . '.blog_id = ' . $this->blogs . '.blog_id');
//or
//$this->db->join($this->blog_comments, $this->blog_comments . 'blog_id = ' . $this->blogs . 'blog_id', 'inner');
$query = $this->db->get();
return $query->result();
}
}Controller Class
I will create controller class to handle request/response for clients. This controller class will communicate between model class and the view file.
Create a file JoinController.php under application/controllers folder with below source code.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class JoinController extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->model('joinmodel');
}
public function index() {
$data['blogs'] = $this->joinmodel->join();
$this->load->view('join_view', $data);
}
}View File
Create a view file join_view.php under application/views which will be used to display blogs and associated comments for blogs.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Welcome to CodeIgniter Join Example</title>
<style type="text/css">
::selection { background-color: #E13300; color: white; }
::-moz-selection { background-color: #E13300; color: white; }
body {
background-color: #fff;
margin: 40px;
font: 13px/20px normal Helvetica, Arial, sans-serif;
color: #4F5155;
}
a {
color: #003399;
background-color: transparent;
font-weight: normal;
}
h1 {
color: #444;
background-color: transparent;
border-bottom: 1px solid #D0D0D0;
font-size: 19px;
font-weight: normal;
margin: 0 0 14px 0;
padding: 14px 15px 10px 15px;
}
code {
font-family: Consolas, Monaco, Courier New, Courier, monospace;
font-size: 12px;
background-color: #f9f9f9;
border: 1px solid #D0D0D0;
color: #002166;
display: block;
margin: 14px 0 14px 0;
padding: 12px 10px 12px 10px;
}
#body {
margin: 0 15px 0 15px;
}
p.footer {
text-align: right;
font-size: 11px;
border-top: 1px solid #D0D0D0;
line-height: 32px;
padding: 0 10px 0 10px;
margin: 20px 0 0 0;
}
#container {
margin: 10px;
border: 1px solid #D0D0D0;
box-shadow: 0 0 8px #D0D0D0;
}
</style>
</head>
<body>
<div id="container">
<h1>CodeIgniter Join Example</h1>
<div id="body">
<?php
foreach ($blogs as $blog) {
?>
<div class="post">
<h2 class="title"><?php echo $blog->blog_title; ?></h2>
<p class="meta">
<?php
echo $blog->blog_date;
?>
<div class="entry">
<p><?php echo $blog->blog_content; ?></p>
</div>
</div>
<p> <strong>Comment on <?php echo $blog->comment_date; ?> :</strong> <?php echo $blog->comment_text; ?></p>
<?php
}
?>
</div>
</div>
</body>
</html>Configuring Route
Now modify application/config/routes.php file for configuring route with our controller class.
$route['default_controller'] = 'JoinController';Testing the Application
Make sure your Apache server is running and Codeigniter join application is deployed.
Source Code
Thanks for reading.