KoolReport
  1. Home
  2. QueryBuilder
  3. Build Join Query with KoolReport

Join Query

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

orderNumbercustomerNameproductNamequantityOrderedpriceEachorderDate
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

Download KoolReport Get KoolReport Pro