Easier way to to my mysqli query

Question

I have a query connected to a database and it works as designed but I feel like I have way more code then needed. Can someone help me out?

Basically I'm trying to get a sum of multiple columns and then assign a variable to the outcome. The variable has to be unique every time so I can echo them out later individually.

Would it be possible to use an array to select a different column and then use that same array to make the variables?

I hope it all makes sense what I'm trying to achieve.

This example is only 3 of the 12 queries I have.

$sql = ("SELECT SUM(Case_closed) AS sum_closed FROM production WHERE Datum = '$actual_time' AND jaar = '$year' ");
    $result = mysqli_query($connect, $sql)  ;
            $Case_closed    = $result->fetch_object()->sum_closed;


$sql = ("SELECT SUM(Case_handled) AS sum_handled FROM production WHERE Datum = '$actual_time'  AND jaar = '$year' ");
    $result = mysqli_query($connect, $sql)  ;
        $Case_handled   = $result->fetch_object()->sum_handled;


$sql = ("SELECT SUM(swap) AS sum_swap FROM production WHERE Datum = '$actual_time'  AND jaar = '$year' ");
    $result = mysqli_query($connect, $sql)  ;
        $swap   = $result->fetch_object()->sum_swap;

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 02 '16 at 18:29
this script if not used for public use so i am not worried about that. thanks for the heads up tho — Derik, May 02 '16 at 18:32
I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."* or *"Security isn't important now..."*. If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard, May 02 '16 at 18:36
You need to get in the habit of [accepting answers](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) which help you to solve your issues. You'll earn points and others will be encouraged to help you. — Jay Blanchard, May 02 '16 at 19:47

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

You can use multiple SUM's (example is based on prepared statements):

SELECT SUM(`Case_closed`) AS `sum_closed`, SUM(`Case_handled`) AS `sum_handled`, SUM(`swap`) AS `sum_swap` 
FROM `production` 
WHERE `Datum` = ?  
AND `jaar` = ?

Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe!

edited May 23 '17 at 12:31

Community

1
1

answered May 02 '16 at 18:32

Jay Blanchard

34,243
16
77
119

I followed @Little Bobby his advice and created a function for the query. Somehow when i use (?) instead of ($type) i do not get results – Derik May 02 '16 at 20:22
The `?`'s are there to illustrate how the query would look when used as a prepared statement. It is just an example as I pointed out in the answer. – Jay Blanchard May 02 '16 at 20:24
ye i tried to explain in a comment but somehow i could not get the code in correctly so i posted the answer – Derik May 02 '16 at 20:28
You cannot bind column or table names. – Jay Blanchard May 02 '16 at 20:30
so you would say the way i posted my answer is the correct way? – Derik May 02 '16 at 20:31
It is certainly one way of doing it. – Jay Blanchard May 02 '16 at 20:32

score 0 · Answer 2 · answered May 02 '16 at 20:28

found the solution by following @little Bobby his advice to make a prepared statement but i failed to to it for the column. Its currently set to ($type) but i would like to have it (?)

function resultaten($type){

$connection = mysqli_connect("localhost","root","","dbname");

if(mysqli_connect_errno()){
echo mysqli_connect_error();
exit(); 
}   
$query = ("SELECT SUM($type) AS sum_gesloten FROM productie WHERE Datum=? AND jaar=?");

$time = time();
$actual_time = date('d-m-Y', $time);
$year = date("Y");
$stmt = mysqli_prepare($connection,$query);

if($stmt){
mysqli_stmt_bind_param($stmt, 'ss', $actual_time, $year);
mysqli_stmt_bind_result($stmt, $typeresult);
mysqli_stmt_execute($stmt);
mysqli_stmt_fetch($stmt);

if(!empty($type)){
    echo "$type $typeresult";       
}else{
    echo "No results!";
}   }else{

echo "Error creating statement object!";
}
}

$Case_gesloten = resultaten('Case_gesloten'); echo $Case_gesloten;

You cannot bind column or table names like `SUM(?)` http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter — Jay Blanchard, May 02 '16 at 20:31

Easier way to to my mysqli query

2 Answers2