finding duplicates...

pointsplat

Hey guys my question is this.

I have a table (I attached an example image of it populated). What I need is an SQL command or a php page (though I prefer to do it all in SQL) that will return the rows that have duplicate ip and prof_id fields.

The problem is I run a website and because pretty much all methods of making sure people dont vote more then once are worthless (I cant ban ips without good reason because alot of people use routers on campus). I allow duplicates to be entered and just monitor the database by myself looking for duplicate votes. I need a way to make this easier because of the sheer number of votes I get, specfically I want to return all the duplicate submissions I get so that I can easily deal with them in phpmyadmin (hence I prefer sql).

If you need any clarifications please let me know.

rachel2004

You could add a column to capture a timestamp value. With that in place, your error checking that someone isn't pumping your table full of bogus votes is to check the source IP address and whether it has a date/timestamp entry in the past 60 seconds, 120 seconds, or whatever you feel is safe to discount votes coming from a source IP address within a certain window of time.

pointsplat

Yea I am going to start implementing that from now on actually but it doesnt help clean up the thousands of votes ive received thus far, some which may have been a day or more apart.

I dunno I guess im stuck with cleaning up suspicious looking ones myself and deciding what I want to do from there.

The problem is I dont want to ban IPs because of routers on campus but then again I dont want to let someone vote for the same teacher even if its a day or more apart because it defeats the purpose of the rating system.

cahva

I quess theres no way to clean those earlier votes automatically but I have ateast couple of ways to make your voting system a little bit better.

-Cookies. Not very good if cookies are disabled and ofcourse if someone wants to vote 100 times a day, he could allways clean up the cookie from browser settings after voting.

Well.. Maybe the best way is that you make login for your site. And just to make fakevoters life a little bit harder, add cookie too 🙂

Both ways are not solutions, but a lot better than to check ip:s(that is not the way.. I did it once and boy I got flamed 🙂 )

rachel2004

<?php 
$query = "SELECT COUNT(*) AS repetitions,
                             ip
          FROM table
          GROUP BY ip
          HAVING repetitions > 1";
?>

Roger_Ramjet

The simplest way to solve your problem is to prevent duplicates being stored in the first place. Creating an index on the TWO fields concerned, make it unique and no duplicate votes will be saved anymore. Follow rachel's advice for finding duplicates already in your db.