And now mysql4 allows doing this another way, I *assume* this new way is slightly faster then the additional count() although I haven't tested it and wonder if it affects mysql_num_rows()...
See: SQL_CALC_FOUND_ROWS() http://www.mysql.com/doc/en/Miscellaneous_functions.html Regards, Philip Olson On Wed, 18 Dec 2002, liljim wrote: > I would also tend to do a count(*) as well as the main query if I intend to > use pagination (with the help of LIMIT in the main query). > > Suppose, for example, you're limiting to 30 news posts per page, and from > calculations there are only 2 pages that fall into the criteria, then trying > to bring the result set back on page 3 when it doesn't "exist" will result > in a warning. Also, you'll want to know how many pages of the news post > there are... > > Brief Example (assuming $page has already been validated as an integer): > > $num = 30; // per page > $total = 49; // result from count(*) > $total_pages = ceil($total/$num); > if($page > $total_pages) > { > $page = $total_pages; > } > > // Main query. > // "select [whatever] from [table(s)] where [criteria] [whateverelse] limit > " . (($page*$num)-$num) . ",$num; > > James > > "Philip Olson" <[EMAIL PROTECTED]> wrote in message > Pine.BSF.4.10.10212181637090.4483-100000@localhost">news:Pine.BSF.4.10.10212181637090.4483-100000@localhost... > > > > First, there is no reason to do mycounter like this > > when mysql_num_rows() will work perfectly. It's not > > based on any certain column numbers or primary keys. > > Although if you're suggesting this method to simply > > print 0-n in the loop (as users may want to know the > > row number of output not any id number) then that's > > good altough it's not really a counter in this case. > > > > Second, we assume this person wants a count for > > informational purposes, to go along with the > > data in which case mysql_num_rows() is your best > > bet. It means having a count before the data is > > printed/used. And checking the number or rows > > returned before fetching is a form of error handling > > as well as if it equals 0 than there is no data to > > fetch. But if one ONLY wants a number of rows count, > > doing SELECT count(*)... works great as suggested > > below. > > > > > > Regards, > > Philip Olson > > > > > > On Wed, 18 Dec 2002, liljim wrote: > > > > > Hi John, > > > > > > "John Taylor-Johnston" wrote in message: > > > > I use $mycounter, not saying it is pretty. But if you have a whole > bunch > > > of stuff deleted, your last id might be worth a lot more than the actual > > > number of rows. > > > > > > > > $myconnection = mysql_connect($server,$user,$pass); > > > > mysql_select_db($db,$myconnection); > > > > > > > > $news = mysql_query('select * from '.$table.' where '.$where.' order > by id > > > asc'); > > > > $mycounter = 0; > > > > while ($mydata = mysql_fetch_object($news)) > > > > { > > > > $mycounter++; > > > > } > > > > > > Have you ever considered just doing a count()? > > > > > > $count = @mysql_query("select count(*) from [table(s)] where [criteria > > > [group by something]]"); > > > $total = mysql_result($count, 0); > > > > > > That will return the number of rows in your table(s). It's also much > quicker > > > and less resource intensive, particularly with large datasets. :) > > > > > > James > > > > > > > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
