Tracking what people search for?

So I have a search box on my site that I coded in PHP (It's not great, but it works okay for what it has to do), and i'd like to somehow modify it to monitor what people search for.

My question is, how do I do this?

I can post the code that I have used for the search if that helps in any way.

Thanks,
TS

 

Using a database? Just grab the query term and file it. Post the code. I'm going to write similiar code here in a few minutes so might as well help.

 

If your using something like:

$searching_for = $_POST['query'];

PHP:

then $searching_for is to be stored somewhere
if you are putting that into mysql of course do not forget to use:

mysql_real_escape_string($_POST['query']);

PHP:

or even do more check on that.

 

Also should check to see if the exact term was used in a prior search. So some method to allow for incrementing terms or push this off as a collation report. Other information that could be useful would be IP, timestamp and language(if used) ....

 

The code looks like this:

// Get search term from url
            if(isset($_GET['search']))
			{
				$search = $_GET['search'];
			}
			
			//trim whitespace from variable
			$search = trim($search);
			$search = preg_replace('/\s+/', ' ', $search);	
            
            // Strip harmful tags
            $search = strip_tags($search);
            
            // Escape search, prevents mysql injection
            $search = mysql_real_escape_string($search);
            
            //seperate multiple keywords into array space delimited
            $keywords = explode(" ", $search);
            
            //Clean empty arrays so they don’t get every row as result
            $keywords = array_diff($keywords, array(""));
            
            // If search term empty then do nothing
            if ($search == NULL or $search == '%'){
			} else {
                for ($i=0; $i<count($keywords); $i++) {
					$query = "SELECT * FROM layouts " .
					"WHERE name LIKE '%".$keywords[$i]."%'".
					#" OR desc LIKE '%".$keywords[$i]."%'" .
                    " ORDER BY id";
                }
                        
                //Store the results in a variable or die if query fails
                $result = mysql_query($query) or die(mysql_error());
            }
            
            // Get number of articles, assign value to a variable
            $count = mysql_num_rows($result);
            
            //If search variable is null do nothing, else print it.
            if ($search == "") {
            } else {
                echo '<h1>Search Results</h2>';
                echo '<p>Your search for <span class="bold">' . $search . '</span> found ' . $count . ' matches.</p>';
            }
            //If users doesn’t enter anything into search box tell them to.
            if ($search == NULL){
                echo "<p>You forgot to enter a search term</p>";
            } elseif ($search == '%'){
                echo "<p>You forgot to enter a search term</p>";
            //If no results are returned print it
            } 
			elseif ($count <= 0){
                echo "<p>Your search term produced no results, please try again with another search term.</p>";
            //ELSE print the data
            } else {
                //While there are rows, print it.
                while($row = mysql_fetch_array($result))
                {
					if ($row['published'] = 'yes')
					{
						echo '<h2 class="title"><a href="http://www.freedivs.com/div-layout/' . $row['name'] . '/" title="' . $row['name'] . ' div layout code">' . $row['name'] . ' Div Layout</h2>';
						echo '<p><a href="http://www.freedivs.com/div-layout/' . $row['name'] . '/" title="' . $row['name'] . ' div layout code"><img src="http://www.freedivs.com/layout_images/' . $row['picture'] . '" alt="' . $row['name'] . ' Myspace div layout" width="200" /></a> <span class="bold">Description:</span> ' . $row['desc'] . '</p>';
						echo '<hr />';
					}
                    //end while
                }
            //end if
            }

PHP:

How would I file it? Into another table in the database that goes something like (id, term, times_searched)?

 

You would just make another table with the headers ID, KEYWORD, HITS. Then each time you execute that script, send a query to create or increase the number of hits per each keyword used by 1. It's quite easy
~Jeremy~

 

I would include the article count in the table results. It appears you are searching for each search term on its own. If that's correct, you could place it right after the query is executed and just create a table search_results_tracker or some such with id,term,hits like Jeremy suggested or do id,term,timestamp,results_total if you are planning on improving the search results. Latter case it would have to be after the count is defined.

 

Thanks guys i'm sure I can do this now. Then i'll just make a page to output the results

How would putting a timestamp and the total results into the database help? Just out of interest.

 

Well timestamp would let you look at the searches made by day or month. Total results could tell you what people are looking for and not finding. Course I was just going over what I'm thinking about including with a search function I'm building. My needs aren't the same as yours so its really up to you.

 

Ah I see I'm glad you explained those, I might put them in, depends what I feel like (i.e. how hard I find it, sounds pretty simple though, so i'll probably do it)

Thanks again,
TS

 

I got something like this on my site

$search = $_GET['search'];
$date= date(DATE_RFC822);
$ip_addy = $_SERVER['REMOTE_ADDR'];
$sql_query = "INSERT into table ( visit, phrase, ip ) VALUES ( '$date' , '$search' , '$ip_addy' )";

Code (markup):