在mysql查询中使用LIMIT和UNION时获取总行数

Query:

SELECT
    tbl_a.id as aid,
    tbl_a.name as aname,
    null location,
    .....
    .....
    from tbl_a
    .........//left join to fetch some other data
    .........//where condition
UNION ALL
    0 aid,
    null aname,
    tbl_b.location as location,
    .....
    .....
    from tbl_b
    .........//left join to fetch some other data
    .........//where condition
limit '.$recordperpage.' OFFSET '.$offset.'

here,$recordperpage and $offset is dynamic.

now i am trying to fetch all no. of rows without limit.

exa:
No. of rows with limit: 20
No. of rows without limit: 50
i am trying to fetch no. of rows without limit(means 50).

So how to achive that? Thanks in advance.

Maybe SQL_CALC_FOUND_ROWS is something for you? Take a look here.

How to count all records but only retrieve (LIMIT) a specific number for display?

This query does the trick:

SELECT count(*) as count
FROM
(SELECT
    tbl_a.id as aid,
    tbl_a.name as aname,
    null location,
    .....
    .....
    from tbl_a
    .........//left join to fetch some other data
    .........//where condition
UNION ALL
    0 aid,
    null aname,
    tbl_b.location as location,
    .....
    .....
    from tbl_b
    .........//left join to fetch some other data
    .........//where condition
) union_table