[solved]4 queries to 1 or 2?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

[solved]4 queries to 1 or 2?

Post 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
Last edited by dreamline on Sun Aug 07, 2005 5:14 am, edited 1 time in total.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post 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
Post Reply