Getting the SUM in PDO

问题:

Below is my code and for some reason it's not giving me the SUM. This always returns 0. Why doesn't it work?

I've used if ($totSubmits==''){ to avoid blank fields in my database.

I also tried removing AS due_fees and using $dueAmont = $result[0], but no luck though.

$sql= "SELECT SUM(dueFees) AS due_fees FROM coursePayments WHERE studentId = $student"; 
$stmt = $c->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_NUM);
$dueAmont = $result['due_fees']; 
if ($dueAmont==""){
    $dueAmont = '0';
}
echo $student." due amount ".$dueAmont;

回答1:

Gah, use parameter binding! Also, fetchColumn() is nice and easy for single row / single column results

$sql= "SELECT SUM(dueFees) FROM coursePayments WHERE studentId = ?"; 
$stmt = $c->prepare($sql);
$stmt->execute(array($student));
$dueAmont = (int) $stmt->fetchColumn();

Addendum

When developing, always run your PHP environment with full error disclosure. Then you won't miss easy mistakes like in your code above. In php.ini

display_errors = On
error_reporting = E_ALL

回答2:

fetch(PDO::FETCH_NUM)

Will not bring you an associative array back. That's a numerically indexed array hence you cant find your field.

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

Do

fetch(PDO::FETCH_ASSOC)

And that is

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

That's what you need to get your alias back from.

Tip:

You can remove your if clause to check whether the value is blank and set it to 0.

$dueAmont = $result['due_fees']; 
if ($dueAmont==""){
    $dueAmont = '0';
}

Can simply be

$dueAmont = intval($result['due_fees']);