# Voting Script Help



## covert215 (Apr 22, 2006)

I am in the process of programming a joke site. I currently have implemented an image and joke of the day script. I also programmed a random image viewer. There is a joke database set up that allows a user to submit jokes, along with a title and category. Once a joke has been submitted, it can be rated. If after 10 votes that average is below 2, the joke is removed from the database. I want to be able to limit a user to 1 vote per joke. How can this be accomplished?


----------



## bassetman (Jun 7, 2001)

Ensabled cookies


----------



## brendandonhu (Jul 8, 2002)

You can save a cookie like basset said, and also store their IP address along with a record of which jokes they've voted on (otherwise they could clear their cookies and vote repeatedly.)


----------



## brendandonhu (Jul 8, 2002)

You could also have users register with their email address and limit them to 1 vote per user.


----------



## redivivus (Mar 30, 2006)

Or you can store some form of encrypted string in their cookies and only let them vote after a couple days on the site or something.  

It wouldnt be foolproof because you would need some good obfuscated js.

I suggest going with 1 vote per IP and/or email.


----------



## covert215 (Apr 22, 2006)

I know how to capture the ip, but i don't know how to structure my database so that it checks which items have already been voted on


----------



## brendandonhu (Jul 8, 2002)

SELECT * FROM votes WHERE ip = their_ip AND poll = poll_id
Query that, and if mysql_num_results() is greater than 0, they've already voted on that poll.


----------



## covert215 (Apr 22, 2006)

The problem is, the number of jokes in the database is dynamic. When there are more than 20 jokes or so, the code becomes very inefficient.

I think I am just going to log each IP and allow it 5 votes per day.

How do I make it so that the server recognizes that it is a new day?


----------



## brendandonhu (Jul 8, 2002)

MySQL doesn't have any problem with 20 rows in a database. That query will take well under a second to run.


----------



## covert215 (Apr 22, 2006)

20 was probably a bad example...more like 200 or so. Anyways, how do I make it so that the server recognizes that it is a new day?


----------



## brendandonhu (Jul 8, 2002)

This forum with 200,000+ members runs on MySQL, it can really handle your 200 jokes with no problem.

You can use time() to get a timestamp or date() to get the date.


----------



## covert215 (Apr 22, 2006)

So, I set up a 3 column table. IP, Votes, Day. Every time a new user votes I create a new row, logging the IP and day. I will test if each vote is made on the same day. If it recognizes a new day, it will reset the votes column. It sounds good in theory...I just have to program it.


----------



## brendandonhu (Jul 8, 2002)

Just make the table IP and Poll ID, then you can insert the IP along with an ID number for each different poll. Then when someone votes you can check if they've already voted on that specific poll.


----------



## covert215 (Apr 22, 2006)

Can I dynamically add a new column whenever a new poll is added?

btw, it seems like just me and you are conversing in 2 threads right now


----------



## brendandonhu (Jul 8, 2002)

Seems so...
You don't need to add a new column, just give each poll a unique ID number. Every time someone votes, store their IP address along with the ID number of the poll they voted in.
Then you can do SELECT * FROM votes WHERE IP=their_ip and POLL=poll_id
If there's a matching column, then they've already voted.


----------



## covert215 (Apr 22, 2006)

ohhhh...i get it

i was thinking that you wanted each ip to have its own row and have a column for each poll
you were thinking of a new row for each vote

makes much more sense


----------



## brendandonhu (Jul 8, 2002)

Right, you might also want a table to keep track of the polls and their ID numbers as wel.


----------



## covert215 (Apr 22, 2006)

i already have all that


----------



## brendandonhu (Jul 8, 2002)

Ok good, then you can also use the votes table to count the results of the poll. Just add a column for which option they chose, and use mysql_num_rows() to get the number of votes.


----------



## covert215 (Apr 22, 2006)

This is my php script that tabulates the votes. The table 'jokes' has columns for id, [total] votes, [total] rating, and average. The table 'logger' has columns for id (same as above) and ip. $ranking is equal to whatever the user voted. All of the variables at the top return their proper values. Line 17 isn't return a value.

SEE CODE BELOW


----------



## brendandonhu (Jul 8, 2002)

Is it $voted that isn't returning a value? Can you echo $sql and post that here?


----------



## covert215 (Apr 22, 2006)

When voting on joke 4:

SELECT * FROM logger WHERE ip=127.0.0.1 and id=4

The text that followed:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\....\rate.php on line 21
Vote Added
Vote Tabulated
Average Calculated
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.0.1,4)' at line 1


----------



## brendandonhu (Jul 8, 2002)

Change the top lines to this


> $id = mysql_real_escape_string(trim(stripslashes($_POST['id'])));
> $ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);


----------



## covert215 (Apr 22, 2006)

I get a ton of errors now:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\...\rate.php on line 3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\...\rate.php on line 3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\...\rate.php on line 4

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\...\rate.php on line 4
SELECT * FROM logger WHERE ip= and id=
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\...\rate.php on line 21
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


----------



## brendandonhu (Jul 8, 2002)

Whoops, move the 2 lines I wrote to after this:

```
mysql_select_db("jokes", $con);
```


----------



## covert215 (Apr 22, 2006)

i should have caught that mistake...

same errors as before:

SELECT * FROM logger WHERE ip=127.0.0.1 and id=4
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\...\rate.php on line 21
Vote Added
Vote Tabulated
Average Calculated
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.0.1,4)' at line 1


----------



## brendandonhu (Jul 8, 2002)

Ok the problem is that string values need to be quoted before putting them into MySQL.
Try it like this:

```
<?php 
$id = trim(stripslashes($_POST['id'])); 
$ip = $_SERVER['REMOTE_ADDR']; 
$ranking = Trim(stripslashes($_POST['rank'])); 
$votes = Trim(stripslashes($_POST['votes'])); 
$rating = Trim(stripslashes($_POST['rating']));
$con = mysql_connect("localhost","username","pw"); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 
mysql_select_db("jokes", $con);

$sql = sprintf('SELECT * FROM logger WHERE ip=%s and id=%s', mysql_real_escape_string($ip), mysql_real_escape_string($id)); 
$voted = mysql_query($sql,$con); 
$rows = mysql_num_rows($voted);

if($rows == 0) {

$sql="UPDATE jokes SET votes = $votes WHERE id = $id";

$votes++; 
$rating += $ranking; 
$average = ($rating/$votes);

if (!mysql_query($sql,$con)) 
  { 
      die('Error: ' . mysql_error()); 
  } 
echo "Vote Added
";

$sql="UPDATE jokes SET rating = $rating WHERE id = $id";

if (!mysql_query($sql,$con)) 
  { 
  die('Error: ' . mysql_error()); 
  } 
echo "Vote Tabulated
";

$sql="UPDATE jokes SET average = $average WHERE id = $id";

if (!mysql_query($sql,$con)) 
  { 
  die('Error: ' . mysql_error()); 
  } 
echo "Average Calculated
";

$sql="INSERT INTO logger (ip, id) VALUES ($ip,$id)";

if (!mysql_query($sql,$con)) 
  { 
  die('Error: ' . mysql_error()); 
  } 
echo "IP logged
";

if($votes == 30 && $average < 2) { 
$sql = "DELETE FROM jokes WHERE id = $id"; 
if (!mysql_query($sql,$con)) 
  { 
  die('Error: ' . mysql_error()); 
  } 
echo "Failed Judgement
"; 
}

echo ""; 
}

else { 
echo "IP already logged"; 
}

mysql_close($con);
?>
```
If that doesn't work- what field type is "ip" in the table?


----------



## covert215 (Apr 22, 2006)

I fixed things up a bit, but I still end up with:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\...\rate.php on line 18

No syntax errors though. It runs properly but it does not add IPs to the database.


----------



## covert215 (Apr 22, 2006)

[edit] see post below


----------



## brendandonhu (Jul 8, 2002)

There's no INSERT statement in that code, you need to INSERT INTO logger VALUES() for the ip and id.

Also, you could combine the update statements into one.
UPDATE jokes SET votes=$votes, average=$average, rating=$rating WHERE id=$id


----------



## covert215 (Apr 22, 2006)

whoops...i posted an older version


```
<?php

$id = Trim(stripslashes($_POST['id']));
$ip = $_SERVER['REMOTE_ADDR']; 
$ranking = Trim(stripslashes($_POST['rank']));
$votes = Trim(stripslashes($_POST['votes']));
$rating = Trim(stripslashes($_POST['rating']));

$con = mysql_connect("localhost","user","pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("jokes", $con);

$sql = sprintf('SELECT * FROM logger WHERE ip=%s and id=%s', mysql_real_escape_string($ip), mysql_real_escape_string($id));
$voted = mysql_query($sql,$con);
$rows = mysql_num_rows($voted);

if($rows == 0) {

$votes++;
$rating += $ranking;
$average = ($rating/$votes);

$sql="UPDATE jokes SET votes = $votes WHERE id = $id";
if (!mysql_query($sql,$con))
  {
      die('Error: ' . mysql_error());
  }
echo "Vote Added
";

$sql="UPDATE jokes SET rating = $rating WHERE id = $id";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Vote Tabulated
";

$sql="UPDATE jokes SET average = $average WHERE id = $id";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Average Calculated
";

$sql="INSERT INTO logger (ip, id) VALUES ($ip,$id)";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "IP logged
";

if($votes == 30 && $average < 2) {
$sql = "DELETE FROM jokes WHERE id = $id";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Failed Judgement
";
}

//echo "";
}
else{
echo "IP already logged";
}

mysql_close($con);

?>
```
IP is a VARCHAR.

Error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\...\rate.php on line 18
Vote Added
Vote Tabulated
Average Calculated
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.0.1,2)' at line 1


----------



## brendandonhu (Jul 8, 2002)

Ok, try changing the $sql line to this
$sql = 'SELECT * FROM logger WHERE ip=\'$ip\' and id=\'$id\'';

And echo $sql and post it if that still gives an error.


----------



## covert215 (Apr 22, 2006)

SELECT * FROM logger WHERE ip='$ip' and id='$id'

Vote Added
Vote Tabulated
Average Calculated
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.0.1,2)' at line 1

Atleast we are getting a resource as a result now. The info still isn't being put in the DB


----------



## brendandonhu (Jul 8, 2002)

The query needs to have the variables replaced with values, change it to this if its not working:
$sql = "SELECT * FROM logger WHERE ip='" . $ip . "' and id='" . $id . "'";

The values also need to be quoted in your INSERT statement
$sql="INSERT INTO logger (ip, id) VALUES ('" . $ip . "','" . $id . "')";


----------



## covert215 (Apr 22, 2006)

Thank you very much. It finally works. Thanks for throwing solutions at me when I was completely clueless as to the problem.


----------



## brendandonhu (Jul 8, 2002)

[tsg=yourewelcome]yourewelcome[/tsg]


----------

