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
