How to restrict votes per day by IP in Php+Mysql voting?

Question

Hello I have this voting script attached it counts votes by IP address. Please how can I create a kind of time session on the IP addresses. "say 5 votes a day per IP. Voter has to wait another 24 hours before voting again. I know there are kind of questions like this. I have tried few, but I just can't get it to work. Thanks.

Update script;

<?php
include("config.php");

$ip=$_SERVER['REMOTE_ADDR']; 


$add_time = new DateTime(null, new DateTimeZone('Europe/London'));
$time=$add_time->format('Y-m-d H:i:s');
$timeMinus = $add_time = - 60*1*1*1;



if($_POST['id'])
{
$id=$_POST['id'];
$id = mysqli_real_escape_String($bd, $id);



$ip_sql=mysqli_query($bd, "SELECT ip_add FROM voting_ip WHERE mes_id_fk='$id' AND ip_add='$ip' AND add_time>'$timeMinus'");
$count=mysqli_num_rows($ip_sql);

if($count<= 2)
{
$query = mysqli_query($bd, "UPDATE Messages SET down=down+1  WHERE mes_id=$id");
( $query);

$sql_in = mysqli_query($bd, "INSERT INTO voting_ip (mes_id_fk,ip_add) values ('$id','$ip')");
( $sql_in);



}
else
{
echo "<script>alert('You have already voted, wait for 24 hours and vote again.');</script>";
} 

$result=mysqli_query($bd, "select down from Messages where mes_id='$id'");
$row=mysqli_fetch_array($result);
$down_value=$row['down'];
echo $down_value;

}
?>

you know IP does not equal person? one person many IP's one IP can be hundreds of people — , Mar 28 '16 at 20:30
thanks Dagon, Yes, I understand 1 person can control multiple IP addresses, but cookies can also be cleared easily. I just need the time setting code — Vin Nwaikwu, Mar 28 '16 at 20:43
Also, a large business or school will typically share the same IP address. So multiple people can share the same IP. — kojow7, Mar 28 '16 at 21:45

Sergio Fernandez · Answer 1 · 2016-03-29T21:22:49.807

0

the easiest way to limit the number of votes per time frame it to store a time-stamp when the person votes in your voting_ip table... then the next time they vote, count all records in your voting table with the persons Id whose time-stamp is greater than the (current time - 24 hours). if the count is >=5 votes.. display the message saying you already votes. its should just be a simple modification to your existing code.

your select should be modified to something like this:

SELECT ip_add FROM voting_ip 
WHERE mes_id_fk='$id' AND ip_add='$ip' AND timetamp>'$nowMinus24Hours'

and your insert should be something like

INSERT INTO voting_ip (mes_id_fk,ip_add,timestamp) values ('$id','$ip','$now')

$now can be set in php using something like:

$timestamp = new DateTime();
$now=$timestamp->format('Y-m-d H:i:s');

and then $nowMinus24Hours is just a variable = $now minus the 24 hours.

NOTE: you can do a SELECT Count(ip_add).... or Select count(*) (Select ..) to get the number of records as the result of your query.

edited Mar 29 '16 at 21:22

answered Mar 28 '16 at 20:42

Sergio Fernandez

383
2
7

The first query is unnecessary and counter-productive – Strawberry Mar 28 '16 at 21:30
I'm just modifying his existing code to get the proper record count... that's just the code in the original question + the timestamp limitation. how would you get the record count? I'm sure it would end up being just equivalent to the above code. you can also do a Select count(ip_add)..... – Sergio Fernandez Mar 28 '16 at 21:37
Sergio thanks... but i am still getting errors in this. will post the updated code soon – Vin Nwaikwu Mar 29 '16 at 19:56
I noticed I had a small typing error in my SELECT I had 2 'WHERE's, hope that was not what caused your error.... take a look an my select again. see if you still get error. – Sergio Fernandez Mar 30 '16 at 18:07
pardon my bad knowledge in php/sql, I stopped getting errors but yet to achieve this.. i now have add_time in my voting_ip table TIME-STAMP updating upon current update... I understand that i need to call that column minus 24 hours from the last time-stamp update if TRUE, count number of votes if less than limited , ADD votes else No vote... – Vin Nwaikwu Apr 01 '16 at 18:42
I know timestamp updates its col automatically, I guess no need updating it in code... now (1). how do i call last update time (2). how do I minus the 24hours from the last update time? please – Vin Nwaikwu Apr 01 '16 at 19:37

score 0 · Answer 2 · answered Mar 28 '16 at 21:04

0

you can add a timestamp column in 'voting_ip' table and set no unique keys.

then you can do the query to get last 5 records.

just subtract the time by latest record and last record

for example:

$times=mysqli_query($bd, "SELECT timestamp FROM voting_ip WHERE mes_id_fk='$id' AND ip_add='$ip' order by timestamp desc limit 5");

if(mysqli_num_rows($ip_sql) < 5 || {first record - last record < 24 hours})
...your codes...

answered Mar 28 '16 at 21:04

CSK

352
1
6

CSK thanks, but I got unexpected '{' error in your second line of code – Vin Nwaikwu Mar 29 '16 at 20:06

score 0 · Answer 3 · answered Mar 28 '16 at 21:39

Consider the following... wherein I attempt to execute the same query over and over, at approximately 2-3 second intervals.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(dt DATETIME NOT NULL);

SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
| 2016-03-28 22:37:37 |
+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
| 2016-03-28 22:37:37 |
+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
| 2016-03-28 22:37:37 |
+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO my_table (dt) SELECT DISTINCT NOW() FROM (SELECT 1) a LEFT JOIN my_table b ON NOW() < b.dt + INTERVAL 10 SECOND WHERE b.dt IS NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+---------------------+
| dt                  |
+---------------------+
| 2016-03-28 22:35:57 |
| 2016-03-28 22:36:06 |
| 2016-03-28 22:37:27 |
| 2016-03-28 22:37:37 |
| 2016-03-28 22:37:47 |
+---------------------+
5 rows in set (0.00 sec)

mysql>

How to restrict votes per day by IP in Php+Mysql voting?

3 Answers3

Linked