Get count of posts from a union mysql query?

Question

SELECT u.id       AS pid    ,
       b2.id      AS id     ,
       b2.message AS MESSAGE,
       b2.uid AS uid,
       b2.date    AS DATE
FROM   (
       (SELECT  b.id         AS id     ,
                b.pid        AS pid    ,
                b.message    AS MESSAGE,
                b.uid    AS uid,
                b.date       AS DATE
       FROM     wall_posts   AS b
                JOIN Friends AS f
                ON       f.id = b.pid
       WHERE    f.buddy_id    = '1'
       AND      f.status      = 'b'
       ORDER BY DATE DESC
       LIMIT    0, 10
       )

UNION
         (SELECT  id     ,
                  pid    ,
                  MESSAGE,
                  uid,
                  DATE
         FROM     wall_posts
         WHERE    pid = '1'
         ORDER BY DATE DESC
         LIMIT    0, 10
         )
       )          AS b2
       JOIN Users AS u
       ON       b2.pid    = u.id
WHERE    u.banned         ='0'
AND      u.email_activated='1'
ORDER BY DATE DESC
LIMIT    0, 10

Is the code. Not sure how I would get the post count with this. I know normally i would do select count(*) as num I tried

So what i did i took

SELECT u.id       AS pid    ,
       b2.id      AS id     ,
       b2.message AS MESSAGE,
       b2.uid AS uid,
       b2.date    AS DATE
FROM

and changed it to

SELECT COUNT(u.id       AS pid    ,
       b2.id      AS id     ,
       b2.message AS MESSAGE,
       b2.uid AS uid,
       b2.date    AS DATE) as num
FROM

and did something similar for all the the select statements, well that didn't work, kept getting errors like #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS pid , b2.id AS id , b2.message AS MESSAGE, b' at line 1. So how would i go about getting the count? I need the count for my pagination php class.

Answer 1

The COUNT function doesn't allow the multiple columns you have. It can be COUNT(*) or COUNT(column_name), where there is a single column name. The COUNT(*) format counts total number of rows, where the COUNT(column_name) returns count of the non-null values for the the specified column.

So, the next step is to alter your COUNT in the SELECT. Then, you can go from there if other issues exist.

Answer 2

mysql_num_rows in php seems to pull it off without modifying the sql.