Prevent Duplicate Entries

 

Results 1 to 15 of 15
I have a problem, as you probably understand from the topic title Anyways i have ...
  1. #1
    Green Arrow is offline Newbies
    Join Date
    Jul 2009
    Posts
    18
    Thanks
    3
    Thanked 3 Times in 2 Posts

    Default Prevent Duplicate Entries

    I have a problem, as you probably understand from the topic title Anyways i have a website where i display "recent searches" and i dont want the same searches to show up at the same time, for example sometimes someone would search for "something random" and probably hits enter like 10 times, that dont look so good on my site... I have tryed to figure this out using google for the past hours... But nothing seems to work, ill include the table ill use for the searches:

    Code:
    CREATE TABLE `search` (
      `id` bigint(20) NOT NULL auto_increment,
      `title` varchar(40) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3708 ;
    and the code i use to insert into this table:

    PHP Code:
    if($_POST['q']){
    $searchtitle $_POST['q'];
    $Fix = array("<"">""#""{""}""//");
    $searchtitle str_replace($Fix""$searchtitle);
    $insertSQL "insert into search (title) values ('$searchtitle')";
    mysql_query($insertSQL);
    }
    ?> 
    Any help would be very welcome, before i go




  2. #2
    qrazy's Avatar
    qrazy is offline Senior Member
    Join Date
    Mar 2012
    Posts
    860
    Thanks
    929
    Thanked 917 Times in 421 Posts

    Default Re: Prevent Duplicate Entries

    You don't have to restrict when inserting it into the DB. You should use DISTINCT keyword in mysql when fetching the records while displaying. An example will be

    SELECT DISTINCT `keyword`FROM `search`;

    will return unique keywords from the table.

  3. The Following User Says Thank You to qrazy For This Useful Post:

    Green Arrow (12-10-2012)

  4. #3
    Green Arrow is offline Newbies
    Join Date
    Jul 2009
    Posts
    18
    Thanks
    3
    Thanked 3 Times in 2 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by qrazy View Post
    You don't have to restrict when inserting it into the DB. You should use DISTINCT keyword in mysql when fetching the records while displaying. An example will be

    SELECT DISTINCT `keyword`FROM `search`;

    will return unique keywords from the table.
    Here is the code wich i use to display the searches, could you maybee help me implement what you posted into it, sorry for being a total NOOB, i should realy learn PHP someday...

    PHP Code:
    <?php
    $resultat 
    mysql_query("select title from search ORDER BY id DESC limit 20");
    while (
    $raekke mysql_fetch_array($resultat)) {
    extract($raekke);
                
    echo 
    "";
    }
    ?>
    Edit: Or could it be as simple as? Just adding SELECT DISTINCT `keyword`FROM `search`; to the code above? It was Thank you so much for the help!
    Last edited by Green Arrow; 12-10-2012 at 10:24 AM.

  5. #4
    jazzc's Avatar
    jazzc is offline Programming & Web Design Moderator black hat forumsblack hat forumsblack hat forums
    Join Date
    Jan 2009
    Posts
    2,155
    Thanks
    5,951
    Thanked 6,842 Times in 2,420 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by qrazy View Post
    You don't have to restrict when inserting it into the DB.
    Noooooo!!!!!!

    Your top priority is to keep your data consistent with your model. Never pollute your data or you 're getting into a future nightmare.

  6. The Following User Says Thank You to jazzc For This Useful Post:

    madoctopus (12-10-2012)

  7. #5
    qrazy's Avatar
    qrazy is offline Senior Member
    Join Date
    Mar 2012
    Posts
    860
    Thanks
    929
    Thanked 917 Times in 421 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by Green Arrow View Post
    Edit: Or could it be as simple as? Just adding SELECT DISTINCT `keyword`FROM `search`; to the code above? It was Thank you so much for the help!
    This is just an example, you might need to customize depending on what fields you're going to display when you fetch the results, probably the keyword and the url for it. You'll find number of tuts to do this.

    Quote Originally Posted by jazzc View Post
    Noooooo!!!!!!

    Your top priority is to keep your data consistent with your model. Never pollute your data or you 're getting into a future nightmare.
    This isn't polluting. You should never ignore any data after all this will be helpful for analytic purpose or the other.

  8. #6
    Green Arrow is offline Newbies
    Join Date
    Jul 2009
    Posts
    18
    Thanks
    3
    Thanked 3 Times in 2 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by qrazy View Post
    This is just an example, you might need to customize depending on what fields you're going to display when you fetch the results, probably the keyword and the url for it. You'll find number of tuts to do this.
    Well it worked as i wanted when i added "DISTINCT" to my code, i didnt paste the whole code btw, i excluded the keyword and url part, and some replace stuff.

  9. #7
    Green Arrow is offline Newbies
    Join Date
    Jul 2009
    Posts
    18
    Thanks
    3
    Thanked 3 Times in 2 Posts

    Default Re: Prevent Duplicate Entries

    Btw i was just thinking of something, for example if a word is less then 5 characters, is there a way to make so it wont show up?

  10. #8
    qrazy's Avatar
    qrazy is offline Senior Member
    Join Date
    Mar 2012
    Posts
    860
    Thanks
    929
    Thanked 917 Times in 421 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by Green Arrow View Post
    Btw i was just thinking of something, for example if a word is less then 5 characters, is there a way to make so it wont show up?
    Use a WHERE clause with LENGTH(FIELD_NAME) function to refine the query based on the number of character.

  11. #9
    jazzc's Avatar
    jazzc is offline Programming & Web Design Moderator black hat forumsblack hat forumsblack hat forums
    Join Date
    Jan 2009
    Posts
    2,155
    Thanks
    5,951
    Thanked 6,842 Times in 2,420 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by Green Arrow View Post
    Btw i was just thinking of something, for example if a word is less then 5 characters, is there a way to make so it wont show up?
    WHERE CHAR_LENGTH(str) < 5

    LENGTH() returns bytes so in a 2-byte encoding you won't get the right result

  12. The Following User Says Thank You to jazzc For This Useful Post:

    qrazy (12-10-2012)

  13. #10
    madoctopus's Avatar
    madoctopus is offline Supreme Member
    Join Date
    Apr 2010
    Posts
    1,202
    Thanks
    1,018
    Thanked 3,099 Times in 933 Posts

    Default Re: Prevent Duplicate Entries

    The DB table should have a UNIQUE index on keyword field. That way you can't insert duplicates.

    When somebody searches you do 2 operations:

    INSERT INTO mykeywords (keyword, num_searched) VALUES ('keyword here', 1);
    UPDATE mykeywords SET num_searched = num_searched+1 WHERE keyword='keyword here';

    First will trigger an error if there's already that keyword. But you don't care about it. If it doesn't exist, it just creates it. Second query just increments thenumber of searches.

    When you display the searches, you just do a SELECT.

    This is probably the most elegant solution.

    Als, instead of that newb-grade filtering you do with str_replace for HTML characters you should use:

    $filteredOutput = htmlentitles($str, ENT_QUOTES, 'UTF-8');

    That produces text safe to display in the HTML page without any XSS risk.

    However, in this case you don't want just HTML sanitized but MySQL santized too. So you should do before the query this:

    $searchtitle = mysql_real_escape_string($searchtitle);

    That will call the MySQL server and instruct ti to escape/sanitize the value for use in the QUERY. That way you avoid SQL injection attacks.
    Last edited by madoctopus; 12-10-2012 at 01:27 PM.

  14. #11
    jazzc's Avatar
    jazzc is offline Programming & Web Design Moderator black hat forumsblack hat forumsblack hat forums
    Join Date
    Jan 2009
    Posts
    2,155
    Thanks
    5,951
    Thanked 6,842 Times in 2,420 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by madoctopus View Post
    INSERT INTO mykeywords (keyword, num_searched) VALUES ('keyword here', 1);
    UPDATE mykeywords SET num_searched = num_searched+1 WHERE keyword='keyword here';

    First will trigger an error if there's already that keyword. But you don't care about it.
    It 's not a good practice. You can't know if the error happened because of the constraint violation or something irrelevant (e.g. "mysql server has gone away"). That makes your error handling code ambiguous. Instead, you can do either:

    INSERT IGNORE (which will throw a warning instead of an error)
    INSERT ... ON DUPLICATE KEY UPDATE
    REPLACE ... WHERE ...
    Last edited by jazzc; 12-10-2012 at 01:39 PM.

  15. #12
    madoctopus's Avatar
    madoctopus is offline Supreme Member
    Join Date
    Apr 2010
    Posts
    1,202
    Thanks
    1,018
    Thanked 3,099 Times in 933 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by jazzc View Post
    It 's not a good practice. You can't know if the error happened because of the constraint violation or something irrelevant (e.g. "mysql server has gone away"). That makes your error handling code ambiguous. Instead, you can do either:

    INSERT IGNORE (which will throw a warning instead of an error)
    INSERT ... ON DUPLICATE KEY UPDATE
    REPLACE ... WHERE ...
    that works too. But you DO know what the error is. There's a specific error code for index collision.

  16. The Following User Says Thank You to madoctopus For This Useful Post:

    jazzc (12-10-2012)

  17. #13
    Green Arrow is offline Newbies
    Join Date
    Jul 2009
    Posts
    18
    Thanks
    3
    Thanked 3 Times in 2 Posts

    Default Re: Prevent Duplicate Entries

    Again thanks guys for the help, have something to play around with for a while now, and hopefuly i get everything to work, it usaly takes some errors first tought :P

  18. #14
    jazzc's Avatar
    jazzc is offline Programming & Web Design Moderator black hat forumsblack hat forumsblack hat forums
    Join Date
    Jan 2009
    Posts
    2,155
    Thanks
    5,951
    Thanked 6,842 Times in 2,420 Posts

    Default Re: Prevent Duplicate Entries

    Quote Originally Posted by madoctopus View Post
    that works too. But you DO know what the error is. There's a specific error code for index collision.
    That 's true, my bad expressing it like that.

    The idea is that if you 're going to be deciding the flow depending on examining the error code you 're losing the advantage of having a very simple error handling mechanism
    Code:
    if (!$ret) {
        // Log and
        return false;
    };
    Now, you have to tie the new code to the specific errors per backend (mysql, mysqli, pdo, mysqlnd) to make it reusable or incorporate it in the backend wrapper to always return true/false.
    Last edited by jazzc; 12-10-2012 at 07:02 PM.

  19. #15
    madoctopus's Avatar
    madoctopus is offline Supreme Member
    Join Date
    Apr 2010
    Posts
    1,202
    Thanks
    1,018
    Thanked 3,099 Times in 933 Posts

    Default Re: Prevent Duplicate Entries

    @jazzc, that's true. also your solution is obviously the correct one. i just gave a solution done the hackish way as i'm used for my usual style of coding which is hackish since is just for me. not nice from me but i just didn't thought about the INSERT IGNORE etc. sometimes when you build tools just for yourself you get used to writing incorrect code that just works and that's all that matters.

  20. The Following User Says Thank You to madoctopus For This Useful Post:

    jazzc (12-10-2012)


Similar Threads

  1. How to prevent duplicate sites with ScrapeBox
    By grantunwin in forum Black Hat SEO Tools
    Replies: 4
    Last Post: 12-03-2011, 12:29 PM
  2. How to extract all the directory entries?
    By virtualc08 in forum Black Hat SEO
    Replies: 3
    Last Post: 10-27-2010, 05:56 AM
  3. Fake rss entries
    By maximart4u in forum Blogging
    Replies: 0
    Last Post: 03-13-2010, 03:15 PM
  4. ripoffreport, how to delete entries
    By chris_muc in forum Black Hat SEO
    Replies: 12
    Last Post: 09-30-2009, 04:28 PM
  5. Checking For Duplicate Entries In Mysql database
    By steves in forum General Programming Chat
    Replies: 4
    Last Post: 11-19-2008, 05:52 AM

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
  •  




BlackHatWorld on Twitter BlackHatWorld on FaceBook


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108