Join Query

The example show how to create a query to several tables using QueryBuilder

orderNumbercustomerNameproductNamequantityOrderedpriceEachorderDate
10,100 Online Diecast Creations Co. 1917 Grand Touring Sedan 30 136 2003-01-06 00:00:00
10,100 Online Diecast Creations Co. 1911 Ford Town Car 50 55 2003-01-06 00:00:00
10,100 Online Diecast Creations Co. 1932 Alfa Romeo 8C2300 Spider Sport 22 75 2003-01-06 00:00:00
10,100 Online Diecast Creations Co. 1936 Mercedes Benz 500k Roadster 49 35 2003-01-06 00:00:00
10,101 Blauer See Auto, Co. 1932 Model A Ford J-Coupe 25 108 2003-01-09 00:00:00
10,101 Blauer See Auto, Co. 1928 Mercedes-Benz SSK 26 167 2003-01-09 00:00:00
10,101 Blauer See Auto, Co. 1939 Chevrolet Deluxe Coupe 45 33 2003-01-09 00:00:00
10,101 Blauer See Auto, Co. 1938 Cadillac V-16 Presidential Limousine 46 44 2003-01-09 00:00:00
10,102 Vitachrome Inc. 1937 Lincoln Berline 39 96 2003-01-10 00:00:00
10,102 Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster 41 43 2003-01-10 00:00:00
10,103 Baane Mini Imports 1952 Alpine Renault 1300 26 214 2003-01-29 00:00:00
10,103 Baane Mini Imports 1962 LanciaA Delta 16V 42 120 2003-01-29 00:00:00
10,103 Baane Mini Imports 1958 Setra Bus 27 122 2003-01-29 00:00:00
10,103 Baane Mini Imports 1940 Ford Pickup Truck 35 95 2003-01-29 00:00:00
10,103 Baane Mini Imports 1926 Ford Fire Engine 22 58 2003-01-29 00:00:00
10,103 Baane Mini Imports 1913 Ford Model T Speedster 27 92 2003-01-29 00:00:00
10,103 Baane Mini Imports 1934 Ford V8 Coupe 35 62 2003-01-29 00:00:00
10,103 Baane Mini Imports 18th Century Vintage Horse Carriage 25 87 2003-01-29 00:00:00
10,103 Baane Mini Imports 1917 Maxwell Touring Car 46 86 2003-01-29 00:00:00
10,103 Baane Mini Imports 1940s Ford truck 36 98 2003-01-29 00:00:00
10,103 Baane Mini Imports 1939 Cadillac Limousine 41 41 2003-01-29 00:00:00
10,103 Baane Mini Imports 1962 Volkswagen Microbus 36 107 2003-01-29 00:00:00
10,103 Baane Mini Imports 1936 Chrysler Airflow 25 89 2003-01-29 00:00:00
10,103 Baane Mini Imports 1980’s GM Manhattan Express 31 92 2003-01-29 00:00:00
10,103 Baane Mini Imports 1996 Peterbilt 379 Stake Bed with Outrigger 45 63 2003-01-29 00:00:00
10,103 Baane Mini Imports 1982 Camaro Z28 42 94 2003-01-29 00:00:00
10,104 Euro+ Shopping Channel 1969 Corvair Monza 34 131 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1957 Chevy Pickup 41 111 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1998 Chrysler Plymouth Prowler 24 136 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1964 Mercedes Tour Bus 29 123 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1992 Ferrari 360 Spider red 23 166 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1970 Triumph Spitfire 38 119 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1970 Dodge Coronet 35 52 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1958 Chevy Corvette Limited Edition 44 30 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1992 Porsche Cayenne Turbo Silver 26 106 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1954 Greyhound Scenicruiser 35 52 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1950's Chicago Surface Lines Streetcar 49 57 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel Diamond T620 Semi-Skirted Tanker 33 115 2003-01-31 00:00:00
10,104 Euro+ Shopping Channel 1962 City of Detroit Streetcar 32 53 2003-01-31 00:00:00
10,105 Danish Wholesale Imports 1972 Alfa Romeo GTA 50 128 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 2001 Ferrari Enzo 41 206 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 1969 Ford Falcon 29 142 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 1903 Ford Model A 22 137 2003-02-11 00:00:00
10,105 Danish Wholesale Imports Collectable Wooden Train 38 88 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 1904 Buick Runabout 41 75 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 18th century schooner 43 118 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 1912 Ford Model T Delivery Wagon 44 73 2003-02-11 00:00:00
10,105 Danish Wholesale Imports 1940 Ford Delivery Sedan 50 75 2003-02-11 00:00:00
10,105 Danish Wholesale Imports The Schooner Bluenose 41 54 2003-02-11 00:00:00
10,105 Danish Wholesale Imports The Mayflower 29 87 2003-02-11 00:00:00
10,105 Danish Wholesale Imports The USS Constitution Ship 31 61 2003-02-11 00:00:00
10,105 Danish Wholesale Imports The Titanic 39 92 2003-02-11 00:00:00
10,105 Danish Wholesale Imports The Queen Mary 22 99 2003-02-11 00:00:00
10,105 Danish Wholesale Imports Pont Yacht 25 45 2003-02-11 00:00:00
10,106 Rovelli Gifts 1980s Black Hawk Helicopter 36 134 2003-02-17 00:00:00
10,106 Rovelli Gifts P-51-D Mustang 34 81 2003-02-17 00:00:00
10,106 Rovelli Gifts 1999 Yamaha Speed Boat 41 81 2003-02-17 00:00:00
10,106 Rovelli Gifts 1941 Chevrolet Special Deluxe Cabriolet 41 94 2003-02-17 00:00:00
10,106 Rovelli Gifts 1928 British Royal Navy Airplane 28 107 2003-02-17 00:00:00
10,106 Rovelli Gifts 1900s Vintage Bi-Plane 49 66 2003-02-17 00:00:00
10,106 Rovelli Gifts 1937 Horch 930V Limousine 31 56 2003-02-17 00:00:00
10,106 Rovelli Gifts Corsair F4U ( Bird Cage) 50 56 2003-02-17 00:00:00
10,106 Rovelli Gifts 1900s Vintage Tri-Plane 26 71 2003-02-17 00:00:00
10,106 Rovelli Gifts 1928 Ford Phaeton Deluxe 33 65 2003-02-17 00:00:00
10,106 Rovelli Gifts 1930 Buick Marquette Phaeton 39 36 2003-02-17 00:00:00
10,106 Rovelli Gifts American Airlines: B767-300 31 91 2003-02-17 00:00:00
10,106 Rovelli Gifts HMS Bounty 30 85 2003-02-17 00:00:00
10,106 Rovelli Gifts America West Airlines B757-200 34 100 2003-02-17 00:00:00
10,106 Rovelli Gifts ATA: B757-300 32 114 2003-02-17 00:00:00
10,106 Rovelli Gifts F/A 18 Hornet 1/72 44 76 2003-02-17 00:00:00
10,106 Rovelli Gifts American Airlines: MD-11S 48 70 2003-02-17 00:00:00
10,106 Rovelli Gifts Boeing X-32A JSF 48 44 2003-02-17 00:00:00
10,107 Land of Toys Inc. 1969 Harley Davidson Ultimate Chopper 30 81 2003-02-24 00:00:00
10,107 Land of Toys Inc. 1996 Moto Guzzi 1100i 39 106 2003-02-24 00:00:00
10,107 Land of Toys Inc. 2003 Harley-Davidson Eagle Drag Bike 27 172 2003-02-24 00:00:00
10,107 Land of Toys Inc. 2002 Suzuki XREO 21 122 2003-02-24 00:00:00
10,107 Land of Toys Inc. 1936 Harley Davidson El Knucklehead 29 53 2003-02-24 00:00:00
10,107 Land of Toys Inc. 1997 BMW R 1100 S 25 97 2003-02-24 00:00:00
10,107 Land of Toys Inc. 1960 BSA Gold Star DBD34 38 73 2003-02-24 00:00:00
10,107 Land of Toys Inc. 1997 BMW F650 ST 20 89 2003-02-24 00:00:00
10,108 Cruz & Sons Co. 1968 Ford Mustang 33 165 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1968 Dodge Charger 45 96 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1970 Plymouth Hemi Cuda 39 76 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1969 Dodge Charger 36 107 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1948 Porsche 356-A Roadster 38 68 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1969 Dodge Super Bee 26 73 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1976 Ford Gran Torino 29 132 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1957 Vespa GS150 43 53 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1957 Corvette Convertible 44 140 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1982 Ducati 900 Monster 35 64 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1971 Alpine Renault 1600s 30 60 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1956 Porsche 356A Coupe 40 132 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1961 Chevrolet Impala 31 67 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1982 Ducati 996 R 27 36 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 1974 Ducati 350 Mk3 Desmo 31 88 2003-03-03 00:00:00
10,108 Cruz & Sons Co. 2002 Yamaha YZR M1 34 75 2003-03-03 00:00:00
10,109 Motor Mint Distributors Inc. 1993 Mazda RX-7 26 117 2003-03-10 00:00:00
10,109 Motor Mint Distributors Inc. 1995 Honda Civic 38 138 2003-03-10 00:00:00
10,109 Motor Mint Distributors Inc. 1999 Indy 500 Monte Carlo SS 26 127 2003-03-10 00:00:00
10,109 Motor Mint Distributors Inc. 1992 Ferrari 360 Spider red 46 161 2003-03-10 00:00:00

This example shows 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

Download KoolReport Get KoolReport Pro