- Home
- QueryBuilder
- Build Join Query with KoolReport
[Last Day] 40% OFF Black Friday
The only occasion to get KoolReport Pro with this great discount.
Owning the Pro license, you will access to all commercial packages and enjoying privilege of Priority Support plus Free Upgrage. The Pro version will save you tons of time & effort to construct a stunning data report. Your purchase is safe with our 30-days money back guarantee policy. If KoolReport Pro does not work for you, we will refund fully!
Join Query
The example show how to create a query to several tables using QueryBuilder
orderNumber | customerName | productName | quantityOrdered | priceEach | orderDate |
---|---|---|---|---|---|
10,123 | Atelier graphique | 1965 Aston Martin DB5 | 26 | 121 | 2003-05-20 |
10,123 | Atelier graphique | 1999 Indy 500 Monte Carlo SS | 46 | 115 | 2003-05-20 |
10,123 | Atelier graphique | 1948 Porsche Type 356 Roadster | 34 | 117 | 2003-05-20 |
10,123 | Atelier graphique | 1966 Shelby Cobra 427 S/C | 50 | 43 | 2003-05-20 |
10,298 | Atelier graphique | 1996 Moto Guzzi 1100i | 39 | 106 | 2004-09-27 |
10,298 | Atelier graphique | 1936 Harley Davidson El Knucklehead | 32 | 61 | 2004-09-27 |
10,345 | Atelier graphique | 1938 Cadillac V-16 Presidential Limousine | 43 | 39 | 2004-11-25 |
10,124 | Signal Gift Stores | 1917 Grand Touring Sedan | 21 | 153 | 2003-05-21 |
10,124 | Signal Gift Stores | 1911 Ford Town Car | 42 | 58 | 2003-05-21 |
10,124 | Signal Gift Stores | 1932 Model A Ford J-Coupe | 42 | 112 | 2003-05-21 |
10,124 | Signal Gift Stores | 1932 Alfa Romeo 8C2300 Spider Sport | 36 | 75 | 2003-05-21 |
10,124 | Signal Gift Stores | 1957 Ford Thunderbird | 23 | 66 | 2003-05-21 |
10,124 | Signal Gift Stores | 1970 Chevy Chevelle SS 454 | 22 | 62 | 2003-05-21 |
10,124 | Signal Gift Stores | 1939 Chevrolet Deluxe Coupe | 45 | 31 | 2003-05-21 |
10,124 | Signal Gift Stores | 1938 Cadillac V-16 Presidential Limousine | 22 | 36 | 2003-05-21 |
10,124 | Signal Gift Stores | 1949 Jaguar XK 120 | 32 | 75 | 2003-05-21 |
10,124 | Signal Gift Stores | 1952 Citroen-15CV | 25 | 94 | 2003-05-21 |
10,124 | Signal Gift Stores | 1969 Chevrolet Camaro Z28 | 49 | 76 | 2003-05-21 |
10,124 | Signal Gift Stores | 2002 Chevy Corvette | 43 | 102 | 2003-05-21 |
10,124 | Signal Gift Stores | 1936 Mercedes Benz 500k Roadster | 46 | 36 | 2003-05-21 |
10,278 | Signal Gift Stores | 1993 Mazda RX-7 | 34 | 115 | 2004-08-06 |
10,278 | Signal Gift Stores | 1965 Aston Martin DB5 | 23 | 107 | 2004-08-06 |
10,278 | Signal Gift Stores | 1948 Porsche 356-A Roadster | 29 | 73 | 2004-08-06 |
10,278 | Signal Gift Stores | 1995 Honda Civic | 29 | 118 | 2004-08-06 |
10,278 | Signal Gift Stores | 1999 Indy 500 Monte Carlo SS | 39 | 117 | 2004-08-06 |
10,278 | Signal Gift Stores | 1992 Ferrari 360 Spider red | 42 | 168 | 2004-08-06 |
10,278 | Signal Gift Stores | 1948 Porsche Type 356 Roadster | 31 | 114 | 2004-08-06 |
10,278 | Signal Gift Stores | 1966 Shelby Cobra 427 S/C | 35 | 49 | 2004-08-06 |
10,278 | Signal Gift Stores | 1982 Lamborghini Diablo | 31 | 37 | 2004-08-06 |
10,278 | Signal Gift Stores | 1956 Porsche 356A Coupe | 25 | 136 | 2004-08-06 |
10,346 | Signal Gift Stores | 1937 Lincoln Berline | 42 | 88 | 2004-11-29 |
10,346 | Signal Gift Stores | 1949 Jaguar XK 120 | 25 | 87 | 2004-11-29 |
10,346 | Signal Gift Stores | 1952 Citroen-15CV | 24 | 117 | 2004-11-29 |
10,346 | Signal Gift Stores | 1969 Chevrolet Camaro Z28 | 24 | 80 | 2004-11-29 |
10,346 | Signal Gift Stores | 2002 Chevy Corvette | 26 | 104 | 2004-11-29 |
10,346 | Signal Gift Stores | 1936 Mercedes Benz 500k Roadster | 22 | 39 | 2004-11-29 |
10,120 | Australian Collectors, Co. | 1996 Moto Guzzi 1100i | 29 | 119 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 2003 Harley-Davidson Eagle Drag Bike | 46 | 159 | 2003-04-29 |
10,120 | Australian Collectors, Co. | P-51-D Mustang | 29 | 83 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1936 Harley Davidson El Knucklehead | 46 | 58 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1997 BMW R 1100 S | 35 | 110 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1928 British Royal Navy Airplane | 39 | 93 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1960 BSA Gold Star DBD34 | 34 | 72 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1900s Vintage Tri-Plane | 29 | 72 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1997 BMW F650 ST | 22 | 95 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1928 Ford Phaeton Deluxe | 29 | 69 | 2003-04-29 |
10,120 | Australian Collectors, Co. | 1930 Buick Marquette Phaeton | 49 | 41 | 2003-04-29 |
10,120 | Australian Collectors, Co. | American Airlines: B767-300 | 47 | 91 | 2003-04-29 |
10,120 | Australian Collectors, Co. | America West Airlines B757-200 | 24 | 82 | 2003-04-29 |
10,120 | Australian Collectors, Co. | ATA: B757-300 | 24 | 107 | 2003-04-29 |
10,120 | Australian Collectors, Co. | F/A 18 Hornet 1/72 | 43 | 72 | 2003-04-29 |
10,125 | Australian Collectors, Co. | 1937 Lincoln Berline | 32 | 89 | 2003-05-21 |
10,125 | Australian Collectors, Co. | 1928 Mercedes-Benz SSK | 34 | 138 | 2003-05-21 |
10,223 | Australian Collectors, Co. | 1969 Harley Davidson Ultimate Chopper | 37 | 80 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1996 Moto Guzzi 1100i | 47 | 111 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 2003 Harley-Davidson Eagle Drag Bike | 49 | 190 | 2004-02-20 |
10,223 | Australian Collectors, Co. | P-51-D Mustang | 47 | 68 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1936 Harley Davidson El Knucklehead | 28 | 59 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1997 BMW R 1100 S | 32 | 105 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1928 British Royal Navy Airplane | 34 | 88 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1960 BSA Gold Star DBD34 | 38 | 61 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1900s Vintage Tri-Plane | 23 | 68 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1997 BMW F650 ST | 21 | 91 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1928 Ford Phaeton Deluxe | 20 | 67 | 2004-02-20 |
10,223 | Australian Collectors, Co. | 1930 Buick Marquette Phaeton | 41 | 41 | 2004-02-20 |
10,223 | Australian Collectors, Co. | American Airlines: B767-300 | 25 | 84 | 2004-02-20 |
10,223 | Australian Collectors, Co. | ATA: B757-300 | 29 | 114 | 2004-02-20 |
10,223 | Australian Collectors, Co. | F/A 18 Hornet 1/72 | 26 | 79 | 2004-02-20 |
10,342 | Australian Collectors, Co. | 1993 Mazda RX-7 | 40 | 119 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1948 Porsche 356-A Roadster | 55 | 63 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1995 Honda Civic | 22 | 115 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1992 Ferrari 360 Spider red | 30 | 168 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1969 Dodge Super Bee | 25 | 76 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1976 Ford Gran Torino | 55 | 137 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1957 Vespa GS150 | 26 | 58 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1957 Corvette Convertible | 38 | 125 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1982 Lamborghini Diablo | 39 | 31 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1971 Alpine Renault 1600s | 48 | 60 | 2004-11-24 |
10,342 | Australian Collectors, Co. | 1956 Porsche 356A Coupe | 42 | 112 | 2004-11-24 |
10,347 | Australian Collectors, Co. | 1952 Alpine Renault 1300 | 30 | 189 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1962 LanciaA Delta 16V | 27 | 133 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1958 Setra Bus | 29 | 133 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1940 Ford Pickup Truck | 42 | 113 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1936 Mercedes-Benz 500K Special Roadster | 21 | 46 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1926 Ford Fire Engine | 50 | 51 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1928 Mercedes-Benz SSK | 21 | 137 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1913 Ford Model T Speedster | 48 | 84 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1934 Ford V8 Coupe | 34 | 61 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 18th Century Vintage Horse Carriage | 45 | 95 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1917 Maxwell Touring Car | 26 | 84 | 2004-11-29 |
10,347 | Australian Collectors, Co. | 1940s Ford truck | 45 | 115 | 2004-11-29 |
10,275 | La Rochelle Gifts | 1969 Harley Davidson Ultimate Chopper | 45 | 81 | 2004-07-23 |
10,275 | La Rochelle Gifts | 1996 Moto Guzzi 1100i | 22 | 115 | 2004-07-23 |
10,275 | La Rochelle Gifts | 2003 Harley-Davidson Eagle Drag Bike | 36 | 155 | 2004-07-23 |
10,275 | La Rochelle Gifts | P-51-D Mustang | 35 | 70 | 2004-07-23 |
10,275 | La Rochelle Gifts | 1936 Harley Davidson El Knucklehead | 37 | 52 | 2004-07-23 |
10,275 | La Rochelle Gifts | 1997 BMW R 1100 S | 21 | 106 | 2004-07-23 |
10,275 | La Rochelle Gifts | 1928 British Royal Navy Airplane | 25 | 97 | 2004-07-23 |
10,275 | La Rochelle Gifts | 1960 BSA Gold Star DBD34 | 30 | 62 | 2004-07-23 |
10,275 | La Rochelle Gifts | Corsair F4U ( Bird Cage) | 41 | 58 | 2004-07-23 |
This example show how to create JOIN query using QueryBuilder
.
DB::table("orders")
->join("orderdetails",'orders.orderNumber','=','orderdetails.orderNumber')
->join("products",'orderdetails.productCode','=','products.productCode')
->join("customers",'orders.customerNumber','=','customers.customerNumber')
->select(
'orders.orderNumber',
'customers.customerName',
'products.productName',
'orderdetails.quantityOrdered',
'orderdetails.priceEach',
'orders.orderDate'
)
<?php
require_once "MyReport.php";
$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";
use \koolreport\querybuilder\DB;
//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
function settings()
{
return array(
"dataSources"=>array(
"automaker"=>array(
"connectionString"=>"mysql:host=localhost;dbname=automaker",
"username"=>"root",
"password"=>"",
"charset"=>"utf8"
),
)
);
}
protected function setup()
{
$this->src('automaker')
->query(
DB::table("orders")
->join("orderdetails",'orders.orderNumber','=','orderdetails.orderNumber')
->join("products",'orderdetails.productCode','=','products.productCode')
->join("customers",'orders.customerNumber','=','customers.customerNumber')
->select(
'orders.orderNumber',
'customers.customerName',
'products.productName',
'orderdetails.quantityOrdered',
'orderdetails.priceEach',
'orders.orderDate'
)
->take(100)
)
->pipe($this->dataStore("orders"));
}
}
<?php
use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
<div class="text-center">
<h1>Join Query</h1>
<p class="lead">
The example show how to create a query to several tables using QueryBuilder
</p>
</div>
<?php
Table::create(array(
"dataSource"=>$this->dataStore("orders"),
"paging"=>true
));
?>
</div>
What People Are Saying
"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
--
Alain Melsens
"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
--
Dr. Lew Choy Onn
"Fantastic framework for reporting!"
--
Greg Schneider