count rows from multiple tables

[doddsey_65]

im trying to get the total forums, topics and posts from 3 different database tables named respectivly. I then need to add the results to an array.

 

I have tried the following:

 

$sql = "SELECT 
            sum(f.forums), sum(t.topics), sum(p.posts)
            FROM (
            SELECT COUNT(*) as forums FROM ".TBL_PREFIX."forums f
            UNION
            SELECT COUNT(*) as topics FROM ".TBL_PREFIX."topics t
            UNION
            SELECT COUNT(*) as posts FROM ".TBL_PREFIX."posts p)";
            
    $result = $db->query($sql) or die($db->printError($sql));
    $row = $result->fetch(PDO::FETCH_NUM);

    $stats['forums'] = $row['forums'];

 

but i get the following error:

 

Every derived table must have its own alias

 

Any advice?

 

[djlee]

AS

 

forums f

  |

forums AS f

 

[doddsey_65]

that wouldnt make any difference

[premiso]

Why are you summing it? A union statement alone should work just fine. The count gives you the total on a single line, so the SUM part is just not needed. I would remove that top level select and just use the UNION.

[doddsey_65]

i changed the sql to

 

$sql = "SELECT 
            *
            FROM (
            SELECT COUNT(*) as forums FROM ".TBL_PREFIX."forums AS f
            UNION
            SELECT COUNT(*) as topics FROM ".TBL_PREFIX."topics AS t
            UNION
            SELECT COUNT(*) as posts FROM ".TBL_PREFIX."posts AS p)";

 

but it throws the same error

 

[premiso]

I said remove the top level select:

 

SELECT COUNT(*) as forums FROM ".TBL_PREFIX."forums AS f
            UNION
            SELECT COUNT(*) as topics FROM ".TBL_PREFIX."topics AS t
            UNION
            SELECT COUNT(*) as posts FROM ".TBL_PREFIX."posts AS p

 

Is all you should use. Details make or break ya.

[Drummin]

I believe djlee was right about "AS" but why not query rows as separate queries and use results as you wish?  I guess UNION is fine for grabbing a combined result.  What works for you.

//Forums	
$queryf=" SELECT * FROM ".TBL_PREFIX."forums ";
$resultf=mysql_query($queryf);
$numf=mysql_num_rows($resultf);	
//Topics
$queryt=" SELECT * FROM ".TBL_PREFIX."topics ";
$resultt=mysql_query($queryt);
$numt=mysql_num_rows($resultt);
//Posts	 
$queryp=" SELECT * FROM ".TBL_PREFIX."posts ";
$resultp=mysql_query($queryp);
$nump=mysql_num_rows($resultp);
//You can then use these values as you wish i.e.
echo "Forums:$numf Topics:$numt Posts:$nump";

[premiso]

Drummin, you do realize that 1 query, vs several is always preferred and way more efficient. Why waste calls when you can do it one go?

 

Second, doing a select * with num_rows is just so.... inefficient and just wrong... it really saddens me that this advice was ever given. That would have to select all the rows, and they would be stored as a resource. He just wants the number of rows, which is easily obtained with a count(*) and done so way more efficiently without wasting resources.

 

So yea, just bad advice all around.

[Drummin]

Good advice, thanks.

[DavidAM]

For the record, the error message

Every derived table must have its own alias

Was complaining about the subquery after the first FROM clause:

 

 -- SELECT columns FROM (subquery) AS ALIAS ...
SELECT 
            sum(f.forums), sum(t.topics), sum(p.posts)
            FROM (
            SELECT COUNT(*) as forums FROM ".TBL_PREFIX."forums f
            UNION
            SELECT COUNT(*) as topics FROM ".TBL_PREFIX."topics t
            UNION
            SELECT COUNT(*) as posts FROM ".TBL_PREFIX."posts p) AS SubQuery

 

However, that query will not work anyway. The inner query (the UNIONs) will return three rows, with ONE COLUMN each. And that column name WILL BE "forums". I usually do this as:

 

SELECT 'Forums' AS RowType, COUNT(*) as RowCount FROM ".TBL_PREFIX."forums f
            UNION
            SELECT 'Topics', COUNT(*) FROM ".TBL_PREFIX."topics t
            UNION
            SELECT 'Posts', COUNT(*) FROM ".TBL_PREFIX."posts p

 

You get three rows (in this case) and I look at the first column to determine which is which