Webmaster Forum

Go Back   Webmaster Forum > Web Development > Coding Forum

Coding Forum Problems with your code? Discuss coding issues, including JavaScript, PHP & MySQL, HTML & CSS, Flash & ActionScript, and more.


Reply
 
Thread Tools Display Modes
Share |
  #1  
Old 09-17-2008, 07:04 PM
yangyang's Avatar
yangyang yangyang is offline
Contributing Member
 
Join Date: 08-30-07
Posts: 228
iTrader: 0 / 0%
How to retrieve the total number of rows when using LIMIT X, XXX

With php+mysql, $results -> num_rows returns the number of rows of the current query, however with LIMIT X, XXX in that query the $results -> num_rows only contains the LIMITED number of rows rather than the total amount of rows meeting the WHERE clause without LIMIT.

e.g. With LIMIT 0, 100 in my query $results -> num_rows returns 100 rather than 2000 which is the total number of rows had I not limited the returning results for paging.

Some search site has the capability of paging big amount of results while displaying also the total amount. Does it require 2 or more queries to do this? Is it possible to do it with just 1 query?

So what's the most efficient way to achieve this:


Quote:
Searching results 1 - 100, 2,653 total
...
...
 
Reply With Quote

Advertisement

Advertisement

  #2  
Old 09-17-2008, 11:58 PM
Boogle's Avatar
Boogle Boogle is offline
Contributing Member
 
Join Date: 09-03-07
Location: England
Posts: 649
iTrader: 0 / 0%
Well normally, the paged results are only loaded into a portion of the page, so you would only refresh that part of the page and therefore the numbers, 1-50 of 2,000 are always going to be there, you just update the 1-50 bit, to 51-100 for instance.

I don't really know PHP but can't you have globally declared variables? You can have the total amount returned as a public variable.

Boog's
 
Reply With Quote
  #3  
Old 09-21-2008, 09:27 AM
marc_gfx marc_gfx is offline
Contributing Member
 
Join Date: 07-01-06
Posts: 126
iTrader: 0 / 0%
two queries, not tested but something like this:

$sql = 'Select SQL_CALC_FOUND_ROWS * FROM table t Limit 1';
$result = mysql_query($sql);

$sql = 'Select FOUND_ROWS( ) as count';
$rtotal = mysql_query($sql);

$total = $rtotal['count'];
 
Reply With Quote
  #4  
Old 09-21-2008, 06:27 PM
yangyang's Avatar
yangyang yangyang is offline
Contributing Member
 
Join Date: 08-30-07
Posts: 228
iTrader: 0 / 0%
Thank you both, FOUND_ROWS is a great way to do it.

However this also arouses my concern about the expenses of getting the total number of rows meeting the criteria. Doesn't MySQL have to perform all the algorithmic comparing actions needed just as when it's returning all results this way, rather than the first 100? So basically it is the same with just 1 query that's without LIMIT clause.
 
Reply With Quote
Go Back   Webmaster Forum > Web Development > Coding Forum

Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search / Retrieve document box usability ideas Skags Web Design Lobby 3 11-29-2007 12:30 PM
how to retrieve results from Database on specific field swcobra Coding Forum 3 06-29-2006 03:15 PM
can't retrieve my password on hostdetective phosting Web Hosting Forum 2 06-21-2004 08:13 PM


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 03:35 PM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC