用户投票表

Question

I have a voting system which works mostly how I would expect it to work. Users click a up or down vote button up or down the votes of the particular item that is on their screen and the database updates as one would expect and the votes are recorded. However I don't want users to vote multiple times.

I have a table in my database called 'votes' and within it I have an 'id' column which is the primary key and I have a 'reportid' column which inserts the reports id into the table, I also have a field called 'userid' which records the session user id of the user who is currently logged in. Once the vote button is pressed it records this information in the database as one would expect.

My issue is that I want to be able to check my database to see whether the reportid and userid are both together in the same row, if a user has voted and the reportid and userid are recorded then i don't want my code to execute, however if they don't exist together then i want the code to execute.

Below you can see my code thus far:

<h4> Votes </h4>

<table>
<tr>
<th>Up-Votes</th>
<th>Down-Votes</th>
<tr/>
<tr>
<?php 


 // Connects to your Database 
 mysql_connect("localhost", "root", "pass") or die(mysql_error()); 
 mysql_select_db("tablename") or die(mysql_error()); 

 if ( isset( $_POST['Upvote'] ) )
 {

 mysql_query ("UPDATE reports SET up = up + 1 WHERE reportID = $id"); 
 mysql_query ("INSERT INTO votes (reportname, userid)
 VALUES ('$id', '$_SESSION[sess_uid]')"); /* This code is what is used to check if the user has voted */




 } else if (isset( $_POST['Downvote'] ))
 {
  mysql_query ("UPDATE reports SET down = down + 1 WHERE reportID = $id"); 
  mysql_query ("INSERT INTO votes (reportname, userid) 
  VALUES ('$id', '$_SESSION[sess_uid]')"); /* This code is what is used to check if the user has voted */


 }

  //Puts SQL Data into an array
  $data = mysql_query("SELECT * FROM reports WHERE reportID = $id") or die(mysql_error()); 

  while($ratings = mysql_fetch_array( $data )) 
  { 

  //This outputs the sites name 
  Echo "<td>" .$ratings['up']."</td>"; 
  Echo "<td>" .$ratings['down']."</td>"; 

  } 

  ?>
  </tr>
  </table>
  <br/>
  <form method='POST'>
  <input type='submit' value='up' name='Upvote' class="myButton">
  <input type='submit' value='down' name='Downvote' class="myButton">
  </form>

Again my database tables update as expected by I am unsure how to check for the existing information and to not let users vote again if their details are recorded.

Thanks

Answer 1

Give this a whirl:

SELECT count(*) FROM votes WHERE reportname = $id AND userid = $_SESSION['sess_uid'];

Run that query before the insert/update query. If the count is greater than 0, then don't run the other queries.

By the way, your queries are susceptible to SQL injection, and that is quite dangerous. Also, mysql_* functions (mysql_query, mysql_fetch_assoc) are deprecated in PHP and should be replaced with PDO.

See here: http://www.veracode.com/security/sql-injection

and here: http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059