Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Combining two tables and outputting dates horizontally PHPExcel

Tags:

php

mysql

codeigniter

phpexcel

My first table list_info

list_id | list_name   |   list_link  |    list_extrafee     | list_reviews
------------------------------------------------------------------------
 4852   |  List One   |    /list1    |   $4 after 3 guests  |    56
 4853   |  List Two   |    /list2    |   $10 after 5 guests |     42
 4854   |  List Three |    /list3    |   $40 after 8 guests |     32
 4855   |  List Four  |    /list4    |         FREE         |    14

Second table list_price

list_id  |  dates        | list_price  | list_avilable
------------------------------------------------------
4852     | 4/12/2015     |     55      |     TRUE
4852     | 5/12/2015     |     56      |     TRUE
4852     | 6/12/2015     |     89      |     TRUE
4852     | 7/12/2015     |     110     |     TRUE
4852     | 8/12/2015     |     150     |     TRUE
4852     | 9/12/2015     |     110     |     TRUE
4852     | 10/12/2015    |     110     |     TRUE
4852     | 11/12/2015    |     150     |     TRUE
4852     | 12/12/2015    |     110     |     TRUE
4853     | 4/12/2015     |     160     |     TRUE
4853     | 5/12/2015     |     140     |     FALSE
4853     | 6/12/2015     |     55      |     FALSE
4853     | 7/12/2015     |     56      |     FALSE
4853     | 8/12/2015     |     89      |     FALSE
4853     | 9/12/2015     |     110     |     FALSE
4853     | 10/12/2015    |     150     |     FALSE
4853     | 11/12/2015    |     110     |     FALSE
4853     | 12/12/2015    |     110     |     FALSE
4854     | 4/12/2015     |     150     |     TRUE
4854     | 5/12/2015     |     110     |     TRUE
4854     | 6/12/2015     |     160     |     TRUE
4854     | 7/12/2015     |     140     |     TRUE
4854     | 8/12/2015     |     160     |     TRUE
4854     | 9/12/2015     |     180     |     TRUE
4854     | 10/12/2015    |     110     |     TRUE
4854     | 11/12/2015    |     110     |     TRUE
4854     | 12/12/2015    |     50      |     TRUE
4855     | 4/12/2015     |     50      |     TRUE
4855     | 5/12/2015     |     48      |     TRUE
4855     | 6/12/2015     |     60      |     TRUE
4855     | 7/12/2015     |     70      |     TRUE
4855     | 8/12/2015     |     65      |     TRUE
4855     | 9/12/2015     |     90      |     TRUE
4855     | 10/12/2015    |     80      |     TRUE
4855     | 11/12/2015    |     70      |     TRUE
4855     | 12/12/2015    |     60      |     TRUE

What I am trying to achieve is this:

enter image description here

So far I've managed to get only one of the tables into an array and passing it with the $objPHPExcel->getActiveSheet()->fromArray(); function, code:

public function scrape()
{
    /** Error reporting */
    error_reporting(E_ALL);

    /** Include path **/
    ini_set('include_path', ini_get('include_path').';../Classes/');

    /** PHPExcel */
    include 'PHPExcel.php';

    /** PHPExcel_Writer_Excel2007 */
    include 'PHPExcel/Writer/Excel2007.php';// Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("AB")
        ->setLastModifiedBy("AB")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("AB document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("AB result file");
    // Add some data

    $q = $this->db->select('*')->from('list_info')->get();
    $sheet=$q->result_array();


    $objPHPExcel->getActiveSheet()->fromArray($sheet);


    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('AB Result '.date('d-m-Y').'');
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // Redirect output to a clients web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="01simple.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

I need help combining those two tables into an array that will be suitable for generating the example excel file i have uploaded..

ERROR in Somnath Muluk's answer

Error

SQL query:

PREPARE stmt FROM @sql ;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `list_info` l JOIN list_price p ON l.list_id = p.list_id
      ' at line 15

LONG WORKING CODE:

SELECT l.*,
   MAX( CASE
        WHEN p.list_date = "2015-11-29" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-11-29" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-11-29`,
   MAX( CASE
        WHEN p.list_date = "2015-11-30" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-11-30" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-11-30`,
   MAX( CASE
        WHEN p.list_date = "2015-12-01" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-01" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-01`,
   MAX( CASE
        WHEN p.list_date = "2015-12-02" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-02" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-02`,
   MAX( CASE
        WHEN p.list_date = "2015-12-03" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-03" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-03`,
   MAX( CASE
        WHEN p.list_date = "2015-12-04" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-04" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-04`,
   MAX( CASE
        WHEN p.list_date = "2015-12-05" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-05" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-05`,
   MAX( CASE
        WHEN p.list_date = "2015-12-06" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-06" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-06`,
   MAX( CASE
        WHEN p.list_date = "2015-12-07" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-07" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-07`,
   MAX( CASE
        WHEN p.list_date = "2015-12-08" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-08" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-08`,
   MAX( CASE
        WHEN p.list_date = "2015-12-09" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-09" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-09`,
   MAX( CASE
        WHEN p.list_date = "2015-12-10" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-10" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-10`,
   MAX( CASE
        WHEN p.list_date = "2015-12-11" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-11" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-11`,
   MAX( CASE
        WHEN p.list_date = "2015-12-12" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-12" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-12`,
   MAX( CASE
        WHEN p.list_date = "2015-12-13" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-13" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-13`,

   MAX( CASE
        WHEN p.list_date = "2015-12-14" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-14" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-14`,
   MAX( CASE
        WHEN p.list_date = "2015-12-15" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-15" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-15`,
   MAX( CASE
        WHEN p.list_date = "2015-12-16" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-16" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-16`,
   MAX( CASE
        WHEN p.list_date = "2015-12-17" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-17" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-17`,
   MAX( CASE
        WHEN p.list_date = "2015-12-18" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-18" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-18`,
   MAX( CASE
        WHEN p.list_date = "2015-12-19" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-19" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-19`,
   MAX( CASE
        WHEN p.list_date = "2015-12-20" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-20" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-20`,
   MAX( CASE
        WHEN p.list_date = "2015-12-21" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-21" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-21`,
   MAX( CASE
        WHEN p.list_date = "2015-12-22" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-22" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-22`,
   MAX( CASE
        WHEN p.list_date = "2015-12-23" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-23" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-23`,
   MAX( CASE
        WHEN p.list_date = "2015-12-24" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-24" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-24`,
   MAX( CASE
        WHEN p.list_date = "2015-12-25" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-25" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-25`,
   MAX( CASE
        WHEN p.list_date = "2015-12-26" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-26" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-26`,
   MAX( CASE
        WHEN p.list_date = "2015-12-27" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-27" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-27`,
   MAX( CASE
        WHEN p.list_date = "2015-12-28" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-28" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-28`,
   MAX( CASE
        WHEN p.list_date = "2015-12-29" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-29" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-29`,
   MAX( CASE
        WHEN p.list_date = "2015-12-30" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-30" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-30`,
   MAX( CASE
        WHEN p.list_date = "2015-12-31" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2015-12-31" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2015-12-31`,
   MAX( CASE
        WHEN p.list_date = "2016-01-01" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-01" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-01`,
   MAX( CASE
        WHEN p.list_date = "2016-01-02" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-02" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-02`,
   MAX( CASE
        WHEN p.list_date = "2016-01-03" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-03" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-03`,
   MAX( CASE
        WHEN p.list_date = "2016-01-04" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-04" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-04`,
   MAX( CASE
        WHEN p.list_date = "2016-01-05" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-05" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-05`,
   MAX( CASE
        WHEN p.list_date = "2016-01-06" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-06" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-06`,
   MAX( CASE
        WHEN p.list_date = "2016-01-07" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-07" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-07`,
   MAX( CASE
        WHEN p.list_date = "2016-01-08" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-08" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-08`,
   MAX( CASE
        WHEN p.list_date = "2016-01-09" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-09" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-09`,
   MAX( CASE
        WHEN p.list_date = "2016-01-10" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-10" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-10`,
   MAX( CASE
        WHEN p.list_date = "2016-01-11" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-11" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-11`,
   MAX( CASE
        WHEN p.list_date = "2016-01-12" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-12" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-12`,
   MAX( CASE
        WHEN p.list_date = "2016-01-13" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-13" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-13`,
   MAX( CASE
        WHEN p.list_date = "2016-01-14" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-14" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-14`,
   MAX( CASE
        WHEN p.list_date = "2016-01-15" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-15" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-15`,
   MAX( CASE
        WHEN p.list_date = "2016-01-16" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-16" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-16`,
   MAX( CASE
        WHEN p.list_date = "2016-01-17" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-17" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-17`,
   MAX( CASE
        WHEN p.list_date = "2016-01-18" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-18" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-18`,
   MAX( CASE
        WHEN p.list_date = "2016-01-19" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-19" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-19`,
   MAX( CASE
        WHEN p.list_date = "2016-01-20" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-20" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-20`,
   MAX( CASE
        WHEN p.list_date = "2016-01-21" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-21" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-21`,
   MAX( CASE
        WHEN p.list_date = "2016-01-22" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-01-22" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-01-22`,
   MAX( CASE
        WHEN p.list_date = "2016-01-23" and p.list_available = "TRUE" THEN p.list_price
        ELSE NULL END) `2016-05-21`,
   MAX( CASE
        WHEN p.list_date = "2016-05-22" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-22" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-22`,
   MAX( CASE
        WHEN p.list_date = "2016-05-23" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-23" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-23`,
   MAX( CASE
        WHEN p.list_date = "2016-05-24" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-24" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-24`,
   MAX( CASE
        WHEN p.list_date = "2016-05-25" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-25" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-25`,
   MAX( CASE
        WHEN p.list_date = "2016-05-26" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-26" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-26`,
   MAX( CASE
        WHEN p.list_date = "2016-05-27" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-27" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-27`,
   MAX( CASE
        WHEN p.list_date = "2016-05-28" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-28" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-28`,
   MAX( CASE
        WHEN p.list_date = "2016-05-29" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-29" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-29`,
   MAX( CASE
        WHEN p.list_date = "2016-05-30" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-30" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-30`,
   MAX( CASE
        WHEN p.list_date = "2016-05-31" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-05-31" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-05-31`,
   MAX( CASE
        WHEN p.list_date = "2016-06-01" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-06-01" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-06-01`,
   MAX( CASE
        WHEN p.list_date = "2016-06-02" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-06-02" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-06-02`,
   MAX( CASE
        WHEN p.list_date = "2016-06-03" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-06-03" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-06-03`,
   MAX( CASE
        WHEN p.list_date = "2016-06-04" and p.list_available = "TRUE" THEN p.list_price
        WHEN p.list_date = "2016-06-04" and p.list_available = "FALSE" THEN CONCAT( p.list_price , ' FALSE')
        ELSE NULL END) `2016-06-04`
FROM `airbnb_lists` l JOIN airbnb_lists_price p ON l.list_id = p.list_id
GROUP   BY l.list_id
like image 871
Ilanus Avatar asked Dec 04 '15 09:12

Ilanus

1 Answers

I have created demo database for you & also added only some dummy data in tables.

You need to find out different dates which are available from list_price.

If you want dynamic columns from list_price then you can run following query:

SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX( CASE
            WHEN p.dates = "', dates,'" and p.list_avilable = "TRUE" THEN p.list_price 
            WHEN p.dates = "', dates,'" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , " FALSE") 
            ELSE NULL END) as `', dates,'`
           ' )
    )
FROM list_price

Which will give you dynamic columns:

  MAX( CASE
       WHEN p.dates = "4/12/2015" and p.list_avilable = "TRUE" THEN p.list_price 
       WHEN p.dates = "4/12/2015" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , " FALSE") 
       ELSE NULL END) as `4/12/2015`,
  MAX( CASE
       WHEN p.dates = "5/12/2015" and p.list_avilable = "TRUE" THEN p.list_price 
       WHEN p.dates = "5/12/2015" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , " FALSE") 
       ELSE NULL END) as `5/12/2015`

And need to make a query like below.

// $columns will have result of first query.
$query= "SELECT l.*,
          ".$columns."
          FROM `list_info` l JOIN list_price p ON l.list_id = p.list_id
         GROUP BY l.list_id";

You query will be like.

SELECT l.*,
       MAX( CASE
            WHEN p.dates = "4/12/2015" and p.list_avilable = "TRUE" THEN p.list_price 
            WHEN p.dates = "4/12/2015" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , ' FALSE') 
            ELSE NULL END) `4/12/2015`,
       MAX( CASE 
            WHEN p.dates = "5/12/2015" and p.list_avilable = "TRUE" THEN p.list_price 
            WHEN p.dates = "5/12/2015" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , ' FALSE') 
            ELSE NULL END) `5/12/2015`
    FROM `list_info` l JOIN list_price p ON l.list_id = p.list_id
    GROUP   BY l.list_id

I was able to find result like:

enter image description here

Update:

Or You can combine both sql query to one. You just need to replace $q = $this->db->select('*')->from('list_info')->get(); this with following raw query statement.

SET @sql= '';
SELECT GROUP_CONCAT(DISTINCT
                    CONCAT('MAX( CASE
                           WHEN p.dates = "', dates,'" and p.list_avilable = "TRUE" THEN p.list_price 
                           WHEN p.dates = "', dates,'" and p.list_avilable = "FALSE" THEN CONCAT( p.list_price , " FALSE") 
                           ELSE NULL END) as `', dates,'`
                           ' )
                   ) INTO @sql
FROM list_price;


SET @sql = CONCAT('SELECT l.*, ', @sql, ' 
                  FROM `list_info` l JOIN list_price p ON l.list_id = p.list_id
                  GROUP   BY l.list_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Some issues with this query in PHPMyadmin. Just shows rows count. Check if you get result in PHP.

like image 191
Somnath Muluk Avatar answered Oct 11 '22 15:10

Somnath Muluk
Sign in to Comment

Related questions

I need to display only one part of a JSON encoded object
Why php function str_word_count() returns different count from js equivalent?
Neteller TransferOut with PHP / CURL
Using node modules in Laravel
Cannot access to localhost/xampp/index.php
How to calculate longest undefeated streak using MySQL?
Symfony 2 - Calling a controller method from console command
Is there a way to have one server-sent-event running per user not per tab?
How to make a Website and API REST with Laravel with the same Controllers?
express checkout section missing in paypal manager hosted checkout set up
Displaying nested array in twig
Searching a sequence of bytes in a binary file in PHP?
PHP casting float to int returns different value
Generate signature in Elixir and PHP using hmac
Crontab Not Working Ubuntu
Overridden core class does not work when in artisan
yii2 GridView Search Fields outside Table
Localhost Error with Laravel 5.1 on Ubuntu 14.04
Error while installing Magento
properly escape URL slashes in regex

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!