SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need your help in deleting 'inactive' polls

    I have a site that provides free polls for webmasters. All polls can have multiple questions. In my terms of service it says: "Accounts that are inactive for a period of 60 days are subject to removal."

    I need your help creating a query that will find all pollid that are inactive (=no votes) for a period of 60 days or more.

    I have a table "Votes" that stores the votes of all the polls. This is an example of how the table looks like:

    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | key | pollid | question | vote1 | vote2 | vote3 | vote4 | vote5 | date |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 1 | 1001 | 1 | 10 | 20 | 0 | 40 | 50 | 20030801 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 2 | 1001 | 2 | 50 | 10 | 30 | 0 | 50 | 20020101 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 3 | 1002 | 1 | 10 | 20 | 10 | 10 | 50 | 20020101 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 4 | 1002 | 2 | 40 | 0 | 30 | 40 | 50 | 20020101 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 5 | 1002 | 3 | 10 | 60 | 20 | 20 | 50 | 20020101 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+
    | 6 | 1003 | 1 | 20 | 20 | 30 | 40 | 50 | 20030801 |
    +-----+-------+------------+--------+--------+--------+--------+-------+--------------+

    key = INT(10) PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    pollid = VARCHAR(4) NOT_NULL
    rank = VARCHAR(2) NOT_NULL
    vote1 = INT(5) NOT_NULL
    vote2 = INT(5) NOT_NULL
    vote3 = INT(5) NOT_NULL
    vote4 = INT(5) NOT_NULL
    vote5 = INT(5) NOT_NULL
    date = TIMESTAMP(8)

    In the example above we see the following:

    - there are 3 polls in the database with ids 1001,1002 and 1003.

    - poll 1001 is a poll with 2 questions, poll 1002 is a poll with 3 questions,
    and poll 1003 is a poll with 1 questions.

    - (the field date is the date of the last vote for the question)
    the last vote for question 1 of poll 1001 was a couple of days ago (20030801=Aug 1, 2003);
    the last vote for question 2 of poll 1001 was in 2002!

    What I want to do is create a query that will select all ids for which ALL question hasn't received a vote for 60 days.

    Poll 1001 does not fit the condition above because although question 2 hasn't received a vote for over a year, question 1 has received a vote a few days ago!

    Poll 1002 does fit the condition above. All 3 question hasn't received votes for over 60 days!

    Poll 1003 does not fit the condition above because question 1 has received a vote a couple of days ago!

    Who can help me?
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this polling data already in place or do you have freedom to change the db structure a bit?
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sketch
    Is this polling data already in place or do you have freedom to change the db structure a bit?
    I have freedom to change the structure a bit. Why? Whta's wrong?

    I want to replace the old system with this new system.
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  4. #4
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like you have a separate table for polls. I'd assume that the table above uses pollid to reference the poll table.

    Why not make life really easy and add a lastupdated field to the poll table and whenever a vote is made, update that table. make it a timestamp(12) or a UNIX_TIMESTAMP(). Then it's just a matter of querying data against that table.
    Code:
    SELECT * FROM polls WHERE timestamp > NOW()-3600
    Should pull data that is less than an hour old. Enhance as necessary.
    Aaron Brazell
    Technosailor



  5. #5
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sketch
    It looks like you have a separate table for polls. I'd assume that the table above uses pollid to reference the poll table.

    Why not make life really easy and add a lastupdated field to the poll table and whenever a vote is made, update that table. make it a timestamp(12) or a UNIX_TIMESTAMP(). Then it's just a matter of querying data against that table.
    Code:
    SELECT * FROM polls WHERE timestamp > NOW()-3600
    Should pull data that is less than an hour old. Enhance as necessary.

    I do have a table named "Polls" and I do use the pollid from "Votes" as a reference (boy, you are good!).

    I did think about your solution for a while, but I decided to only update the "Votes" table since I'm already updating that table with the votes.

    But is there a query that will do want I want?
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  6. #6
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:
    Code:
    SELECT DISTINCT pollid FROM Votes WHERE MAX(date) > Now()-518400000
    Untested.
    Last edited by Sketch; Aug 8, 2003 at 12:32.
    Aaron Brazell
    Technosailor



  7. #7
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sketch
    Try:
    Code:
    SELECT DISTINCT pollid FROM Votes WHERE MAX(date) > Now()-518400000
    Untested.
    I get the following error: "Invalid use of group function"
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  8. #8
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think I've got it.

    Code:
    SELECT pollid FROM Votes GROUP BY pollid HAVING Max( date )  < Now()-518400000
    Can someone confirm this? Don't wanna be deleting active polls of my members!!
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •