使用php检索mysql中两个不同的表列字段的总和

Question

Hey guys I m stuck over this query

I have two different table with same column name but different values with primary key id

Table 1 : q1

id ability_to_delegate communication confidence commitment

1           0                0            1          0          

2           0                0            0          0

3           0                0            0          0

4           1                0            1          0

Table 2 : q2

id ability_to_delegate communication confidence commitment

1           0                0            2          1          

2           0                0            1          1

3           0                0            0          0

4           0                0            1          1

Now what I want is to sum the values of two different tables with same field name but different IDs.

For example I want values of confidence field from table q1 with id = 4 i.e 1 and values of confidence field from table q2 with id = 1 i.e 2 to be added i.e 3.

I tried using union but not getting the rseult

$mresult=mysqli_query($con,"select sum(sm) from 

(select confidence sm from q1 where id='$id' 

union 

select confidence sm from q2 where id='$id') ss");

while ($row1 = mysqli_fetch_assoc($mresult)){
echo "Sum ". $row1['ss'];
}

I m getting warning

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, object given in .... on line 89

Please help me out

Answer 1

The query to accomplish what you're looking for is

SELECT `q1`.`confidence` + `q2`.`confidence` AS `TotalConfidence`
FROM `q1`, `q2`
WHERE `q1`.`id` = 4
AND `q2`.`id` = 1

You can plug this into your PHP and substitute the variables where appropriate.

$mresult=mysqli_query($con,"SELECT `q1`.`confidence` + `q2`.`confidence` AS `TotalConfidence` FROM `q1`, `q2`WHERE `q1`.`id` = '{$q1id}' AND `q2`.`id` = '{$q2id}'");

while ($row1 = mysqli_fetch_assoc($mresult)){
    echo "Sum ". $row1['TotalConfidence'];
}