Q:

MySQL SELECT COUNT From multiple tables in PHP

I have two tables in a database of different product brands that I'm trying to get a count of so that it combines the count of both given the description search. What I have so far is this:
$pages_query = mysql_query ("SELECT COUNT('Product Number') FROM brand1 WHERE description LIKE '%oven%' UNION ALL SELECT COUNT('Product Number') FROM brand2 WHERE description LIKE '%oven%'") ;

When I echo this, it spits out 60, which is the result for brand1 table only and does not add the count from the brand2 table (which I thought UNION ALL was supposed to do or do I have that incorrect?) The end number should be 173 because the other table has 113 results that match this query. 
Which would be the best way to add the count results into one number?
Thanks.

A:

A UNION should give you two rows, one for each separate count.  If you want the total of the two, you can add them in a subselect:
SELECT
  /* Add these two counts together and alias as total_count */
  (SELECT COUNT(*) FROM brand1 WHERE description LIKE '%oven%') 
  + (SELECT COUNT(*) FROM brand2 WHERE description LIKE '%oven%) AS total_count

MySQL is lenient about the contents or presence of a FROM clause, so you can omit it and use only the SELECT.
As a side note, it is not a typical database practice to have separate tables per thing like a brand.  Instead, you should probably have these combined into a single table, which has a column identifying the brand.  It is likely to simplify your life greatly down the line, if you are in a position to make such a change now.

A:

When you UNION, you're creating a row for each count.  You have to get a SUM of those two rows.
By the way, you were selecting COUNT('Product Number') - which is for all intents and purposes the same as COUNT(1).  If you want to check a particular column for a non-null value, you should name that column.  If Product Number is the name of your column, you should encapsulate it in back-ticks (`).  Regular single-quotes (') will merely define a string.
$sql = "SELECT SUM(rowcount)
        FROM (
            SELECT COUNT(1) AS rowcount 
            FROM brand1 
            WHERE description LIKE '%oven%' 
            UNION ALL 
            SELECT COUNT(1) 
            FROM brand2 
            WHERE description LIKE '%oven%'
        ) AS counts";
$pages_query = mysql_query ($sql) ;

