1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Prevent Duplicate Entries

Discussion in 'PHP & Perl' started by Green Arrow, Dec 10, 2012.

  1. Green Arrow

    Green Arrow Newbie

    Joined:
    Jul 22, 2009
    Messages:
    18
    Likes Received:
    4
    I have a problem, as you probably understand from the topic title :p 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:
    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 :crazy:
     
  2. qrazy

    qrazy Senior Member

    Joined:
    Mar 19, 2012
    Messages:
    1,103
    Likes Received:
    1,679
    Location:
    Banana Republic
    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.
     
    • Thanks Thanks x 1
  3. Green Arrow

    Green Arrow Newbie

    Joined:
    Jul 22, 2009
    Messages:
    18
    Likes Received:
    4
    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:
    <?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: Dec 10, 2012
  4. jazzc

    jazzc Moderator Staff Member Moderator Jr. VIP

    Joined:
    Jan 27, 2009
    Messages:
    2,426
    Likes Received:
    9,869
    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.
     
    • Thanks Thanks x 1
  5. qrazy

    qrazy Senior Member

    Joined:
    Mar 19, 2012
    Messages:
    1,103
    Likes Received:
    1,679
    Location:
    Banana Republic
    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.

    This isn't polluting. You should never ignore any data after all this will be helpful for analytic purpose or the other.
     
  6. Green Arrow

    Green Arrow Newbie

    Joined:
    Jul 22, 2009
    Messages:
    18
    Likes Received:
    4
    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.
     
  7. Green Arrow

    Green Arrow Newbie

    Joined:
    Jul 22, 2009
    Messages:
    18
    Likes Received:
    4
    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?
     
  8. qrazy

    qrazy Senior Member

    Joined:
    Mar 19, 2012
    Messages:
    1,103
    Likes Received:
    1,679
    Location:
    Banana Republic
    Use a WHERE clause with LENGTH(FIELD_NAME) function to refine the query based on the number of character.
     
  9. jazzc

    jazzc Moderator Staff Member Moderator Jr. VIP

    Joined:
    Jan 27, 2009
    Messages:
    2,426
    Likes Received:
    9,869
    WHERE CHAR_LENGTH(str) < 5

    LENGTH() returns bytes so in a 2-byte encoding you won't get the right result ;)
     
    • Thanks Thanks x 1
  10. madoctopus

    madoctopus Supreme Member

    Joined:
    Apr 4, 2010
    Messages:
    1,247
    Likes Received:
    3,495
    Occupation:
    Full time IM
    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: Dec 10, 2012
  11. jazzc

    jazzc Moderator Staff Member Moderator Jr. VIP

    Joined:
    Jan 27, 2009
    Messages:
    2,426
    Likes Received:
    9,869
    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: Dec 10, 2012
  12. madoctopus

    madoctopus Supreme Member

    Joined:
    Apr 4, 2010
    Messages:
    1,247
    Likes Received:
    3,495
    Occupation:
    Full time IM
    that works too. But you DO know what the error is. There's a specific error code for index collision.
     
    • Thanks Thanks x 1
  13. Green Arrow

    Green Arrow Newbie

    Joined:
    Jul 22, 2009
    Messages:
    18
    Likes Received:
    4
    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
     
  14. jazzc

    jazzc Moderator Staff Member Moderator Jr. VIP

    Joined:
    Jan 27, 2009
    Messages:
    2,426
    Likes Received:
    9,869
    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: Dec 10, 2012
  15. madoctopus

    madoctopus Supreme Member

    Joined:
    Apr 4, 2010
    Messages:
    1,247
    Likes Received:
    3,495
    Occupation:
    Full time IM
    @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.
     
    • Thanks Thanks x 1