CODEWITHSUNDEEP
phpmysql
5ummer5
5ummer5

Reputation: 153

Add 2 row counts together in MySql

I am adapting something and I need to add 2 row counts together in mysql. So far I have

<? 
$result = mysql_query("SELECT *  FROM Table1 WHERE Field1 ='2' ");
$num_rows = mysql_num_rows($result);

$result2 = mysql_query("SELECT *  FROM Table2 WHERE Field2 ='6' ");
$num_rows2 = mysql_num_rows($result2);

$num_rows3 = ($num_rows + $num_rows2)

echo "$num_rows3";
?>

I can echo either $num_rows OR $num_rows2 fine but I need to do the calculation then echo $num_rows3.

I am probably doing something stupid here but I do not know mysql at all so I am trying to learn.

Thanks for the help!

Upvotes: 0

Views: 117

Answers (4)

Fabio Petrillo
Fabio Petrillo

Reputation: 682

Using only one query and counting before add, a possible code is

<? 

   $query  = "SELECT c1 + c2 FROM ";
   $query .= "(SELECT count(Field1) c1 FROM Table1 WHERE Field1 ='2') t1,";
   $query .= "(SELECT count(Field2) c2 FROM Table2 WHERE Field2 ='6') t2";

   $result = mysql_query($query);
   $value  = mysql_num_rows($result);

   echo "$value";
?>

Upvotes: 1

Linial
Linial

Reputation: 1154

This is just a suggestion even though you got your answer. If you want to add those into ONE MYSQLI query you could use this:

SELECT sum(cnt) from 
(SELECT COUNT(*) cnt FROM T1 WHERE Field1=2 union all 
 SELECT COUNT(*) cnt FROM T2 WHERE Field2=6) a

I just don't see the point in fetching all data in SELECT * FROM Where all you do is mysql_num_rows($result)

Hope this helps, and maybe improves your code.

Good Luck!

Here is just a demo IN SQLFiddle, so you can see this in action:

SQLFiddle Demo

Upvotes: 2

Ende Neu
Ende Neu

Reputation: 15783

You could also have one single query for both counts:

SELECT count(t1.id), count(t2.id)
FROM (SELECT id FROM Table1 WHERE Field1 ='2') t1,
      (SELECT id FROM Table2 WHERE Field2 ='6') t2

Also note that you are missing a ; when summing the counts.

Upvotes: 3

5ummer5
5ummer5

Reputation: 153

I was missing the ; after the calculation!!

Upvotes: 1

Related Questions