Get total number of rows when using LIMIT and UNION in mysql query

Question

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.

Really I do not understand what your problem If you want to all record without limit then `remove limit clause` — Sadikhasan, Jul 15 '14 at 09:02
The problem is i use pagination, so i have to know what is the total no. of rows? — DS9, Jul 15 '14 at 09:03
That contain no. of records within limit. suppose: total records without limit is 50, but using limit is 20, then your method return 20, not 50. — DS9, Jul 15 '14 at 09:07
Have a look at http://stackoverflow.com/questions/24538115/preg-replace-removing-all-but-the-end-of-query — VMai, Jul 15 '14 at 09:13
so you use the same query without limit to bring the total number of records but use count instead of selection and save that in a variable and pass to the pagination may be ajax — Mobasher Fasihy, Jul 15 '14 at 09:23

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

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?

edited May 23 '17 at 10:32

Community

1
1

answered Jul 15 '14 at 09:21

Darloz

165
11

score 0 · Answer 2 · answered Jul 15 '14 at 09:28

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

Get total number of rows when using LIMIT and UNION in mysql query

2 Answers2