How do I prevent multiple entries like this in database?

Question

Is there anyway to prevent a user from clicking like multiple times without having them be inserted into the database like this?

I know I could just disable the button, but I want do this all without reloading the page.

    $dbh = mysqli_connect("localhost", "root", "", "testdb");
$postid = $_POST['postid'];
if(isset($_POST['liked'])&& empty($_SESSION[$postid])){
    $_SESSION[$postid] = TRUE;

    $userid = $_SESSION['username'];
    $result = mysqli_query($dbh,"SELECT * FROM user_images WHERE id=$postid");
    $row = mysqli_fetch_array($result);
    $n = $row['likes'];
    mysqli_query($dbh,"INSERT INTO likes(username, postid) VALUES('$userid', '$postid')");
    mysqli_query($dbh,"UPDATE user_images SET likes=$n+1 WHERE id=$postid");
    echo $n+1;
    exit();
}


if (isset($_POST['unliked'])){
    $postid = $_POST['postid'];
    $result = mysqli_query($dbh,"SELECT * FROM user_images WHERE id=$postid");
    $row = mysqli_fetch_array($result);
    $n = $row['likes'];
    $userid = $_SESSION['username'];
    //delete from likes before updating
    mysqli_query($dbh,"DELETE FROM likes WHERE postid=$postid AND username=$userid");
    mysqli_query($dbh,"UPDATE user_images SET likes=$n-1 WHERE id=$postid");
    echo $n-1;
    exit();
}

Just check what's in the database before you insert something new. — KIKO Software, Nov 11 '17 at 19:12
As noted previously, pleasssse don't use this code. You are open to SQL injections, and doing more than you need to. — chris85, Nov 11 '17 at 19:14
You can make a primary key from username & postid and on the second insert it will give you an error. — Radu, Nov 11 '17 at 19:15
What if I have multiple users that have multiple posts? ...I tried using UNIQUE INDEX, both no luck there. — Carrie Shower, Nov 11 '17 at 19:15
As to your current issue, you could create a session and once they vote set the specific session value to true, if they come again and already set disallow. — chris85, Nov 11 '17 at 19:16
I'm getting this error: `Incorrect table definition; there can be only one auto column and it must be defined as a key` @Radu — Carrie Shower, Nov 11 '17 at 19:17
Unique INDEX on both keys combined together should not allow 2 lines with same username and postid in likes table — Radu, Nov 11 '17 at 19:18
@chris85 How would I write that into my code? Sorry, I'm still learning PHP and SQL. — Carrie Shower, Nov 11 '17 at 19:18
`session_start()` at the start. Move `$postid = $_POST['postid'];` to outside the conditionals. Add `&& empty($_SESSION[$postid])` inside the conditional statement. Have `$_SESSION[$postid] = TRUE;` in the conditional blocks after the confirming the `insert`/`update` worked. — chris85, Nov 11 '17 at 19:28
I was able to confirm that the information went in the database, but I can still insert it multiple times @chris85 — Carrie Shower, Nov 11 '17 at 19:35
Please note that your server is open to SQL injection attacks. This Q&A will tell you how to do database interactions safely: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Nov 11 '17 at 20:06
You need it on `unlike` as well... and you have `session_start()` as well? — chris85, Nov 11 '17 at 20:12

score 0 · Answer 1 · answered Nov 11 '17 at 19:49

0

        if(isset($_POST['liked'])){
    $postid = $_POST['postid'];
    $userid = $_SESSION['username'];
    $result = mysqli_query($dbh,"SELECT * FROM user_images WHERE id=$postid");
    $row = mysqli_fetch_array($result);
    $n = $row['likes'];
    $check_query = mysqli_query($dbh,"SELECT * FROM likes WHERE username = '$userid' AND postid = '$postid'");
    $found = mysqli_num_rows($check_query);
    if ($found == 0) {
       mysqli_query($dbh,"INSERT INTO likes(username, postid) VALUES('$userid', '$postid')");
       mysqli_query($dbh,"UPDATE user_images SET likes=$n+1 WHERE id=$postid");
       echo $n+1;
   }
    exit();
}

answered Nov 11 '17 at 19:49

Iyad Basim

101

Thank you, can you take a look at my `isset($_POST['unliked']`? I can't delete anything for some reason... – Carrie Shower Nov 11 '17 at 20:01
well, it doesn't really tell me, but when I go to the database once I click the 'unlike' button, it doesn't delete – Carrie Shower Nov 11 '17 at 20:12

score 0 · Accepted Answer · answered Nov 11 '17 at 20:11

First you need to create a unique index in the table, to ensure that no duplicates entries are allowed even if you have a mistake in your PHP code. Since an user can like several posts you have to make the index using both columns:

create unique index IDU_likes on likes (username,postid);

Then you have to modify your code and check if the insert failed (you should be doing it always anyway). Also, the code you are using is open to SQL injection, you should use prepared statements and bind the variables instead of manually constructing the query, for example:

$stmt=mysqli_prepare($dbh,"INSERT INTO likes(username, postid) VALUES(?, ?)");
mysqli_stmt_bind_param($stmt,'si',$userid,$postid);
if(mysqli_stmt_execute($stmt) {
  $n=$n+1;
  $stmt=mysqli_prepare($dbh,"UPDATE user_images SET likes=? WHERE id=?");
  mysqli_stmt_bind_param($stmt,'ii',$n,$postid);
  mysqli_stmt_execute($stmt)
}
echo $n;

Thank you so much... but why can't a delete a user from the `likes` table? — Carrie Shower, Nov 11 '17 at 20:35
What do you mean, that you get an error when you try to delete an user? What error are you getting? (use [`mysqli_error()`](http://php.net/manual/en/mysqli.error.php) for getting the last DB error). — Alberto Martinez, Nov 11 '17 at 21:15
@AlbertoMarinez I'm Sorry!! It was because of my quotations. Thank you! — Carrie Shower, Nov 11 '17 at 22:24

How do I prevent multiple entries like this in database?

2 Answers2