MySQL/PHP Boolean Search Updated

August 3, 2008

Topics: Web Development.

This is an old project, now obsolete. Links have been redacted.

Following the publication of a brief article on Search results design by Adaptive Path, I decided that revising my database search script was a valuable goal. Specifically, meeting the checklist in that result was probably not a bad idea!

It’s not that the previous version was terrible, but I knew perfectly well that it could be much better.

The additions to the script are pretty straightforward:

Additions:

  • Added: Made row highlighting available in both tabular and list-based search results.
  • Added: Search terms are now highlighted in search results.
  • Added: The default sort is now to order results by query relevance.
  • Added: Paginated navigation of search results is now available.
  • Added: Translation base file [English], so translating the script is easier.
  • Added: Basic Spellchecking [English]
  • Added: Default stylesheet

Changes:

  • Changed: Text excerpts are now truncated at word boundaries, rather than in the middle of words.
  • Changed: separated results template information into external include files for easier upgrading or modification.
  • Changed: Included the search form as part of the script so that search terms could be automatically returned to the search input.

The spell checking is the most exciting addition in my view. It’s hardly complete, but it’s based on a list of 4,068 common misspellings available from Wikipedia. This addition has significantly bulked up the total download size, since I’m including the spell-checking database as part of the download, but I think it adds a lot of value to the script.

I’ve also added a translation base file to the package, to make it a bit easier for users of the script to port it to their own languages. Unfortunately, I haven’t yet had time to seriously work on the internationalization of the search script itself, so (to be entirely frank) this is an area which the script isn’t really well suited at this time.

Internationalization is next on the list, however. It’s a high priority at this point, since internationalization ranks as one of the most reported problems with the script.

With spell-checking in mind, I think it’s appropriate to provide a healthy reminder of the limitations of spellcheck:

Candidate for a Pullet Surprise

by Mark Eckman and Jerrold H. Zar

I have a spelling checker,
It came with my PC.
It plane lee marks four my revue
Miss steaks aye can knot sea.

Eye ran this poem threw it,
Your sure reel glad two no.
Its vary polished in it’s weigh.
My checker tolled me sew.

A checker is a bless sing,
It freeze yew lodes of thyme.
It helps me right awl stiles two reed,
And aides me when eye rime.

Each frays come posed up on my screen
Eye trussed too bee a joule.
The checker pours o’er every word
To cheque sum spelling rule.

Bee fore a veiling checker’s
Hour spelling mite decline,
And if we’re lacks oar have a laps,
We wood bee maid too wine.

Butt now bee cause my spelling
Is checked with such grate flare,
Their are know fault’s with in my cite,
Of nun eye am a wear.

Now spelling does knot phase me,
It does knot bring a tier.
My pay purrs awl due glad den
With wrapped word’s fare as hear.

To rite with care is quite a feet
Of witch won should bee proud,
And wee mussed dew the best wee can,
Sew flaw’s are knot aloud.

Sow ewe can sea why aye dew prays
Such soft wear four pea seas,
And why eye brake in two averse
Buy righting want too pleas.

Have something to contribute?




« Read my Comment Policy

8 Comments to “MySQL/PHP Boolean Search Updated”

  1. The reason i have to do it is because to limit the db growth i have a location db with country and in the properies field i add just the location ID instead of the name…

    Which is very sensible! Glad that everything worked for you — I’ve never tested the script using joins in the query, but I couldn’t think of any reason it would be a problem.

  2. hi,
    I have to confess that i was a bit border yesterday, but today with a cup of cafe i did what i asked you.
    What i did was: in config.php i added an additional field for db_table, and
    on Search.php i just changed a bit the select string:
    AS relevance FROM $db_table LEFT JOIN $db_table2 on properties.location=locations.id WHERE…

    The reason i have to do it is because to limit the db growth i have a location db with country and in the properies field i add just the location ID instead of the name…

    So i wanted that the country name should be searched too..

    Thanks for your great job!

  3. That shouldn’t be any problem — the script is written based on a single table model mostly because I can’t possibly guess any other reasonable database design…adding some options to configure this kind of thing is in the plans, but hasn’t happened yet.

    Hard to wrap your head around how to plan for who-knows-how-many different possible database designs.

    Regardless, as long as you’re creating a valid database query and the resulting fields are defined consistently with your configuration file, you shouldn’t have any problems. You might end up with some complications concerning which fields on which tables you’re searching, of course — it’s entirely likely you’ll end up needing to toss the configuration variable and hard code the queries. Depends on what you need.

  4. hi!

    i need to do left join on my tables do do a search..
    is it a problem to change your script?

    $q = “select properties.id, properties.title, properties.city, locations.name FROM properties LEFT JOIN locations on properties.location=locations.id ” .
    “WHERE title LIKE ‘%”.$keywords[$i].”%'”.

  5. Well…simultaneous comment authorship!

    Thanks for stopping by, Chiara! It was one of those checklists which just spurred me to actually implement what I should have done the first time around.

  6. In fact, I was aware of that (courtesy of this article) — I just chose to use the “revised” version anyhow. I rather like the total absurdity of the extremism in the expanded poem. Your original version, however, does take the cake for elegance — the longer version becomes very difficult to scan due to the differences of meter and slant-wise homonyms.

    Thanks for stopping by!

  7. I’m so glad that you found the checklist helpful. I hope you find that your script is more usable now.

    And that is a great poem on the dangers of spell checkers. Ha!

  8. Actually, those first two stanzas are not the original, and I’d be happy to explain the origins of the verse.

    In 1992, I worked for AT&T and at the time we were proud of our proprietary e-mail. However, a debate raged over including or not including a spell check in the software. Lot’s of arguments were forwarded on the topic ranging from “customers that can’t spell shouldn’t use e-mail” to limitations for computers with 64k of RAM. The argument spilled over into the daily e-mail blurb sent to everyone. I sent these two verses, which were published the next day, ending the discussion. My intention was to subtly lower the arguments to absurdity :

    I have a spelling checker
    It came with my PC
    It highlights for my review
    Mistakes I cannot sea.

    I ran this poem thru it
    I’m sure your pleased to no
    Its letter perfect in it’s weigh
    My checker told me sew.

    A couple of years later I was doing a presentation on search engines and so began an urban ledgend.

    Mark Eckman