CODEWITHSUNDEEP
phparrays
Myt
Myt

Reputation: 204

PHP Arrays - getting sum of hours per month for certain job

I would appreciate some help with php arrays. I need to create a graph, but I can't seem to get the values from database the way I would like them to. So basically what I need are hours per month for certain job. Database looks something like this:

Date       |   Job   | Hours
2012-01-01 |  104    |   8.5

Every job has a different number, but I hope this helps a little.

I need to get the sum of hours per month spent on one job. So far, I have made an array for months and looped through them:

 $month_date_array = array('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12');
 foreach ($month_date_array as $month) {
 $sql = "SELECT * FROM database WHERE date LIKE '%-$month-%'";
 $result = mysql_query($sql) or die (mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $job[] = $row['job'];
    }
}

Okei, I made some changes and I am going to update my question: The year does matter, it can't group 2011 and 2012 together. The year comes from a dropdown menu - the user chooses what year. Next I added an array of jobs, because I just figured out that I can't take all jobs, so the job array looks something like this:

$job = array('22', '6', '12', '55');

Basically I would like to know how to get the sum of hours what somebody spent on doing one job per month. And I don't want to do it with SQL, I need arrays of some sort, because later I have to put together a graph.

Upvotes: 0

Views: 397

Answers (5)

VDP
VDP

Reputation: 6420

I disagree with most of the other answers as they group also same months from other years. Something like this would be more efficient. 

SELECT CAST(DATE_FORMAT(`Date`, '%Y-%m-01') AS DATE) month, SUM(`Hours`) hours
FROM `your_table`
WHERE `Job` = 104
GROUP BY month

http://sqlfiddle.com/#!2/8cf63/1

This technique will "round" your date to the first day of the month; thus, if you GROUP on those dates, you are grouping on months. This is nice because it combines the year and month together into one column for easy sorting and comparing and joining, if necessary.

Upvotes: 1

Anthony Hatzopoulos
Anthony Hatzopoulos

Reputation: 10557

If you want to learn how to manipulate and do this with arrays:

<?php
$jobs = array();
$month_date_array = array('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12');
foreach ($month_date_array as $month) {
    $sql = "SELECT * FROM database WHERE date LIKE '%-$month-%'";
    $result = mysql_query($sql) or die (mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $jobs[$row['job']][$month] += $row['hours'];
    }
}

But know that this is actually going to group all the years together. In other words November 2011, 2012 and 2013 will be added together if those dates exist.

You could also do this grouping and adding with SQL only.

http://sqlfiddle.com/#!2/416d9/5/0

# Group by Month and Year
SELECT YEAR(`Date`) as `Year`, MONTH(`Date`) AS `Month`, `Job`, SUM(`Hours`)
FROM `your_table`
GROUP BY `Year`, `Month`, `Job`

or http://sqlfiddle.com/#!2/416d9/6/0

# Group by Month combining the Years
SELECT MONTH(`Date`) AS `Month`, `Job`, SUM(`Hours`)
FROM `your_table`
GROUP BY `Month`, `Job`

Upvotes: 3

Abhishek Saha
Abhishek Saha

Reputation: 2564

How about the below query:

SELECT SUM(hours) FROM database 
WHERE DATE_FORMAT(date,'%m' ) = $month
GROUP BY  DATE_FORMAT(date,'%m' );

Upvotes: 0

Lalit Jain
Lalit Jain

Reputation: 136

You must chance sql query like that:

$sql = "SELECT * FROM database WHERE MONTH(date) = '".$month."'";
 $result = mysql_query($sql) or die (mysql_error());

    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $job[] = $row['job'];
    }

Upvotes: 0

Marc B
Marc B

Reputation: 360812

Rather inefficient, when you could have

SELECT month(date) AS month, COUNT(*) AS cnt
FROM database
GROUP BY month(date);

one single query doing everything your query loop is doing, and with far less overhead.

Upvotes: 1

Related Questions