0

I am querying the database to return a simple array. IE: "SELECT * FROM contacts LIMIT 50"

But what i'm trying to figure out is, what if the user only has 10 contacts. I would like to show a response saying (10 of 10) contacts showing.

I know I could run 2 queries on the database, a. SELECT * FROM contacts (and then count the results) b. SELECT * FROM contacts (and then find the difference of A and B

But the problem lies when a user has 2,000 contacts which wouldn't be uncommon. and then querying the database every time the user selects the "contacts" page.

So is there a simpler way to do this? or am I on the right track?

Any light would be appreciated. Cheers.

Justin
  • 2,348
  • 6
  • 39
  • 71
  • 1
    Justin, I think this topic has the answers you need: http://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying. The search term you were looking for is "MySQL pagination". – Greg Feb 28 '11 at 00:59
  • Thank you Greg, that does seem like the right solution. – Justin Feb 28 '11 at 01:04

3 Answers3

1

You can run SELECT COUNT(*) FROM contacts to get the total number of contacts. Another solution -

SELECT  SQL_CALC_FOUND_ROWS * from contacts limit 50;// get up to 50 records
SELECT FOUND_ROWS(); // returns the number of rows in contact
a1ex07
  • 35,290
  • 12
  • 78
  • 96
1

Another approach

select *,
     (select count(*) from table) as `found`,
     (select 10 - count(*) from table) as missing
from table limit 10
Nicola Cossu
  • 49,868
  • 15
  • 89
  • 95
0

Hi you can query "select * from contacts" and then display records depending on your page limit (eg. 50). So you have total records also.

OR

you can do following to get number of records in your table

SELECT COUNT(*) FROM CONTACTS

after you query "SELECT * FROM contacts LIMIT 50"

Regards

user427969
  • 3,782
  • 4
  • 44
  • 72