phpcountmysql-num-rows

SELECT count rows from 2 tables and sum the results


I have the PHP code:

$query = mysqli_query($mysqli, "SELECT * FROM `table_1`");
$result = mysqli_num_rows($query);

$queryTwo = mysqli_query($mysqli, "SELECT * FROM `table_2`");
$resultTwo = mysqli_num_rows($queryTwo);

$number =  $result + $resultTwo;
return $number;

The point is that sometimes, the $number variable is returning NULL, when it should not supposed to do that.

I have always rows in those 2 tables, and the returned result should not be NULL, ever.

Is this a correct approach to sum the number of rows from 2 tables? I don`t understand why sometimes I get NULL instead of a number.


Solution

  • Well, I would suggest you to do it like

      select ( select count(*) from Table1 ) + ( select count(*) from Table2 ) 
      as total_rows
    

    executing this query and getting the value of total_rows will return you true result

    Or you can create a stored procedure to do the same thing. as explained below

    CREATE PROCEDURE sp_Test
    AS
    -- Create two integer values
    DECLARE @tableOneCount int, @tableTwoCount int
    
    -- Get the number of rows from the first table
    SELECT @tableOneCount = (SELECT COUNT(*) FROM Table1
                             WHERE WhereClause)
    SELECT @tableTwoCount = (SELECT COUNT(*) FROM Table2
                             WHERE WhereClause)
    
    -- Return the sum of the two table sizes
    SELECT TotalCount = @tableOneCount + @tableTwoCount