Re: INSERT, UPDATE and Groundhogs
At 10:05 AM -0700 10/26/01, Jonathan M. Morgan wrote: I am working my way through Paul DuBois' excellent book MySQL and Perl for the Web. One of the examples shows how to conduct a poll- vote for your favorite groundhog: http://www.kitebird.com/cgi-perl/groundhog.pl Paul then suggests modifying the poll ...to log EACH vote and when it occurred so that you can perform time-based analysis of poll activity. How does one tally the vote (UPDATE) and insert a new record for each vote? The original MySQL table is as follows: CREATE TABLE ( name CHAR(10) NOT NULL, tally INT UNSIGNED NOT NULL DEFAULT 0 ) The vote tally is updated like this: $dbh-do (UPDATE groundhog SET tally = tally + 1 WHERE name = ?, undef, $name); I updated the table to include an auto incremented vote_id(PRIMARY KEY) as well as a timestamp. But how do you log each vote and tally the vote? Thanks! JMM The original table maintains the current count using a single record per name, so that doesn't work if you want to maintain a log of each vote. To log each vote, you could use a table that has a TIMESTAMP (or DATETIME) column and a string column to hold the name. Then you don't need a column to count the number of votes, because that is implicit as the number of rows per name. CREATE TABLE groundhog_log ( t TIMESTAMP NOT NULL, name CHAR(10) NOT NULL INDEX (name) ) Each time you get a vote for a name $name, you add a record to the table like this: $dbh-do (INSERT INTO groundhog_log SET name = ?, undef, $name); That'll set the t column to the current date and time automatically. To summarize votes, issue this query: SELECT COUNT(*), name FROM groundhog_log GROUP BY name; Another approach would be to use both the original and the log table. Update the tally in the original table and add a new log record each time you get a vote. That way, when you want to produce the vote tallies, you need only query the original table, not summarize the log table. That might be preferable if the summary query got slow as the log table becomes large. Glad you like the book, by the way. :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT, UPDATE and Groundhogs
I am working my way through Paul DuBois' excellent book MySQL and Perl for the Web. One of the examples shows how to conduct a poll- vote for your favorite groundhog: http://www.kitebird.com/cgi-perl/groundhog.pl Paul then suggests modifying the poll ...to log EACH vote and when it occurred so that you can perform time-based analysis of poll activity. How does one tally the vote (UPDATE) and insert a new record for each vote? The original MySQL table is as follows: CREATE TABLE ( name CHAR(10) NOT NULL, tally INT UNSIGNED NOT NULL DEFAULT 0 ) The vote tally is updated like this: $dbh-do (UPDATE groundhog SET tally = tally + 1 WHERE name = ?, undef, $name); I updated the table to include an auto incremented vote_id(PRIMARY KEY) as well as a timestamp. But how do you log each vote and tally the vote? Thanks! JMM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT, UPDATE and Groundhogs
Basically for each vote you insert a new record, and then to determine the
tally, you have to do a count(*) on the number of records for each vote.
so have ID | NAME | TIMESTAMP columns.
You log each vote by doing an insert (insert into whatever (name, timestamp)
values ('answer', 'timestamp')
and then to get the tally for that vote do :
select name, count (*) from table group by name.
if you only want the tally for one vote than just add a WHERE clause.
Chrsi
I am working my way through Paul DuBois' excellent book MySQL and Perl for
the Web. One of the examples shows how to conduct a poll- vote for your
favorite groundhog: http://www.kitebird.com/cgi-perl/groundhog.pl Paul then
suggests modifying the poll ...to log EACH vote and when it occurred so
that you can perform time-based analysis of poll activity. How does one
tally the vote (UPDATE) and insert a new record for each vote? The original
MySQL table is as follows: CREATE TABLE ( name CHAR(10) NOT NULL, tally INT
UNSIGNED NOT NULL DEFAULT 0 ) The vote tally is updated like this: $dbh-do
(UPDATE groundhog SET tally = tally + 1 WHERE name = ?, undef, $name); I
updated the table to include an auto incremented vote_id(PRIMARY KEY) as
well as a timestamp. But how do you log each vote and tally the vote?
Thanks! JMM
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
