CODEWITHSUNDEEP
phpmysqlpdo
Mo A
Mo A

Reputation: 45

PHP Average values in column based on year date

Im working on webpage that should average values in a given column based on the current year. Ive got the following code:

public function getElecReadings(){
        try {
            $stmt = $this->dbconn->prepare("SELECT AVG(ElecUsage), DateAdded FROM elec_readings  WHERE AccountNumber = '" . $_SESSION['user_session'] . "'");
            $stmt->execute();
            return $stmt;
        } catch (Exception $e) {

        }
    }

As i understand that should select the average from all the values within the ElecUsage column. However i was wondering how i can select all the average values from the column based on the current year that were in. I know to get the current year i can do something along the lines of : <?php echo date("Y"); ?> which will echo out 2016.

My DateAdded column is stored as a date type. So today would be 2016-04-19. Is there any way to extract the 2016 from the column and apply it to the select statement and then display the averaged value in a textbox on my HTML page?

Thanks

Upvotes: 0

Views: 276

Answers (4)

David Guerreiro
David Guerreiro

Reputation: 51

I would do something like this :

public function getElecReadings(){
    try {
        $stmt = $this->dbconn->prepare("SELECT AVG(ElecUsage), DateAdded FROM elec_readings  WHERE AccountNumber = '" . $_SESSION['user_session'] . "' AND CurrentYear LIKE '%2016'");
        $stmt->execute();
        return $stmt;
    } catch (Exception $e) {

    }
}

It worked for me. Just grab the year with PHP as you said before and then ouput that variable instead.

Upvotes: 1

George Pant
George Pant

Reputation: 2117

You can also get current year with mysql using CURDATE(). Just add this condition to your select statement

AND YEAR(DateAdded) = YEAR(CURDATE())

Upvotes: 0

HSLM
HSLM

Reputation: 2012

its about your query. You have to select avg from the table where year(date) = 'current_year'

so your code could be like this :

public function getElecReadings(){
        try {
            $current_year = date('Y');
            $stmt = $this->dbconn->prepare("SELECT AVG(ElecUsage), 
            DateAdded FROM elec_readings  WHERE year(DateAdded) = '$current_year' and AccountNumber = '" .$_SESSION['user_session'] . "'");
            $stmt->execute();
            return $stmt;
        } catch (Exception $e) {

        }
    }

also you can use another query to extract every average for every year:

SELECT AVG(ElecUsage), year(DateAdded) FROM elec_readings GROUP BY YEAR(DateAdded)

Upvotes: 1

Tommy
Tommy

Reputation: 366

You basically answered the question yourself:

$year = date("Y");
$year = "$year%";
$stmt = $this->dbconn->prepare("SELECT AVG(ElecUsage), DateAdded FROM 
elec_readings  WHERE AccountNumber = '" . $_SESSION['user_session'] . "' 
AND DateAdded LIKE '$year'");

That will extract all the account numbers where the year is 2016-andsomething, which I think is what you want?

Upvotes: 1

Related Questions