[SOLVED] Loging Search Terms In Mysql?

[Deanznet]

Hey i have a search form on my site that searches a table in Mysql. i created another tabled called query_log with 2 columns query and results.

 

How can i make it so when someone searches it inserts their keyword into query and then inserts how many times it was search for into results.

 

so if someone searches php it inserts php then 1 into the query and results. if someone else searches php it inserts 2 into results.

 

Anyone?

[BlueSkyIS]

i would first see if the search term has been searched for before. if so, get the record id and update that record setting count = count + 1. if not, insert the search term with a count of 1.

[Mchl]

INSERT INTO query_log (query, results) VALUES ('$query',1) ON DUPLICATE KEY UPDATE SET results = results + 1

 

That's assuming there's a UNIQUE index on query field.

[BlueSkyIS]

sweet! i didn't know that "ON DUPLICATE KEY UPDATE SET" existed! thanks for that!

 

 

[Deanznet]

Thanks! But im still having a problem.

 

This works.

 

$querylog = "INSERT INTO query_log (query,results) VALUES ('$search',1)"; 
mysql_query($querylog) or die('Error, insert query failed');

 

But if i do

 

$querylog = "INSERT INTO query_log (query,results) VALUES ('$search',1) ON DUPLICATE KEY UPDATE SET results = results + 1)"; 
mysql_query($querylog) or die('Error, insert query failed');

 

I made my query column primary index unquie was that right?

It wont work, i get the insert query failed message.

[Deanznet]

Bump.

[Mchl]

$querylog = "INSERT INTO query_log (query,results) VALUES ('$search',1) ON DUPLICATE KEY UPDATE SET results = results + 1)"; 
mysql_query($querylog) or die('Error, insert query failed: '.mysql_error());

[Deanznet]

Thanks i got it now!

[Mchl]

What was it?