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!

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

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.

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

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!

Here is what you should do:

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?

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!!!!!

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.

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

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

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

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