large hit search queries

[Ngreth Thergn]

I am curently working on the search feature for the site.

We of course know that it is posible to get a large amount of hits. I want to limit the hits to 25, but wouild like to be able to show x of Y (where Y is the total hits) but the query returns only the limit.

Is there something to do to get it to show me the total hits AND give the limited hits I want, without running a second querry to get just hits?

Bellow is an example of the query

query(): SELECT * FROM items WHERE item_name LIKE '%acr%' ORDER BY item_name LIMIT 0, 25;
query() returned 25 rows

[Krazick]

Ngreth,

My first immediate thought was:

SELECT *,COUNT(*) FROM items WHERE... LIMIT 0,25;

But tryed that and it didn't work. it wants a GROUP BY clause in SQL.

The obvious answer is of course to do a second query:

SELECT COUNT(*) FROM items WHERE ...;

That would return a single row with the count.

I will look further for another way to do it in one Query.

[Zallarenya]

I think what you're wanting requires two steps. I'm assuming you're building this in PHP, though, and PHP can keep track of the number of rows returned while displaying your subset.

Something you might want to look at is using someone else's code I use an updated class from "sephiroth" to display pages of results. You can download the code and view a (weird, ugly) example on his website:

http://www.sephiroth.it/file_detail.php?id=89

It works well for me - I'm not sure how well it holds up to a huge load. I hate reinventing the wheel

[Ngreth Thergn]

oh my...
his code is greek!

[Zallarenya]

it's.... a style It took me a while to get through it, and there are still a few things I scratch my head about. The "documentation", if you can call it that, is in the comment at the top of the script. It's sometimes a little tricky since English isn't his native language.

I'm willing to offer what help/guidance I can. You know how to reach me if you need it.

[Ngreth Thergn]

I also recieved this help from the super secret help area...

If you're running MySQL 4.0 or greater, you can use (from the docs):

FOUND_ROWS()
Returns the number of rows that the last SELECT SQL_CALC_FOUND_ROWS ... command would have returned, if it had not been restricted with LIMIT.

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate all rows in the result set. However, this is faster than if you would not use LIMIT, as the result set need not be sent to the client. SQL_CALC_FOUND_ROWS is available starting at MySQL version 4.0.0.

Now to see if I have mysql 4.0 on the server

[Zallarenya]

Nope, you're still on 3.23.x

4.0 is in "gamma" release, by the way. 3.23.55 is the current production version.

[Lorthien]

It would seem that you are still going to have to do multiple hits to the DB, since you do not want to have a buffer filled with the entire search results (i.e. over 100 rows of data). My suggestion is, and I assume your using a persistent connection, do the extra Query to search for the # of rows, store that as a variable and then run your SELECT query based on # of results per page (i.e.) then when you hit NEXT do the search again, but use the LIMIT in conjuction with the correct variables, $page, $total_hits.

Im sure this is not the most eloquent way but you are not whacking the server for a huge hit all at once, which is what I think your trying to avoid.

[Ngreth Thergn]

yeah, I may just do a...

if (!isset($sz)) {
**querry for size**
}

and then pass the sz as part of the querry string.

Thanks

[Cyrnan]

All I have to say is -- "Huh?"

"I'm not really a druid. I just play one on EQ."


Thanks for all the hard work in things I can never hope to understand, Ngreth, Zallarenya, et al.