Add result of multiple sql queries

Question

I am trying to add the result of 3 SQL queries. All 3 queries return integer values.

How can I add the results from the 3 SQL queries into a variable and echo it? The code:

<?php
define('HOST','mywebsite.com');
define('USER','username');
define('PASS','password');
define('DB','imagebase');
$con=mysqli_connect(HOST,USER,PASS,DB);
if($_SERVER['REQUEST_METHOD']=='POST'){
    $val1=$_POST['sval1'];
    $val2=$_POST['sval2'];
    $val3=$_POST['sval3'];
    $sql="select price from images where name='$val1'"; //returns 100
    $sql1="select price from images where name='$val2'"; //returns 100
    $sql2="select price from images where name='$val3'"; //returns 100
    $result=mysqli_query($con,$sql);

    $count=mysqli_num_rows($result);
    $result1=mysqli_query($con,$sql1);

    $count1=mysqli_num_rows($result1);
    $result2=mysqli_query($con,$sql2);

    $count2=mysqli_num_rows($result2);

    if ($count==1) {
        $res1=$count;
    } 
    if ($count1==1) {
        $res2=$count;
    } 
    if ($count2==1) {
        $res3=$count;
    } 

    $final=$res1+$res2+$res3;  //should return 300 but returns 3
    echo $final;

    mysqli_close($con);

} else {
    echo 'Error Updating Price';
    mysqli_close($con);
}
?>

Haven't you already? $final=$res1+$res2+$res3; echo $final; — ceejayoz
– ceejayoz, Commented May 1, 2018 at 17:15
It's difficult to answer this without seeing the queries. It might be that you can just construct a query to return the total. — T Gray
– T Gray, Commented May 1, 2018 at 17:16
You can use a single query for this. SELECT SUM(price) FROM images WHERE name IN (?, ?, ?). Also, you're open to SQL injection attacks. Read about PDO - phpdelusions.net/pdo — waterloomatt
– waterloomatt, Commented May 1, 2018 at 17:22
@Melwin that's because mysqli_num_rows returns the number of rows in the query result, not the actual result. So each time you call it, it returns 1. You need to fetch the results from the queries before trying to add them. Or, you could get all three in one query as the other comment suggests. Either way, you'll have to fetch. mysqli_num_rows will not do it. — Don't Panic
– Don't Panic, Commented May 1, 2018 at 17:27

spencer7593 · Accepted Answer · 2018-05-01 18:00:27Z

1

WARNING code in question is VULNERABLE to SQL Injection! Don't do this. Any potentially unsafe values that are included into SQL text must be properly escaped. The preferred pattern is to use prepared statements with bind placeholders.

To address the specific question that was asked: we would need to fetch rows from the resultsets, and accumulate the values returned for price.

It doesn't look like we are concerned with the number of rows that are returned; by each query, so there's not really a reason to call num_rows function.

$tot = 0;

$result=mysqli_query($con,$sql);
while( $row = $result->fetch_assoc() ) {
    $tot += $row['price'];
}

$result1=mysqli_query($con,$sql1);
while( $row = $result1->fetch_assoc() ) {
    $tot += $row['price'];
}

$result2=mysqli_query($con,$sql2);
while( $row = $result2->fetch_assoc() ) {
    $tot += $row['price'];
}

echo "tot = " . $tot;

But why that whole rigmarole of running three separate queries? If what we want is a total, we could have MySQL calculate that for us.

Also, the object oriented pattern is much easier than the procedural pattern.

$sql = 'SELECT SUM(i.price) AS tot_price
          FROM images i
         WHERE i.name IN ( ? , ? , ? )';

if( $sth = $con->prepare($sql) ) { 
   $sth->bind_param('sss',$val1,$val2,$val3);
   if( $sth->execute() ) {
       $sth->bind_result($tot_price);
       if( $sth->fetch() ) {
          echo "tot_price = " . $tot_price;
       } else {
         // no row returned 
       }
       $sth->close(); 
   } else {
      // handle error in execute
} else {
   // handle error in prepare
}

edited May 1, 2018 at 18:00

answered May 1, 2018 at 17:32

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Don't Panic Over a year ago

Nice answer! I didn't realize you could fetch_assoc on mysqli statements. I thought you had to fetch into bound variables. I guess I'm a bit rusty with mysqli.

spencer7593 Over a year ago

@Don'tPanic.. i've probably got it wrong, i should refrain from posting answers including mysqli... i'm more familiar with PDO. (Answer edited)

Don't Panic Over a year ago

Yeah, same here. It's so much easier to use.

spencer7593 Over a year ago

I also see in the first code block in my answer, I added oo-style calls mixed in with OP original procedural calls... ACCCKKK. i need more practice changing gears, switching from PDO to mysqli, and switching from oo-style to procedural style. But I absolutely refuse (in good conscience) to switch gears into including any code that follows a pattern that appears to be vulnerable to SQL Injection.

Giacomo Penuti · Accepted Answer · 2018-05-01 17:25:27Z

0

Inside your if statements, you forgot to change $count to $count1 and $count2 in second and third statements. Also, are you sure you want to check that $count, $count1, $count2 are equal to 1? You may want to check if those variable have falsy value so if($count) etc. After that, you need to initialize $res1, $res2, $res3 to 0 before the if statement, otherwise it can occour you get error later when summing $res variables that are not initialized due to falsy if statement before.

answered May 1, 2018 at 17:25

Giacomo Penuti

1,06811 silver badges14 bronze badges

Collectives™ on Stack Overflow

Add result of multiple sql queries

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related