[thelist] SQL query help

[Erik Mattheis]

At 2:04 AM -0500 1/2/02, Warden, Matt wrote:

>Well, wait a minute here. Is there some reason you need poll answers to be
>individual records like that?

Yeah - I only allow one vote per IP address per hour ... I set a 
cookie when the visitor votes and if the cookie isn't found it looks 
to see if there was a vote from that IP within an hour. 
http://aprilwinchell.com/ - poll is on home page after you select 
Flash or html.


>  if not, i see your problem going away if you
>change poll_answers to:
>
>poll_answers
>poll_answer_id, option_id, hitcount
>
>so:
>
>poll_result
>poll_result_id, option_id, hitcount
>
>Then, when you were recording a vote, you'd just do this:

>UPDATE poll_result SET hitcount=(hitcount+1) WHERE option_id=#form.foo#

Yes! indeed - this is what I was looking for ... I didn't know how to 
increment a field with one trip to the DB ... I will use this. That 
way I don't have to use nested queries - there's already 4-7 calls to 
the DB for the home page depending on Flash/HTML version (but thanks 
for the help too, Rudy.ca).

>Note that I axed the poll_id field. It's redundant because an option is
>already unique to a poll (see poll_options).

Because of the one vote per hour thing, I wanted the poll_id in the 
poll_answers table so the SQL would be simpler and quicker for 
looking for the IP.
-- 

__________________________________________
- Erik Mattheis

(612) 377 2272
http://goZz.com/

__________________________________________