Re: [PHP] count() total records for pagination with limit
Chris wrote: > PJ wrote: >> I seem to recall that it is possible to count all instances of a query >> that is limited by $RecordsPerPage without repeating the same query. I >> believe that COUNT() had to called immediately after the SELECT word but >> I neglected to bookmark the source. Dummy! > > You're probably thinking of > > SQL_CALC_FOUND_ROWS > > (http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html) > > It's not always faster though > (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/). > > >> I don't like the idea of count(*) over count() or something like that as >> it seems rather slow fram what I read. >> right now I do this: >> $sql = "SELECT * FROM book >> WHERE id IN (SELECT bookID >> FROM book_author WHERE authID IN (SELECT author.id >> FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) "; >> $Count1 = mysql_num_rows(mysql_query($sql, $db)); > > The problem with this is if your '*' includes 50 fields (from all of > the tables in the joins etc) then that is still processed in mysql > taking up memory especially. > > Doing a count() just has 1 field - the count. > > $sql = "select count(1) as count from book > "; > $result = mysql_query($sql, $db); > $row = mysql_fetch_assoc($result); > $count = $row['count']; > >> $Count gives me the actual rows for display - $Count1 gives me the total >> rows available. >> >> Can this be streamlined any? > > Not really. > OK. Your suggestion does help, though. :-) -- unheralded genius: "A clean desk is the sign of a dull mind. " - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] count() total records for pagination with limit
PJ wrote: I seem to recall that it is possible to count all instances of a query that is limited by $RecordsPerPage without repeating the same query. I believe that COUNT() had to called immediately after the SELECT word but I neglected to bookmark the source. Dummy! You're probably thinking of SQL_CALC_FOUND_ROWS (http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html) It's not always faster though (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/). I don't like the idea of count(*) over count() or something like that as it seems rather slow fram what I read. right now I do this: $sql = "SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) "; $Count1 = mysql_num_rows(mysql_query($sql, $db)); The problem with this is if your '*' includes 50 fields (from all of the tables in the joins etc) then that is still processed in mysql taking up memory especially. Doing a count() just has 1 field - the count. $sql = "select count(1) as count from book "; $result = mysql_query($sql, $db); $row = mysql_fetch_assoc($result); $count = $row['count']; $Count gives me the actual rows for display - $Count1 gives me the total rows available. Can this be streamlined any? Not really. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] count() total records for pagination with limit
I seem to recall that it is possible to count all instances of a query
that is limited by $RecordsPerPage without repeating the same query. I
believe that COUNT() had to called immediately after the SELECT word but
I neglected to bookmark the source. Dummy!
I don't like the idea of count(*) over count() or something like that as
it seems rather slow fram what I read.
right now I do this:
$sql = "SELECT * FROM book
WHERE id IN (SELECT bookID
FROM book_author WHERE authID IN (SELECT author.id
FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) ";
$Count1 = mysql_num_rows(mysql_query($sql, $db));
$books = array();
$SQL = "SELECT * FROM book
WHERE id IN (SELECT bookID
FROM book_author WHERE authID IN (SELECT author.id
FROM author WHERE LEFT(last_name, 1 ) = '$Auth'))
ORDER BY $sort $dir
LIMIT $offset, $records_per_page ";
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row;
}
}
$Count = mysql_num_rows($results);
$Count gives me the actual rows for display - $Count1 gives me the total
rows available.
Can this be streamlined any?
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Count total
On 19-Aug-2001 Martin Kampherbeek wrote: > Hi, > > Who can help me with the following problem? > I have the tables score and totalscore. > > Score: > id > userid > name > score > > Totalscore: > userid > name > totalscore > > In the table score one user can have mutiple scores. But in totalscore the > userid is unique. Now I want to count all the score's of a user en place > this at the same user in totalscore. > > Example of score: > 11Martin10 > 22John5 > 33Richard 12 > 41Martin 3 > 53Richard8 > 61Martin 7 > 72John15 > > So I would like to know the total score of each user. > But I don't know how to do this. Who can help me out? > REPLACE INTO Totalscore SELECT userid,name,sum(score) from Score GROUP BY userid; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Count total
On Sun, 19 Aug 2001, Martin Kampherbeek wrote: > Hi, > > Who can help me with the following problem? > I have the tables score and totalscore. > > Score: > id > userid > name > score > > Totalscore: > userid > name > totalscore > > In the table score one user can have mutiple scores. But in totalscore > the userid is unique. Now I want to count all the score's of a user en > place this at the same user in totalscore. > > Example of score: > 11Martin10 > 22John5 > 33Richard 12 > 41Martin 3 > 53Richard8 > 61Martin 7 > 72John15 > > So I would like to know the total score of each user. > But I don't know how to do this. Who can help me out? insert into totalscores (userid,name,totalscore) select userid, name, sum(score) as totalscore from scores group by userid; -Rasmus -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Count total
Assuming MySQL... /* Chris Lambert, CTO - [EMAIL PROTECTED] WhiteCrown Networks - More Than White Hats Web Application Security - www.whitecrown.net */ - Original Message - From: Martin Kampherbeek <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 19, 2001 5:34 AM Subject: [PHP] Count total Hi, Who can help me with the following problem? I have the tables score and totalscore. Score: id userid name score Totalscore: userid name totalscore In the table score one user can have mutiple scores. But in totalscore the userid is unique. Now I want to count all the score's of a user en place this at the same user in totalscore. Example of score: 11Martin10 22John5 33Richard 12 41Martin 3 53Richard8 61Martin 7 72John15 So I would like to know the total score of each user. But I don't know how to do this. Who can help me out? Cheers, Martin. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Count total
Hi, Who can help me with the following problem? I have the tables score and totalscore. Score: id userid name score Totalscore: userid name totalscore In the table score one user can have mutiple scores. But in totalscore the userid is unique. Now I want to count all the score's of a user en place this at the same user in totalscore. Example of score: 11Martin10 22John5 33Richard 12 41Martin 3 53Richard8 61Martin 7 72John15 So I would like to know the total score of each user. But I don't know how to do this. Who can help me out? Cheers, Martin.
