Best way to register votes?

Databases
1

So in the website there are posts, and users can vote them. There is an Id from the post and an Id from the user. Database is MySQL.

I’m pretty shure what I’m doing is worng: I have a varchar(22), and in it I put for exemple 233.788, that is: post id “.” user id

Otherways if I have two colums one for each id, it will have to search all the id from a post and then see if there is the other id of the user. I suppose this is the correct way perhaps faster seraching INT’s, but using the other I just make and insert and if it fails that probably means the vote alredy exist because is unique key, and with only one simple query I do the job. With the correct methot I need a more complex query.

I’m afraid I not explained it well so lets see both exemples:

What I’m doing:

$vot_id = $post_id.“.”.$user_id;
$sql = “INSERT INTO vot_post (vot_pos) VALUE (‘$vot_id’)”;

In the database: “varchar(22)” Unique Key. Exemple: 233.788

What I think I should do but I really don’t know If will be better or faster:

$post_id = 233;
$user_id = 788;
$sql = “INSERT INTO vot_post(id_post,id_user) SELECT ‘$post_id’, ‘$user_id’ FROM DUAL WHERE NOT EXISTS (SELECT id_post FROM vot_post WHERE id_post=‘$post_id’ AND id_user=‘$user_id’ LIMIT 1)”;
In the database: “INT(12)” and “INT(10)”

2

the second way is significantly better. Declare a unique key across the two columns. instead of the way you are trying to insert the data, use INSERT IGNORE syntax so you don’t have to look up the value if it exists, it will just ignore that, otherwise will insert non duplicate values.

3

Ok thanks, I didn’t know I can make a combined unique key.

But I don’t want to use INSERT IGNORE, because using only INSERT it gives me TRUE or FALSE that way I know if the vote alredy exist or not.


if (mysqli_query($link, $sql) { // vote inserted
 } else { // vote already exist
}
4

Except a false is also returned if the connection failed, unless of course you have a proper exception system in place.

5

I don’t have a proper exception system, but it’s not a critic function, and if the connection fails probably there isn’t to much I can do. What I have is an error handler that write in a text document all database errors, so that way I can know if something happened.