Seperate queries?

cheeyim

Hie,

I have this problem where I cant seperate my queries into two and print them out the same time. I need to seperate the query because I dont get the correct data if I put everything into one.

For example, here is my query:
select sum(on_hand), count(distinct(items.cust_id))
from inventory, items, wine
where wine.wine_id = items.wine.id AND
wine.wine_id = inventory.wine_id AND
wine.wine_id = 191;

Result given:
2400 10

Correct result:
240 10

This is because it seems to go through this query multiple times and thus, the sum(on_hand) is added so many times. In order to get just 240 in thsi case, I thought of seperating my query? Can I use for loop for that? What if I dont know my wine.wine_id and have to depend on some other data ? If i seperate the query, wont it give me dif rows? Thanks

Lars_Berg

select sum(on_hand), (select count(*)
 from items 
 where wine_id = inventory.wine_id)
from inventory
where inventory.wine_id = 191

cheeyim

Hie Lars Berg!
Thanks alot for ur suggestion..I didnt know u can query it that way!
But i am still stuck trying to modify abit. Apparently, I wont be able to know my wine_id because user are called to input the wine_name which from there I could know the wine_id. But the problem is, user may also not input anything which means to generate all wines. My pc was hang when generating that query as the database is very large. Any ideas? Here is the code so far:

"SELECT wine_name, variety, year, winery_name, region_name, MIN(cost), on_hand, sum(on_hand),
(select count(*) from items where wine_id = inventory.wine_id)
FROM wine, wine_variety, winery, region, grape_variety,inventory
WHERE wine_name LIKE '{$wineName}%' AND
winery_name LIKE '{$wineryName}%' AND
wine.wine_id = wine_variety.wine_id AND
winery.winery_id = wine.winery_id AND
wine_variety.variety_id = grape_variety.variety_id AND
region.region_id = winery.region_id AND
inventory.wine_id = wine.wine_id
GROUP BY wine.wine_id, variety ORDER BY wine_name,year,variety";

DoctorWho

you can use a form validation, java script, to make sure they input something

or at the end of your SQL put LIMIT 200 or something, that way they can't download your whole database

Roger_Ramjet

Or might I suggest a Select list from which users pick a wine_name and the form returns the wine_id. Saves validating for an entry or searching for misspelled names and gives you the input you want. Just make sure they have selected something. You could extend this to listing varieties, regions, etc. for broader searches.

cheeyim

hey thanks for all the suggestion but unfortunately that is the question for my college assignment.. I am suppose to allow user to key in the wine name or nothing at all.. AM still trying to think how to link it..ehhee.. Thanks!

cheeyim

hmm.. just to add something.. actually i have seen ppl do it and it worked.. as in, when user do not enter anything, it's assumed that the user wants all the wines to be printed out. Logically, it should work cause if i take out the whole inner select statement, it displayed all the wines.