Select total additions in this month

[Mutley]

I'm trying to do a donate script that selects the total donation amount made in the current month.

 

<?php

$result = mysql_query("SELECT SUM(amount), dateline FROM forum_paymenttransaction");
while($row = mysql_fetch_array( $result ))
{
	$amount = $row['amount'];
}

?>

 

 

The above gives the total amount of money and selects the date field of when the donation was made (which is a unix timestamp) the part I'm stuck at, is how to only show the total donation for THIS present month.

 

Any ideas?

 

Thanks in advance.

 

[jonsjava]

Do you track per month? If so, we could use that info as well. Can we see the table schema?

[webent]

add WHERE datefield = $currentmonth to the end of the query...

[Mutley]

The table is made up of, the user ID, the amount they donated and a UNIX Timestamp to when they donated.

 

 

[haku]

Use this (note - not tested so there may be some typos):

 

$current_date = time();
$current_month = date("m", $current_date);
$current_year = date("Y", $current_date);
switch($current_month)
{
    case 1:
    case 3:
    case 5:
    case 7:
    case 8:
    case 10:
    case 12:
       $number_of_days = 31;
       break;
    case 4:
    case 6:
    case 9:
    case 11:
        $number_of_days = 30;
    case 2:
        $number_of_days = (date("L", $current_date) == 0) ? 28 : 29;
        break;
}
$start_of_month = mktime(0, 0, 0, $current_month, 1, $current_year);
$end_of_month = mktime(23, 59, 59, $current_month, $number_of_days, $current_year);
$query = "SELECT SUM(amount), dateline FROM forum_paymenttransaction WHERE date >= {$start_of_month} AND date <= {$end_of_month}";

[Mutley]

Thanks a lot Haku, I tweaked it to echo the result but get an error (I think your code is correct though, just bad SQL?):

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in [path]/modules/donations.php on line 30

 

<?php
$current_date = time();
$current_month = date("m", $current_date);
$current_year = date("Y", $current_date);
switch($current_month)
{
	case 1:
	case 3:
	case 5:
	case 7:
	case 8:
	case 10:
	case 12:
	   $number_of_days = 31;
	   break;
	case 4:
	case 6:
	case 9:
	case 11:
		$number_of_days = 30;
	case 2:
		$number_of_days = (date("L", $current_date) == 0) ? 28 : 29;
		break;
}
$start_of_month = mktime(0, 0, 0, $current_month, 1, $current_year);
$end_of_month = mktime(23, 59, 59, $current_month, $number_of_days, $current_year);
	$result = mysql_query("SELECT SUM(amount), dateline FROM forum_paymenttransaction WHERE date >= {$start_of_month} AND date <= {$end_of_month}");
	while($row = mysql_fetch_array( $result ))
	{
		$display = $row['amount'];
		echo $display;
	}
?>

 

[haku]

Use this and paste the output in this thread.

 

$result = mysql_query("SELECT SUM(amount), dateline FROM forum_paymenttransaction WHERE date >= {$start_of_month} AND date <= {$end_of_month}") or die(mysql_error());

[Mutley]

It was 'date' should of been 'dateline', I fixed this but not get this error:

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[haku]

I kind of thought that may be illegal, but I'd never tried it. You can't do what you want in 1 query. Use this:

 

$result 1= mysql_query("SELECT SUM(amount) FROM forum_paymenttransaction WHERE date >= {$start_of_month} AND date <= {$end_of_month}") or die(mysql_error());
$result2 = mysql_query("SELECT dateline FROM forum_paymenttransaction WHERE dateline >= {$start_of_month} AND dateline <= {$end_of_month}") or die(mysql_error());

[Mutley]

Don't understand what you mean, should I do 2 queries?

[PFMaBiSmAd]

Try this query (and forget that horrendous php code to come up with a start and end unix timestamp) -

 

$result = mysql_query("SELECT SUM(amount), dateline, FROM_UNIXTIME(dateline) as stddate
FROM forum_paymenttransaction
WHERE MONTH(stddate) = MONTH(CURDATE()) AND YEAR(stddate) = YEAR(CURDATE()) GROUP BY stddate");

[haku]

Don't understand what you mean, should I do 2 queries?

 

Yes, using the two queries I gave you.

 

Although what the guy above me (who is still obviously pissed off that I was right last night) may serve you better. Try it out first.

[Mutley]

Try this query (and forget that horrendous php code to come up with a start and end unix timestamp) -

 

$result = mysql_query("SELECT SUM(amount), dateline, FROM_UNIXTIME(dateline) as stddate
FROM forum_paymenttransaction
WHERE MONTH(stddate) = MONTH(CURDATE()) AND YEAR(stddate) = YEAR(CURDATE()) GROUP BY stddate");

 

Thanks, nice and compact, although:

 

Unknown column 'stddate' in 'where clause'

[PFMaBiSmAd]

Like the error states, it does not like the alias name in the WHERE clause. 

 

Do this instead (tested this time to make sure the syntax works) -

 

$query = "SELECT SUM(amount), dateline FROM forum_paymenttransaction
WHERE MONTH(FROM_UNIXTIME(dateline)) = MONTH(CURDATE()) AND
YEAR(FROM_UNIXTIME(dateline)) = YEAR(CURDATE()) GROUP BY MONTH(FROM_UNIXTIME(dateline))";

[Mutley]

Hmm, I can't get the die(mysql_error()) to work but I have an error still:

 

 

$result = "SELECT SUM(amount), dateline FROM forum_paymenttransaction
WHERE MONTH(FROM_UNIXTIME(dateline)) = MONTH(CURDATE()) AND
YEAR(FROM_UNIXTIME(dateline)) = YEAR(CURDATE()) GROUP BY MONTH(FROM_UNIXTIME(dateline))" or die(mysql_error());
while($row = mysql_fetch_array( $result ))
	{
		$display = $row['amount'];
		echo $display;
	}

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in [path]/modules/donations.php on line 7

 

Thanks so far!

[PFMaBiSmAd]

What I posted was just the query string ($query). It needs to be executed to give you your $result variable.

 

$result = mysql_query($query) or die("Query failed: " . mysql_error());

[haku]

Thats because you have only defined the query, you haven't added the mysql_query function to it.

[Mutley]

Okay, the SQL works and displays the correct amount in the database but the script displays "Resource id #29", no errors, so it must be working.

 

Can't understand why it isn't displaying the correct amount, no idea what "Resource id #29" means?

 

 

$query = "SELECT SUM(amount), dateline FROM forum_paymenttransaction
WHERE MONTH(FROM_UNIXTIME(dateline)) = MONTH(CURDATE()) AND
YEAR(FROM_UNIXTIME(dateline)) = YEAR(CURDATE()) GROUP BY MONTH(FROM_UNIXTIME(dateline))" or die(mysql_error());

$result = mysql_query($query) or die("Query failed: " . mysql_error());
echo $result;

[AndyB]

$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_array($result); // abstract resource into an array named row
echo $row['some_variable_you_want_displayed'];

[Mutley]

Thanks Andy, although the Resource message has gone it is just blank now, no errors or any text.

 

 

<?php
$query = "SELECT SUM(amount), dateline FROM forum_paymenttransaction
WHERE MONTH(FROM_UNIXTIME(dateline)) = MONTH(CURDATE()) AND
YEAR(FROM_UNIXTIME(dateline)) = YEAR(CURDATE()) GROUP BY MONTH(FROM_UNIXTIME(dateline))" or die(mysql_error());

$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_array($result);
echo $row['amount'];
?>

[AndyB]

http://www.tizag.com/mysqlTutorial/mysqlsum.php

 

there's an example of the SUM syntax in use and displayed.  Alternatively use an alias for SUM(whatever)