MySQL ODBC Connector - Can I do a boolean search and get the row count in 1 SQL query?

Author

Can I do a boolean search and get the row count in 1 SQL query?

Grant Giddens

2006-01-02, 9:23 am

--0-1267530439-1136208941=:80986
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,

I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:

1. (To get the actual rows via the search)

SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10

2. (To get the total number of results)

SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC

The queries might be a bit off as I don't have the exact code in
front of me right now.

Is there a way I can combine this into 1 query?

Thanks,
Grant

---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
--0-1267530439-1136208941=:80986--

Octavian Rasnita

2006-01-02, 1:23 pm

Hi,

You can do:

select sql_calc_found_rows [and here follow the rest of the select query];

And then you can get the number of all found rows, not only those got by
"limit 10" as follows:
select found_rows();

Teddy

From: "Grant Giddens" <lggiddens@yahoo.com>

>
> Hi,
>
> I have a web app where I am doing a boolean search. I only want
> to return 10 results per page, but I'd also like to know how many total
> rows match the search query. I'm currently performing this with 2
> query statements:
>
> 1. (To get the actual rows via the search)
>
> SELECT $product_column[title],
> MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
> AS score FROM $product_table
> WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
> ORDER BY score DESC
> LIMIT $lower_limit,10
>
> 2. (To get the total number of results)
>
> SELECT COUNT(*) as num_results
> MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
> AS score FROM $product_table
> WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
> ORDER BY score DESC
>
> The queries might be a bit off as I don't have the exact code in
> front of me right now.
>
> Is there a way I can combine this into 1 query?
>
> Thanks,
> Grant
>
>
>
> ---------------------------------
> Yahoo! Photos
> Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links