SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to register votes?

    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. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    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.
    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.
    PHP Code:
    if (mysqli_query($link$sql) { // vote inserted
     
    } else { // vote already exist


  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by albatrosl View Post
    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.
    PHP Code:
    if (mysqli_query($link$sql) { // vote inserted
     
    } else { // vote already exist

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

  5. #5
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Except a false is also returned if the connection failed, unless of course you have a proper exception system in place.
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •