Mysql Count & Where clause

[bigheadedd]

Hi,

 

I imagine theres a simple way of doing this, but I can't think what it could be.

Basically I am making a simple query to a database and fetching rows. Each 'page' has 10 records each, and is dealt with by using OFFSET '#number'.

 

What i'm wondering though, is if there is an easy way of getting the required rows (say number 40-50), but also get the total number of posts that exist.

 

$result = mysql_query("SELECT * FROM articles OFFSET 40 LIMIT 10");

 

but also combine in the same query this:

 

$total = mysql_query("SELECT COUNT(article_id) AS count FROM articles");

 

Is there a simple way of doing this with two queries rather than two? Seems a little excessive with two.

 

Thanks in advance!

E

[Psycho]

For pagination you will want to run two queries. One to get the records for the current page and a second to get the total number of records across every page. The only one-query solutions I can think of would be less efficient: 1) query all the records and then filter the records in PHP (pretty stupid method) or do some type of sub-query that is joined to the results. But, again, that would be less efficient.

[QuickOldCar]

Exactly what Psycho said, there was a good pagination tutorial at phpfreaks, unfortunately it seems the link is gone now.

http://www.phpfreaks.com/tutorial/basic-pagination/

 

I have 2 pagination scripts you can check out and the code for them to get a better idea.

http://get.blogdns.com/paginate/

 

This one is hard set to 10 per page and jumping ahead and back pages in groups

http://get.blogdns.com/dynaindex/paginate/

[bigheadedd]

Thats great, thanks guys!

[Psycho]

The tutorials are still there - just hard to find: http://www.phpfreaks.com/tutorial/basic-pagination

 

This site has been pretty much converted to a forum only existence.