How to properly use MySql SUM function in OOP PDO?

Question

I have a HTML table with numbers, I'm trying to get the SUM to display on the bottom of table beneath the numbers using MySql.

function test_sum(){

    $query = "SELECT SUM(test_column) FROM" . $this->table_name . " ";

    $stmt = $this->conn->prepare( $query );

    $stmt->execute();

    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    $this->test_column = $row['SUM(test_column)'];

}

Full code I'm trying to use to display the results

    $stmt = $class_name->test_sum();
    $row = $stmt->fetch(PDO::FETCH_ASSOC){
    echo "$row['SUM(test_column)']";
    }

score 1 · Accepted Answer · answered Jun 21 '19 at 05:07

1

You should add an alias to the sum in your MySQL query:

$query = "SELECT SUM(test_column) AS total FROM " . $this->table_name;
$stmt = $this->conn->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$this->test_column = $row['total'];

But note that using a concatenation to choose the table name in the query is generally bad practice, and open to SQL injection, if that table name would be coming from the outside. Typically we already know what the target table would be when creating a prepared statement.

answered Jun 21 '19 at 05:07

Tim Biegeleisen

387,723
20
200
263

Thank you for the quick response, I'm going to try that code now. $table_name = test_table is where the table name is coming from at top of my code not shown, how is that open to SQL injection? What do you mean table name coming from outside? – jay mey Jun 21 '19 at 05:09
If `$this->tablename` is defined by the user, it can be exploited to do SQL injection attacks. Table-names don't really ever change their name, so it rarely serves a purpose to use variables (as opposed to hard-coding it). See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Qirel Jun 21 '19 at 05:15
This is a departure from the code you posted above (and what is contained in my answer), and I don't understand what you are trying to do. – Tim Biegeleisen Jun 21 '19 at 06:25
I'm having a difficult time displaying the total, I've tried rewording numerous ways, the same format for display ing the table data does not display the total. also am I correct to use a separate function dedicated for the SUM of a column? – jay mey Jun 21 '19 at 19:47
@jaymey - `also am I correct to use a separate function dedicated for the SUM of a column` Sum is an aggregate function, think of Group By. Your basically compressing the table into a single row. So you cannot pull both the row data and the aggregate at the same time (unless you use a subquery). In this case a sub query is very inefficient, because it would do the sum for each row in the main query. So in short yes, I would do it separate from pulling the row data. That said if you pull all the row data, you could sum it in PHP instead. Eg. When you iterate over the Data. – ArtisticPhoenix Jun 22 '19 at 03:26
1

@ArtisticPhoenix This is no longer strictly true in MySQL 8+, as analytic functions have been introduced. Now it is possible to select every row and also compute the sum. – Tim Biegeleisen Jun 22 '19 at 06:50
@ArtisticPhoenix I had asked in a previous question on this site, the difference between jquery sum vs mysql sum. You mentioned using PHP above to pull all row data ( for multiple columns), do you think that it hugely matters which method is used? – jay mey Jun 22 '19 at 19:32
Client is typically a bit slower, plus you have no control over the hardware used. For example some users may have good computer some not so much. Mainly I mentioned that because if you are pulling all the data and looping over it already in PHP why not just count it there as you have the data you need so no need to ask the DB to do the work. It can be a bit more efficient if can build on something you are doing anyway, if that makes sense. Basically you could save a DB call if you already loop over all the data... – ArtisticPhoenix Jun 22 '19 at 19:35
For example `$total = 0; while($row = $stmt->fetch(PDO::FETCH_ASSOC)){ $total += $row['test_column']; ... other code ... }` It seems pretty obvious if you already do the while loop to just add it in there. But that's not in your question, so it's just a speculation on my part... – ArtisticPhoenix Jun 22 '19 at 19:39
@ArtisticPhoenix `SELECT *, SUM(test_column) OVER () FROM test_table` – Tim Biegeleisen Jun 23 '19 at 00:50

Your Common Sense · Answer 2 · 2019-06-22T03:44:44.093

1

First of all, the PDO part of your code works all right. There is no need to add any aliases, 'SUM(test_column)' is just as good. It is your "oop" (or rather returning the result from the function) is messed up:

you are assigning it to a variable instead of returning
you are trying use a (non-existent) return value as an array when it's just a scalar value already.

However, the PDO part could be improved as well, as PDO is a database wrapper that offers many helper methods to ease the routine operations, including one to return a single scalar value right off the statement.

Besides, there is no point in using prepare for a query that doesn't contain placeholders, making it sort of a cargo cult prepared statement. Simple query() would be enough

So your code could be much cleaner:

function test_sum()
 {
    $query = "SELECT SUM(test_column) FROM `$this->table_name`";
    return $this->conn->query( $query )->fetchColumn();
 }

Now, the returned value already contains the sum from the table, so you can echo it right away:

echo $class_name->test_sum();

edited Jun 22 '19 at 03:44

answered Jun 22 '19 at 03:38

Your Common Sense

152,517
33
193
313

This code works, thank you for your help. There's a lot of different ways of communicating to the database, for example, the previous answer adds `AS 'total'`, and I've seen that recommended in a lot of examples online, but I was having issues with displaying the results. – jay mey Jun 22 '19 at 19:16
If I were wanting to find the sum of multiple columns in this same table, would I be able to just add them in? `function test_sum() { $query = "SELECT SUM(test_column), SUM(test_column2) FROM `$this->table_name`"; return $this->conn->query( $query )->fetchColumn(); }` – jay mey Jun 22 '19 at 19:20
You can add any number of fields to your select statement, but of course you won't be able to use fetchColumn in this case and will need to use fetch() instead. – Your Common Sense Jun 23 '19 at 03:44

How to properly use MySql SUM function in OOP PDO?

2 Answers2