no results

[DrTrans]

when i run the function, I want it basically spit out

 

$month = MONTHNAME(payment_date);

$amount = SUM(payments)

 

Basically want it to spit out months and amount for that month..

 

Can you please assist. Thanks in advance.

 

function operatingyear() {
       print "<center><h3> OPERATING STATMENT REPORT [12-Month]</h3></center>";
       print "<hr>";
       print "<br />";
   
        getconnect();
        $active = "1";
        $query9 = "SELECT MONTHNAME(payment_date), SUM(payment) FROM payments GROUP BY YEAR(payment_date), MONTH(payment_date)";
        $result9 = mysql_query($query9);
    $sum = mysql_result($result9,0);
        }

 

 

 

[Pikachu2000]

$result9 = mysql_query($query9) or die( mysql_error() );

[DrTrans]

function operatingyear($daterange) {
   global $loginid;	
   global $daterange;   
       print "<center><h3> OPERATING STATMENT REPORT [12-Month]</h3></center>";
       print "<hr>";
       print "<br />";
   
        getconnect();
        $active = "1";
        $query9 = "SELECT MONTHNAME(pay_date), SUM(pay_amount) FROM payments GROUP BY YEAR(pay_date), MONTH(pay_date)";
        $result9 = mysql_query($query9) or die( mysql_error() );
    $sum = mysql_result($result9,0);
        
        print "$sum";
        
    

   	
}

 

Fixed the Mysql errors. However it still not putting out any information.  This is my first time working with SUM.

[xyph]

What's your database look like?

 

Here's my sample database

 

CREATE TABLE IF NOT EXISTS `sometable` (
  `date` date NOT NULL,
  `amount` decimal(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `sometable`
--

INSERT INTO `sometable` (`date`, `amount`) VALUES
('2012-07-10', '10.50'),
('2012-06-04', '15.25'),
('2012-07-19', '18.75'),
('2012-06-10', '5.50'),
('2012-07-06', '8.25');

 

And the following code...

<?php

mysql_connect('localhost','root','');
mysql_select_db('db');

$q = 'SELECT MONTHNAME(date), SUM(amount) FROM sometable GROUP BY YEAR(date), MONTH(date)';
$r = mysql_query($q) or die(mysql_error());

$sum = mysql_result($r,0);
echo $sum;


?>

 

... outputs

June

 

This is the way it should behave.

[DrTrans]

My db looks exactly the same, However, no results from the "echo"....

 

My Database:

pay_amount   |   pay_date |  pay_controller
----------------------------------------------------------
    107.13           2012-07-07         +
     98.10            2012-07-07          -
   

 

i even copied ur code and edited the fields in mysql statement to

 

 

function operatingyear($daterange) {
       print "<center><h3> OPERATING STATMENT REPORT [12-Month]</h3></center>";
       print "<hr>";
       print "<br />";
   
        getconnect();
        $q = 'SELECT MONTHNAME(pay_date), SUM(pay_amount) FROM payment GROUP BY YEAR(pay_date), MONTH(pay_date)';
        $r = mysql_query($q) or die(mysql_error());
        $sum = mysql_result($r,0);
        echo $sum;        
    

   	
}

 

 

[xyph]

There's a lot going on in your code that you haven't posted, so it's impossible to help.

 

Try copying and pasting directly, only changing the MySQL info and columns/table.

 

Also, try echoing mysql_num_rows to see if your SELECT statement is actually grabbing any rows.

[DrTrans]

That is all the code in the function.

 

on the echo mysql_num_rows($r);

 

It game me "4"

[xyph]

What about

 

var_dump( mysql_fetch_assoc($r) )

 

Again, I'm sure if you copy-pasted my snippet into a new, empty PHP file, you'd get the expected results. This leads me to believe the problem lies in code you haven't posted.

[DrTrans]

response to the var_dump();

 

array(2) { ["MONTHNAME(pay_date)"]=> string(3) "May" ["SUM(pay_amount)"]=> string(5) "-1127" }

[DrTrans]

Copied and Pasted into new file.. Same issue.

 

[DrTrans]

Saw an error in database, one of the dates was set as 2012-00-18.

 

Fixed the Dtae and Got the echo response as "May "

 

 

anyway to it to do

 

January - Amount

Feb - Amount

March  - Amount ..

 

 

Right now it only gives me an echo of " May "

[xyph]

You need to loop through your query results. There are examples in the manual. Check out

 

mysql_fetch_assoc