CODEWITHSUNDEEP
phpmysql
George
George

Reputation: 1114

How to compare data in a table with the current year and immediate past year

I have some data in MySQL table, I am trying to compare those data by the current year value and the immediate past year.

Books
|   id  |   Name    |
|   01  |   Maths   |
|   02  |   English |   

BooksTable
| Books |   value   |   Year    |
| 01    |   40      |   2012    |
| 02    |   30      |   2012    |
| 02    |   50      |   2013    |
| 01    |   50      |   2013    |
| 01    |   60      |   2014    |

I want an optimized and better way to compare the books value by the year (2013 and 2014), and be able to output it into an html table via php. This is how I would want it to appear in the HTML table

HTML TABLE
| BOOKS  |  2013    |   2014    |
| Maths  |   50     |   60      |
| English|   50     |   -       |

This is what I did:

  1. I first queried data for only 2013
  2. and then queried data for only 2014
  3. I made the comparison in the 2014 while statement

The errors I had 1. All the queries runs null if the 2014 query returns empty, since the output for 2013 query was dependent on 2014 query

UPDATE Please this is what i tried 

//query for the year 2013
SELECT
    a.id,
    a.name,
    b.books,
    b.value
FROM
    BOOKS a
JOIN
    BooksTable b
ON
    a.id=b.books
WHERE
    year=2013


//query for the year 2014
SELECT
    a.id,
    a.name,
    b.books,
    b.value
FROM
    BOOKS a
JOIN
    BooksTable b
ON
    a.id=b.books
WHERE
    year=2014

Please, I know I am doing the wrong thing, is there an optimized and better way to this? I would be grateful if someone could help. Thanks.

Upvotes: 2

Views: 262

Answers (5)

George
George

Reputation: 1114

In my own way, this is how I will solve it with php and mysql

    SELECT
        a.id,
        a.name,
        b.books,
        IFNULL(NULL,'0') AS  currentValue,
        IFNULL(b.value,0) as PastValue
    FROM
        `Books` a
    JOIN
        `BooksTable` b
    ON
        a.id=b.id
             and
        Year='2013'

UNION ALL

    SELECT
        a.id,
        a.name,
        b.books,
        IFNULL(b.value,0) as currentValue,
        IFNULL(NULL,0) as PastValue  

    FROM
        `Books` a
    JOIN
        `BooksTable` b
    ON
        a.id=b.id
            and
        Year='2014'

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

I would just execute the simplest query possible, and handle the display issues in PHP (not shown).

DROP TABLE IF EXISTS Books;

CREATE TABLE books
(book_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Name    VARCHAR(12) NOT NULL 
);
INSERT INTO books VALUES
(01  ,'Maths'),
(02  ,'English');

DROP TABLE IF EXISTS book_values;

CREATE TABLE book_values
(book_id INT NOT NULL 
,value   INT NOT NULL
,Year    INT NOT NULL
,PRIMARY KEY(book_id,year)
);

INSERT INTO book_values
VALUES
(01    ,40      ,2012    ),
(02    ,30      ,2012    ),
(02    ,50      ,2013    ),
(01    ,50      ,2013    ),
(01    ,60      ,2014    );

SELECT b.*,v.value,v.year FROM books b JOIN book_values v ON v.book_id = b.book_id WHERE v.year IN (2013,2014);
+---------+---------+-------+------+
| book_id | Name    | value | year |
+---------+---------+-------+------+
|       1 | Maths   |    50 | 2013 |
|       1 | Maths   |    60 | 2014 |
|       2 | English |    50 | 2013 |
+---------+---------+-------+------+

EDIT: I'm not too great at PHP or manipulating arrays within it, but something like this...

<?php

include('path/to/connection/statem.ent');

$query = "
SELECT b.*
     , v.value
     , v.year
  FROM books b
  JOIN book_values v
    ON v.book_id = b.book_id
 WHERE v.year IN (2013,2014)
 ORDER
    BY b.book_id
     , v.year;";

$old_array = array();

$result = mysqli_query($db,$query);

while($row = mysqli_fetch_assoc($result)){
$old_array[] = $row;
}

$new_array = Array();

foreach( $old_array as $v )
{
    if(!isset( $new_array[$v["Name"]][$v["year"]] ))
    {
        $new_array[$v["Name"]][$v["year"]] = 0;
    }
    $new_array[$v["Name"]][$v["year"]] += $v["value"];
}
print_r($new_array);

?>

... will produce an array like this... 

Array
(
    [Maths] => Array
        (
            [2013] => 50
            [2014] => 60
        )

    [English] => Array
        (
            [2013] => 50
        )

)

Hopefully, you can figure out how to spit that out to an html table.

Upvotes: 1

Pinx0
Pinx0

Reputation: 1258

You could use the following:

SELECT b.Name, IFNULL(bt1.value, '-') as PastYear, IFNULL(bt2.value, '-') as CurrentYear
FROM Books b
LEFT JOIN BooksTable bt1 ON b.id=bt1.Books AND bt1.Year = (SELECT bt1y.Year FROM BooksTable bt1y GROUP BY bt1y.Year ORDER BY bt1y.Year DESC LIMIT 1,1)
LEFT JOIN BooksTable bt2 ON b.id=bt2.Books AND bt2.Year = (SELECT bt2y.Year FROM BooksTable bt2y GROUP BY bt2y.Year ORDER BY bt2y.Year DESC LIMIT 0,1)

That will return the data in the same way you want to show it in html, so just loop it once:

output

Upvotes: 1

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this mate:

DROP PROCEDURE IF EXISTS sp_calc_books;
DELIMITER //
CREATE PROCEDURE sp_calc_books (IN in_year INTEGER(4), OUT out_value       INTEGER(11))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    START TRANSACTION;
        SET @min_year = 0;

        SELECT MIN(year) INTO @min_year FROM bookstable;

        SELECT SUM(b.value) INTO out_value FROM books b
        JOIN bookstable bt ON bt.book = b.id
        WHERE bt.year BETWEEN @min_year AND  in_year;
COMMIT;
END//
DELIMITER ;

Then you can call it thru:

CALL sp_calc_books(2014, @output_var);
SELECT @output_var;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Mysql does not have function to generate the pivot tables i.e. showing the row value as column. You can do it using dynamic sql query with prepared statement.

There are plenty of tutorials on it (mysql showing row data as column.) In your case the following should do the job.

set @sql = null;
select
  group_concat(distinct
    concat(
      'sum(case when bt.Year = ''',
      Year,
      ''' THEN bt.value ELSE 0 END) AS ',
      CONCAT('`',Year, '`')
    )
  ) into @sql
from BooksTable ;

SET @sql = concat('SELECT  b.Name,
                           ', @sql, 
                  'FROM     Books b
                            INNER JOIN BooksTable bt
                                ON b.id = bt.Books
                    GROUP   BY  b.Name');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Upvotes: 0

Related Questions