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

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.

Originally Posted by qrazy

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!

Originally Posted by qrazy

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.

Originally Posted by Green Arrow

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.

Originally Posted by jazzc

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.

Originally Posted by qrazy

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.

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?

Originally Posted by Green Arrow

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.

Originally Posted by Green Arrow

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

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.

Originally Posted by madoctopus

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 ...

Originally Posted by jazzc

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.

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

Originally Posted by madoctopus

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.

@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.