CODEWITHSUNDEEP
phpmysql
Enoch
Enoch

Reputation: 1011

Issue with Dynamic Pivot Table

Having some issues with my query in that it generates the required data between the dates specified (on live it is 2 date fields (date_from and date_to), but the order of data is in the wrong order. e.g it will appear 

JAN 2017 | MARCH 2017 | APRIL 2017 | FEB 2017

And the most baffling issue is that it generates data outside what you search. e.g if you search from 1-1-2017 to date, it adds December to the data but there is no data in the database containing december records.

The code used to generate the dynamic pivot chart is:

$sql = "
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'SUM(CASE WHEN EXTRACT(".$period." FROM redeem_pledge) = ',
          EXTRACT(".$period." FROM redeem_pledge),
          ' THEN AMOUNT else 0 END) AS `',
          EXTRACT(".$period." FROM redeem_pledge),
          '`'
        )
      ) AS `pivot_columns`
    FROM record_pledge
    WHERE redeem_pledge BETWEEN ? AND ?
    ORDER BY redeem_pledge asc
";
$stmt = $pdo->prepare($sql);
$date_from = $this->input->get('date_from') ? $this->input->get('date_from') : '2017-05-01';
$date_to   = $this->input->get('date_to') ? $this->input->get('date_to') : date('Y-m-d');
$stmt->execute([$date_from, $date_to]);
$row = $stmt->fetch();
$stmt->closeCursor();
$pivot_columns = $row['pivot_columns'];

$sql = "
    SELECT title AS `Pledge Purpose`, {$pivot_columns}
    FROM record_pledge t1
    JOIN setting_pledge_purpose ON t1.purpose_pledge = setting_pledge_purpose.id
    WHERE t1.redeem_pledge BETWEEN ? AND ?
    GROUP BY title asc WITH ROLLUP
";

$stmt = $pdo->prepare($sql);
$stmt->execute([$date_from, $date_to]);
$results = $stmt->fetchAll();
$stmt->closeCursor();

Image of Added data and unorganised data:

enter image description here

UPDATE echo of SQL- SEARCH FROM 01-01-2017(JAN) - 15-10-2017(OCT) :

SELECT title AS `Pledge Purpose`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201701 THEN AMOUNT else 0 END) AS `201701`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201702 THEN AMOUNT else 0 END) AS `201702`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201704 THEN AMOUNT else 0 END) AS `201704`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201705 THEN AMOUNT else 0 END) AS `201705`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201706 THEN AMOUNT else 0 END) AS `201706`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201708 THEN AMOUNT else 0 END) AS `201708`,
SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM redeem_pledge) = 201709 THEN AMOUNT else 0 END) AS `201709` 
FROM record_pledge t1 JOIN setting_pledge_purpose ON t1.purpose_pledge = setting_pledge_purpose.id 
WHERE t1.redeem_pledge BETWEEN ? AND ? GROUP BY title asc WITH ROLLUP

Upvotes: 0

Views: 67

Answers (1)

Barmar
Barmar

Reputation: 782148

You need to put the ORDER BY option in the GROUP_CONCAT function, not the SELECT query.

Also, you need to put quotes around the value returned by EXTRACT($period FROM redeem_pledge). If $period is a unit that returns multiple parts of the date, like YEAR_MONTH, it needs to be compared as a string because it will be 2017-01; if you don't quote it, it will be treated as a numeric subtraction.

$sql = "
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'SUM(CASE WHEN EXTRACT(".$period." FROM redeem_pledge) = \"',
          EXTRACT(".$period." FROM redeem_pledge),
          '\" THEN AMOUNT else 0 END) AS `',
          EXTRACT(".$period." FROM redeem_pledge),
          '`'
        )
      ORDER BY redeem_pledge ASC) AS `pivot_columns`
    FROM record_pledge
    WHERE redeem_pledge BETWEEN ? AND ?

";

Upvotes: 1

Related Questions