Page 1 of 1

[solved]4 queries to 1 or 2?

Posted: Thu Oct 28, 2004 3:39 am
by dreamline
Hi all,
Here a little problem i encounter. I need 3-4 queries in a row to get my results page and ofcourse I want to optimize it
as much as possible. It would be perfect to get my same results in only 2 queries.

Through a dynamic query i print the following results on screen:
1. A listbox with all countries encountered by the query
2. The total number of results found
3. Incase a country is selected from the listbox the nr. of results and the total number of all results
4. The pages since i only have about 25 results on a page
<code>
Basically i use 3 queries, however if a country is selected from the listbox then it turns into 4 queries.

First : Is a select distinct to get the countries in that query (for my listbox)
Select distinct country from <tables> where .....

Second : Is a query on all countries combined to print the total results found (also used to determine the total number of pages)
Select * from <tables> where ..... <same query as First, but without distinct)

Third : Is a query to get the results from the selected country (if you selected one from the listbox)
Select count(*) from <tables> where .... and country = .......

Fourth : Is a query with a LIMIT so that only 25 results are shown per page
Select * from <tables> where .... LIMIT 0,25
</code>

My question is: Would it be possible to shorten the number of queries used to either 1 or 2 queries in which i get the same result?

I can always kill number 3 and not print the total results of a specific country so that i only have 3 queries left.

Hope someone has an idea or encountered the same problem when working on similar result pages.

Thanks for any help... :D

Posted: Thu Oct 28, 2004 12:36 pm
by lostboy
1. can you use an array of countries instead of the db call ?

2/3. should be one query...if the country is set, the use that else count the whole thing

Posted: Thu Oct 28, 2004 7:38 pm
by dreamline
Thanks for your reply.. :)
I could use an array for the countries, but decided to put it into a database incase of changes, but it's definately something to think about.. :D

As for query 2-3 that tip is very usefull and i will try to implement it.. :D

Thanks for your view on this.. :D