SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Stopping Duplicate Inserts

    Hi,

    I am trying to come up with a way of stopping duplicate inserts from being into a table. I have got this point which creates an error message for duplicate follows but I am stuck on what to try next. Any suggestions please?



    Code:
    $query = "INSERT INTO `follow` (`user_id`, `follow_user_id`) VALUES ('{$profileid}', '{$followerid}')";
    $result = mysql_query($query);
    
    	if (!$result){
    	$errors[] = "Query: {$query}<br>Error: " . mysql_error();
    	}
    }       
      
             if(($profileid) == ($followerid)) {
    $errors['sameprofile'] = "You cannot follow your own profile.";
    }
      
      $loginprofile = intval($_SESSION['userID']); 
      
      $query_rsSearch = "SELECT * FROM follow WHERE `follow_user_id` = '$loginprofile'";
    $rsSearch = mysql_query($query_rsSearch) or die(mysql_error());
    $row_rsSearch = mysql_fetch_assoc($rsSearch);
    $totalRows_rsSearch = mysql_num_rows($rsSearch);
      
      $duplicate = $profileid; 
      
      
      if(($loginprofile) == $row_rsSearch['follow_user_id']) {
    $errors['duplicatefollow'] = "You already follow this profile.";
    }

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Place a composite primary key on user_id, follow_user_id.

    You want to check for error # 1062 on insert, which is a duplicate record error, then you can display your message. On top of that, you can remove the link if they are following the user already.

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    One way to stop duplicates is to set one or more columns as being UNIQUE in your database.

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Cups View Post
    One way to stop duplicates is to set one or more columns as being UNIQUE in your database.
    Or that, that would be handled by composite primary key, as primary keys are forced to be unique.

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Apologies, If I didn't explain it properly. The problem is I do want duplicates being inserted.

    So I cant have:

    412 and 555
    412 and 555

    But I can have:

    412 and 555
    352 and 555

    So I need something in the insert code to stop the same number being inserted twice against the same number.

  6. #6
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I have added this top part. However I cant work out how to stop the duplicate insert.

    It does return the error message.


    Code:
    	$query = mysql_query("SELECT * FROM follow WHERE follow_user_id  = '". $followerid ."'"); 
    $duplicatefollow = null;
    if (mysql_num_rows($query) > 0) 
    { 
    $duplicatefollow = 'You Already Follow This Profile.</a>.'; 
    }   
    
      
    if(isset($_POST['followbutton'])) {
    
    
    $query = "INSERT INTO `follow` (`user_id`, `follow_user_id`) VALUES ('{$profileid}', '{$followerid}')";
    $result = mysql_query($query);
    
    
    	if (!$result){
    	$errors[] = "Query: {$query}<br>Error: " . mysql_error();
    	}
    }

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by justlukeyou View Post
    Hi,

    Apologies, If I didn't explain it properly. The problem is I do want duplicates being inserted.

    So I cant have:

    412 and 555
    412 and 555

    But I can have:

    412 and 555
    352 and 555

    So I need something in the insert code to stop the same number being inserted twice against the same number.
    Put a UNIQUE index on the columns `user_id` and `follow_user_id` and you would achieve this.

    The combination of the two fields must be unique.

    When you subsequently attempted to add a duplicate (as in your 412 / 555 example) your db will let you know with a particular error number which you can detect and then fork your code/issue an error.

    This may not be the best solution if either of those fields contain nulls though.

  8. #8
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    That sounds quite complicated for skill level. How would I get the error number. Would it come from MySQL or from the PHP code.

    I take it is not possible to have a PHP code which identifies if the 'followerid' and 'profileid' are already in the table together?

    So it would read.

    412 and .... allow insert 555
    412 and 555 block insert 555

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    You'd just check mysql_errno() instead of mysql_error.

    If you dont want to read about how a UNIQUE index works in Mysql, or handling like I suggested then, yes, you can see if the combination of numbers exists and if it does not -- go on and do your insert.

    In pseudocode that would look like:

    Code:
    $exists = do select id from your table where `user_id` = 412 AND `follow_user_id`555
    
    if( $exists is positive ) {
    
     abort and tell the user
    
    }else{
    
     go on with your insert
    
    }
    All it means is that:

    you are making 2 db calls
    you are not enforcing this integrity in your db
    your code is arguably now a little harder to follow

    How many other places will you have to do this check? in the future maybe? What if you forget to do this pre-check...

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by justlukeyou View Post
    Hi,

    That sounds quite complicated for skill level. How would I get the error number. Would it come from MySQL or from the PHP code.

    I take it is not possible to have a PHP code which identifies if the 'followerid' and 'profileid' are already in the table together?

    So it would read.

    412 and .... allow insert 555
    412 and 555 block insert 555
    Yes, you can, it requires a second query. Or, you can be really slick, just do the insert statement (after you have a pk or a unique index on the two columns) and check for the duplicate error number to return the fact that they are already following. Same result, the latter is cleaner and faster.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I have had a go at this but I have got stuck. Am I doing this part correct please?



    Code:
    $exists =  mysql_query("SELECT * FROM follow WHERE user_id = '". $profileid ." AND follow_user_id= '". $followerid ."'"
    if (mysql_num_rows($exists) = 1) 
    { 
    $duplicatefollow = 'You Already Follow This Profile.</a>.'; 
    }   else
    {
    
    
    }

  12. #12
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Actually this code seems to be working. Once a user follows another user it stops duplicate inserts. However the error message remains on the screen even when returning back to the users profile.

    Is it possible to remove the error message only once?


    Code:
    <?php
     
    $ID = mysql_real_escape_string($_GET['ID']);
    $followerid = intval($_SESSION['userID']);
    $profileid  = $ID;		
    
    
    
    $query = mysql_query("SELECT * FROM follow WHERE user_id = '". $profileid ."' AND follow_user_id= '". $followerid ."'"); 
    $duplicatefollow = null;
    if (mysql_num_rows($query) > 0) 
    { 
    $duplicatefollow = 'You Already Follow This Profile.</a>.'; 
    }   
    else
    {
      
    if(isset($_POST['followbutton'])) {
    
    
    $query = "INSERT INTO `follow` (`user_id`, `follow_user_id`) VALUES ('{$profileid}', '{$followerid}')";
    $result = mysql_query($query);
    
    
    	if (!$result){
    	$errors[] = "Query: {$query}<br>Error: " . mysql_error();
    	}
    }  
    }     
      
             if(($profileid) == ($followerid)) {
    $errors['sameprofile'] = "You cannot follow your own profile.";
    }
      
      $loginprofile = intval($_SESSION['userID']); 
      
      $query_rsSearch = "SELECT * FROM follow WHERE `follow_user_id` = '$loginprofile'";
    $rsSearch = mysql_query($query_rsSearch) or die(mysql_error());
    $row_rsSearch = mysql_fetch_assoc($rsSearch);
    $totalRows_rsSearch = mysql_num_rows($rsSearch);
      
    
    
            
    ?>

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    This is row wrong:

    A single = means you are assigning 1 to it.
    PHP Code:
    if (mysql_num_rows($exists) = 1

    You should be checking if the value is the integer 1 (or greater if you already have dirty data in your db)


    PHP Code:
    if (mysql_num_rows($exists) === 1
    You could be doing a looser check with == but that then throws up the possibility of other problems.

    You might benefit from carefully reading the PHP Truth Table and playing with some variables to make sure you have understood the difference between =, == and ===.

  14. #14
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am no longer using that part but when I do follow someone it blocks the duplicate insert but always displays the message "You Already Follow This Profile". So I just need to stop this part and it should be okay.

    Code:
    <?php
     
    $ID = mysql_real_escape_string($_GET['ID']);
    $followerid = intval($_SESSION['userID']);
    $profileid  = $ID;		
    
    
    
    $query = mysql_query("SELECT * FROM follow WHERE user_id = '". $profileid ."' AND follow_user_id= '". $followerid ."'"); 
    $duplicatefollow = null;
    if (mysql_num_rows($query) > 0) 
    { 
    $duplicatefollow = 'You Already Follow This Profile.</a>.'; 
    }   
    else
    {
      
    if(isset($_POST['followbutton'])) {
    
    
    $query = "INSERT INTO `follow` (`user_id`, `follow_user_id`) VALUES ('{$profileid}', '{$followerid}')";
    $result = mysql_query($query);
    
    
    	if (!$result){
    	$errors[] = "Query: {$query}<br>Error: " . mysql_error();
    	}
    }  
    }     
      
             if(($profileid) == ($followerid)) {
    $errors['sameprofile'] = "You cannot follow your own profile.";
    }
      
      $loginprofile = intval($_SESSION['userID']); 
      
      $query_rsSearch = "SELECT * FROM follow WHERE `follow_user_id` = '$loginprofile'";
    $rsSearch = mysql_query($query_rsSearch) or die(mysql_error());
    $row_rsSearch = mysql_fetch_assoc($rsSearch);
    $totalRows_rsSearch = mysql_num_rows($rsSearch);
      
    
    
            
    ?>

  15. #15
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Any suggestions please on how I can remove the error message once it is shown when the duplicate insert is attempted?

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    When you are doing a conditional check like this:
    PHP Code:
    if(mysql_num_rows($query) > 0) { 
    and your code is not forking as you expect, then var_dump that variable you are checking and take a good look at what it contains vs the condition you are checking against.

    I will make some other suggestions about this part of your code too ...
    PHP Code:
    $ID mysql_real_escape_string($_GET['ID']);
    $followerid intval($_SESSION['userID']);
    $profileid  $ID;        

    $query mysql_query("SELECT * FROM follow WHERE user_id = '"$profileid ."' AND follow_user_id= '"$followerid ."'"); 
    This is what I would have done:
    PHP Code:
    $profileid = (int)$_GET['ID'];
    $followerid $_SESSION['userID'];
        

    $query mysql_query("SELECT ID FROM follow 
                          WHERE user_id = 
    $profileid 
                          AND follow_user_id = 
    $followerid"); 
    Now here again is your original code with my comments:

    PHP Code:
    // you set a var called ID, then on line 3 you create another var and re-assign it
    // this just makes your code hard to follow and is wasteful
    $ID mysql_real_escape_string($_GET['ID']);
    // why do real_escape_string if ID is going to be an integer?
    // setting it an integer with (int) is better here because if it comes across anything
    // other that an integer it sets it to 0 (zero) which will not devastate your db table

    // but SESSION vars should contain data which you already set somewhere else
    // you should have set it to an integer when you set that session var. You should
    // be able to trust your own data, otherwise it suggests you are allowing strings to be set
    // as userIDs
    $followerid intval($_SESSION['userID']);

    // see the top comment
    $profileid  $ID;        


    // if profileid and followerid are both indeed integers, then there is no need to quote them
    // so your sql string should be easier to type, less error prone and far easier to read

    $query mysql_query("SELECT * FROM follow WHERE user_id = '"$profileid ."' AND follow_user_id= '"$followerid ."'"); 

    // stop doing select * when you don't need to access all the data in that row, the id alone would bring back 
    // what you are looking for, a positive result set (at this stage anyway).
    // try and make your SQL stand out from the rest of you code by using new lines and tabs (if you like) 
    // it'll make your sql code easier to spot, fix and add extra lines 
    You should also stick to a var naming convention so you confuse yourself and others less.

    You have used variously:

    userID (caps for id)
    followerid (all lowercase)

    you might also use other conventions such as camelCase (followerId, or followerID), PascalCase (FollowerId) or use all lower case and join with underscores such as user_id, follower_id.

    It matters not to me which one you pick, but pick one and stick with it and you will make far less errors and feel more comfortable. It might only seem a simple thing but will matter a lot to you in the future.

    I hope you don't mind me saying all that, it just hurts to see someone making exactly the same errors that I did when I was starting out.

  17. #17
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I dont mind at all, thanks for the advice.

    Im not sure what you mean by "forking".

    When I try to follow the same person twice it displays the message "You Already Follow This Profile" as I want it to. However the error message continues to be displayed even when I go back to the page.


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
  •