Redirect Search Engine Traffic to your Search
I had a little problem on Pro-Football News where I don't really have a display for my aggregate data. Google and the other search engines would still crawl and show results, but the results were just a snap shot in time. Since the content rotates as new articles are aggregated, I needed someway to add just a little stickiness for the users coming for specific search details. I decided to add a little MySQL magic and create context sensitive searches via Xaraya.
This technique is not limited to Xaraya, but rather can be used on any page that has some sort of search engine. It's not revolutionary, but since I had to do a hack job from multiple sources, I figured I would write up a cursory tutorial. Very little of the code is mine, but rather it is a compilation of multiple sources doing similar ideas. I have borrowed from I Love Jack Daniels :: Google Style Keyword Highlight and the MySQL manual :: Full Text Searches.
The first issue that I faced was creating a full text search. One of the gotcha's for this is, you need to index your columns that will be searched. This index can be created via a script or phpMyAdmin, and it needs to be FULLTEXT. MySQL will return an error if you do match on a non-fulltext indexed column. Xaraya uses ADODB currently, and the proper syntax for the index can be found in the manual. Please note that the FULLTEXT index is MySQL only, according to the docs.
Once you have the index created. it is time to create the query. I have always used "LIKE" for searches, but this gets us a little closer to an actual context relevance search. My query looks something like this:
A couple of things to notice. First, I normally use bindvars to protect against SQL Insertion, but it appears that there is a bug with ADODB. Instead, I sanitized the variable $q prior to the query in this function. The WHERE clause is where the magic happens.
Basically, we are searching our two indexed columns from before (xar_title, xar_summary) against our text that we are searching on (more on that later). This gives us a return of the 5 most relevant results (see the SelectLimit function call).
Our first step is completed, now it is time to build the search terms based on what the search engines send us. For this, I have modified the script from I Love Jack Daniels to shoot a string to be searched upon. Here is the modification of that script:
What this does is compile the keywords from any given search from multiple search engines and throws it into an array ($keywords). If the referer was not from a search engine, then I simply return; back to my main function. Once I have my $keywords array built, then it is just a little sanitizing into the $keyword_array. I then use an array_unique call to remove duplicate keywords, and implode it to create my string for searching. This then sends a GET to my search function for further processing.
To take this just a bit further, I have created a "Similar Article" function which takes the title of any given article and extracts just the words longer than 4 characters. I then shove that to my search function again, and return the same amount of results.
Easy Peasy. For applications other than Xaraya, the modifications should be rather easy to make. There are two Xaraya functions calls in this example (xarResponseRedirect and xarModURL) but those simply build the url and redirects the php header. You could simulate these functions in minutes, should it be necessary.
Happy Hacking....