PDO SUM MYSQL table

Question

I am learning as i go and been picking up snippets of code as i go and been working on this for the past few days and now i have thrown the towel in to seek help.

I am trying to calculate the sum of 2 columns in my database using PDO.

here is my code

<?php
$host = "localhost";
$db_name = "dbname";
$username = "root";
$password = "root";

try {
    $con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// show error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}

$query = "SELECT SUM (fill_up) AS TotalFill, 
                 SUM (mileage_covered) AS Totalmiles 
                 FROM fuel_cost";

$row = $query->fetch(PDO::FETCH_ASSOC);
$total_fill = $row['TotalFill'];
$total_miles = $row['Totalmiles'];           
$myanswer = $total_fill/$total_miles;

//display the answer
echo $myanswer               
?>

I have also checked my database table and both columns are varchar(32)

the error I am getting is Call to a member function fetch() on a non-object I have searched into this but not sure what's the issue with the above code Thank You in advance

Abhishek Sharma · Accepted Answer · 2015-10-29T11:13:47.700

2

Try this code :-

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT SUM (fill_up) AS TotalFill, 
                 SUM (mileage_covered) AS Totalmiles 
                 FROM fuel_cost";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {

        $total_fill = $row['TotalFill'];
        $total_miles = $row['Totalmiles'];
        $myanswer = $total_fill / $total_miles;

//display the answer
        echo $myanswer;
        die;
    }
} else {
    echo "0 results";
}
$conn->close();
?>

edited Oct 29 '15 at 11:13

answered Oct 29 '15 at 10:58

Abhishek Sharma

6,689
1
14
20

1

I have adapted your answer for my query but its still giving me an error. $row = $con->query ("SELECT SUM (fill_up) AS TotalFill, SUM (mileage_covered) AS Totalmiles FROM fuel_cost"); Thanks for your answer :) – Richard Ransom Oct 29 '15 at 11:02
1

please check my updated code and try this code @RichardRansom – Abhishek Sharma Oct 29 '15 at 11:14
I really appreciate you help on this i am getting another error lol Trying to get property of non-object on the line if ($result->num_rows > 0) { – Richard Ransom Oct 29 '15 at 11:20
SELECT SUM (fill_up) AS TotalFill, SUM (mileage_covered) AS Totalmiles FROM fuel_cost – Abhishek Sharma Oct 29 '15 at 11:25
2

please execute this query in database – Abhishek Sharma Oct 29 '15 at 11:26
#1630 - FUNCTION dbname.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual – Richard Ransom Oct 29 '15 at 11:30
1

It was spaces in the query :) – Richard Ransom Oct 29 '15 at 11:32

PDO SUM MYSQL table

1 Answers1