Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: one long select statement that can count (creating pages)

    Hi all,

    I'm stamped on this one. Therefore my first question here:

    I'm trying to creates a pages feel in the application (you are now on page 4 of 6), showing for example 30 records per screen. BUT, I am having difficulty counting the number of pages (or records)! The select statement is quite long:

    "Select COUNT(x)...join 3 or 4 table... WHERE ... few rules ... ORDER BY ... LIMIT y,z"

    COUNT(x), COUNT DISTINT, COUNT(*), GROUP BY combinations didn't work for me at all. And I tried LOTS of combinations! Either I get erros, or 0, or another number (like all the records).

    I've seen another example where that programmer did two SQL calls, one to find count and another to get records. There must be another way!

    Does anyone have an example of this kind of statement in MySQL? Or can tell me what I'm doing wrong?

    I'm using MySQL 3.23 on windows 2000, but can upgrade to v4 no problem if it will help. (BTW coding in ASP)

    Thank you!
    Last edited by zkenet; 01-11-04 at 13:14.

  2. #2
    Join Date
    May 2002
    Location
    Vancouver Canada
    Posts
    26
    The only way I know of is to use two queries, the first without any LIMIT on it.

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Originally posted by Dylan Leblanc
    The only way I know of is to use two queries, the first without any LIMIT on it.
    Does this mean every single discussion board out there does two queries each time they list posts for a forum? One for the count and another for the limited recordset of posts?

    I did see some websites that do NOT show total number of pages, each time you click on the last page they give you a few more pages. I find that interface annoying.

    Anyone with a single sql query workaround? My query is not short, I would much prefer not to hit the db twice. Is this a MySQL limitation? Or does this limitation exist in other DBs too?

    Thx

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: one long select statement that can count (creating pages)

    It is hard to come up with a specific answer to your question without knowing exactly what the query is. There are a couple of solutions. You can retrieve the whole result set and deal with the problem in ASP (not very efficient). You can use a cartesian product of your result set. This way, you group on the desired columns from the left half of the query and roll up the records on the right half with a COUNT function.

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Re: one long select statement that can count (creating pages)

    Originally posted by aus
    It is hard to come up with a specific answer to your question without knowing exactly what the query is.
    Here is a trimmed version of my query, but all the components are there. So this will get the first 20 posts and the user info per post. What I also need to get from this query is the total number of posts (as if there is no limit).

    SELECT tbl_posts.nPostID, tbl_posts.sPostSubject, tbl_members.sUserName FROM tbl_posts LEFT JOIN tbl_members ON tbl_members.nUserID = tbl_posts.nUserID WHERE tbl_posts.nForumID=4 AND tbl_posts.nPostIsBanned<>1 ORDER BY tbl_posts.nThreadImportance, tbl_posts.dPostDatePosted LIMIT 0, 20

    Originally posted by aus
    You can use a cartesian product of your result set. This way, you group on the desired columns from the left half of the query and roll up the records on the right half with a COUNT function.
    Wow, well, am, it sounds great! Unfortunatly I don't know how do go about it. Can you show me what I would need to add to my query? Thx!

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: one long select statement that can count (creating pages)

    Here is what you should do:

    Code:
    SELECT tbl_posts.nPostID, tbl_posts.sPostSubject, tbl_members.sUserName, COUNT(rolluptable.nPostID) AS TotalPosts
    FROM tbl_posts, tbl_members, tbl_posts rolluptable 
    WHERE tbl_members.nUserID = tbl_posts.nUserID AND tbl_posts.nForumID=4 AND tbl_posts.nPostIsBanned<>1 
    GROUP BY tbl_posts.nPostID, tbl_posts.sPostSubject, tbl_members.sUserName 
    ORDER BY tbl_posts.nThreadImportance, tbl_posts.dPostDatePosted LIMIT 0, 20;
    This should give you the right order with a total number of posts in the last field.

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Re: one long select statement that can count (creating pages)

    Originally posted by aus
    This should give you the right order with a total number of posts in the last field.
    I thought it worked, but it turns out it always gives me the total number of records in the table, as opposed to the total number of records from the query. Mind you, only when I add OPTION=16387 to the connection string does it give me the recordset back. Without 16387 I get no records from the query at all. (I'm not even sure what OPTION=16387 is for exactly, but read somewhere I do need it). Any ideas?

  8. #8
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: one long select statement that can count (creating pages)

    You need to modify the query in your WHERE clause to limit rolluptable to the same records as the query.

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Re: one long select statement that can count (creating pages)

    Originally posted by aus
    You need to modify the query in your WHERE clause to limit rolluptable to the same records as the query.
    It seems to work! Yay! I've added this, is it what you meant?:
    WHERE tbl_members.nUserID = tbl_posts.nUserID AND tbl_posts.nForumID=4 AND tbl_posts.nPostIsBanned<>1 AND rolluptable.nForumID=4 AND rolluptable.nPostIsBanned<>1
    So I'm duplicating all the WHERE arguments. Now in my full version I have 6 arguments, so I'll have 12 in total right? Does performence suffer? I'm sure its better than 2 queries...

    2 other questions now (to better understand):
    1) Which columns do I want to put in the Group By ? It seemed to work when I only put in nPostID, as well as in your exmaple (all three columns). In my statement I query about 10 columns, do I only need the unique one (nPostID?)?

    2) I pulled this out from some website:
    "16384 Change LONGLONG columns to INT columns, as some applications can't handle LONGLONG or BIGINT. "
    Why does OPTION=16387 affect COUNT() !? Any disadvantages to it? Query doesnt work without it.

    THX!!!!!

  10. #10
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: one long select statement that can count (creating pages)

    You are right that you should double up the WHERE clause. The where arguments actually increase performance when dealing with a cartesian product of big tables because it filters the input rows (as opposed to a HAVING clause- which only limits the records returned to the client).

    1) The group by does only need the unique identifier specified. MySQL has extended the use of GROUP BY to allow fields not specified in the GROUP BY clause to be returned as part of the result set. This is not standard. It makes sense, but don't expect it of other databases;

    2) COUNT() probably returns a BIGINT, so MyODBC has to change that to INT.

  11. #11
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Wink Re: one long select statement that can count (creating pages)

    You're my hero!
    Seriously though, I really appreciate all your help.
    Cheers!
    Ziv.

  12. #12
    Join Date
    Jan 2004
    Posts
    24
    Just to give you an another option on this one you can do something like this:


    SQL_CALC_FOUND_ROWS (version 4.0.0 and up) tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See section 12.6.4 Miscellaneous Functions. Please note that in versions prior to 4.1.0 this does not work with LIMIT 0, which is optimized to return instantly (resulting in a row count of 0).

    You just add it to your select. So you will always know how many total rows would be returned in your query if you didn't use the limit clause. Only works with 4.x and up.

    Donny
    http://www.phpscripts.com/

  13. #13
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    FOUND_ROWS()

    Thanks Donny, I actually found this on mysql.org manual maybe just an hour before you posted it here !

    I do have a question though re ASP implementation of FOUND_ROWS. Can I do it with one SQL statement, or does it have to be 1 SQL statement for
    sSQL = "SELECT SQL_CALC_FOUND_ROWS *.... LIMIT 0,10"
    and a second:
    sSQL = "SELECT FOUND_ROWS() as nTotalNumRecords"
    and therefor 2 recordsets. I tried plugging FOUND_ROWS into the first one and got stange results, but just double checking.

    BTW: For anyone else, more info on : http://www.mysql.com/doc/en/Informat...s.html#IDX1381

  14. #14
    Join Date
    Jan 2004
    Posts
    24
    SELECT SQL_CALC_FOUND_ROWS *.... LIMIT 0,10
    and SELECT FOUND_ROWS() as nTotalNumRecords
    Have to be two different select statements unfortunately, but select FOUND_ROWS() will always come back as taking 0.00 seconds.

    Donny
    http://www.phpscripts.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •