Only Allowing 1 Upvote Via MYSQL Explode

Question

I'm creating an upvote system on my site. Currently, the upvotes are working but I want to limit the upvotes to 1 per user. The votes are added by updating the total votes:

mysqli_query($connection,"UPDATE news SET votes=(votes + 1) WHERE ID='". $_POST['id1'] . "'");

And then I record that the user voted on that article by using:

mysqli_query($link,"UPDATE users SET votes=CONCAT(votes,'" . $_POST['id1'] .",') WHERE ID='". $_SESSION['ID'] . "'");

Now, I try to implement only allowing an upvote if the article id is not already in their "votes" database. I'm storing votes like 1,4,7,12 so that user voted on articles 1, 4, 7 and 12. So I'm trying to use explode to check if they already voted on an article:

$results = mysqli_query($link2,"SELECT * FROM users WHERE ID ='" . $_SESSION['ID'] . "'");

while($result = mysqli_fetch_array( $results )){

  $votes = explode(",", $result['votes']);

  foreach($votes as $vote) {

  if ($vote = $_POST['id1']) {
//Do nothing
} else {
mysqli_query($connection,"UPDATE news SET votes=(votes + 1) WHERE ID='".     $_POST['id1'] . "'");
mysqli_query($link,"UPDATE users SET votes=CONCAT(votes,'" . $_POST['id1'] .",') WHERE ID='". $_SESSION['ID'] . "'");

Any help or suggestions on a better way to accomplish the same thing would be greatly appreciated, thanks!

It might be easier to create a new record for each vote and have the query do the work. — Rob, Jul 17 '15 at 20:32
Comma-separated lists in databases are almost always a bad idea. — Barmar, Jul 17 '15 at 21:35
For more about why you want to use a vote table and not comma-separated fields: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Andy Lester, Jul 17 '15 at 22:03
Ah thanks for the heads up on not using a list, working on a vote table now! — Alteredorange, Jul 17 '15 at 22:21

score 1 · Accepted Answer · answered Jul 17 '15 at 20:57

1

Use a vote table:

CREATE TABLE voted (
   user_id INT NOT NULL,
   news_id INT NOT NULL,
   FOREIGN KEY (user_id) REFERENCES user(id),
   FOREIGN KEY (news_id) REFERENCES news(id)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Add a unique constraint on this:

CREATE UNIQUE INDEX idx_voted_nn_1 ON voted (user_id,news_id);

Then use some php code which exits on a constraint issue:

$connection = new mysqli("example.com", "user", "password", "database");
$query="INSERT INTO voted (user_id,news_id) VALUES (?,?)";
$stmt=$connection->query($query);
$stmt->bind("ii",$_SESSION['ID'],$_POST['ID']);
$stmt->execute();
$error=$mysqli_stmt->errno($stmt);
if(!$error) update your news vote;
else do nothing or say something to user;

answered Jul 17 '15 at 20:57

Norbert

6,026
3
17
40

Sweet, I'm trying this out now. Should I replace the VALUES (?,?) with my variables, or does the (?,?) link with the "ii"? I'm not too familiar wtih PDO – Alteredorange Jul 17 '15 at 21:53
1

Please read up on mysqli/pdo then because your version was slightly hackable. This version is not. PS: FOund one more small issue in your code: **if ($vote = $_POST['id1'])** should be **if ($vote== $_POST['id1'])** – Norbert Jul 17 '15 at 22:02
Hmm. I've got the table set up but it won't insert anything into the table. My current code is: https://gist.github.com/alteredorange/0fad2c9a67c3a1c0d185 – Alteredorange Jul 17 '15 at 22:16
Got it working with some mysqli, thanks for the help! – Alteredorange Jul 17 '15 at 23:29

Only Allowing 1 Upvote Via MYSQL Explode

1 Answers1