MySQL Search Query Library v0.1

[eluser]trice22[/eluser]
Hello,

I wrote a library which produces MySQL-queries like you need them for a search field, since I needed it for a project and I thought someone might maybe have use for it and/or you could atleast have a look at it (this is the first actual class I wrote - so please have patience).

WHAT DOES IT DO?
Actually not much: It takes a search string (e.g. from an input field) and forms a MySQL-query for you, using "LIKE".

WHY WOULD I NEED IT THEN?
Well, it does a few things for you, that might come handy - it:
- splits the search-string, but keeps expressions between double-quotes intact
- removes a list of too common symbols (except from the expressions)
- removes stopwords (words, that are too common) depending on a external stopwords file
(so far I've includes stopwords for English, German, French and Finnish) - not the expressions
- reduces words to it's stem (using the Porter Stemmer algorithm - so far only for English) - and again leaves the expressions untouched
- will allow the usage of the most basic BOOLEANS like "AND or +", "NOT or -" and "OR"
- furthermore you have the possibility to set a few options, often used in "advances serach modi" - like "search for ALL words"


HOW TO USE IT?
Download the zip and extract it into your library folder.
Then put into your Model (or Controller):

Code:

$this->load->library('Emq');
// Set config-variables - details below:
$config = array(
    "table" => "rt_articles",
    "returnColumns" => "id"
);
$this->emq->setConfig($config);        
// get query according to your $searchstring        
$myquery = $this->emq->find($searchstring);

You'll have to set atleast the variables "table" and "returnColumns", here's the full list:

Code:

$returnColumns;     // What comes back
$table;             // From which table
$searchColumns;     // Where to look
$groupByColumn;     // GROUP BY
$orderByColumn;     // ORDER BY
$order;             // DESC or ASC
$limit;             // LIMIT - "10" becomes "0, 10"
$stemmer;           // use Porter Stemmer algorithm (true or false)
$lang;              // for stopwords ("en","de","fr",fi")
$stopWords;         // Has to contain all words
$searchAll;         // Search for all words (true or false)
//------------------------
// EXAMPLE:
$config = array(
    "returnColumns"  = "id",
    "table"          = "blog_articles",        
    "searchColumns"  = "title, subtitle, text",    
    "groupByColumn"  = "title",            
    "orderByColumn"  = "date",            
    "order"          = "DESC",            
    "limit"          = "0, 30",            
    "stemmer"        = true,                
    "lang"           = "en",                
    "searchAll"      = false                
);

You can also set every option separately:

Code:

$this->emq->setReturnColumns("returnColumns");
$this->emq->setTable("blog_articles");
$this->emq->setSearchColumns("title, subtitle, text");
$this->emq->setGroupBy("title");
$this->emq->setOrderBy("date");
$this->emq->setOrder("DESC");
$this->emq->setLimit("0,30");
$this->emq->setStemmer(true);
$this->emq->setLanguage("en");
$this->emq->searchAll();

Furthermore:

Code:

$this->emq->disableStemmer(); // will disable the Porter Stemmer Algorithm
$myStopWords = array("word1","word2");
$this->emq->appStopWords($myStopWords); // extends the stopwords list

WHAT IT DOESN'T DO:
- No security features are integrated - CI comes with great security helpers (like xss-cleaning etc.) - just use them before the EMQ.

EXAMPLE 1:

Code:

// The search string
$searchstring = 'I never had "sex with this" NOT woman';

// will produce this MySQL-query:
SELECT id
FROM articles
WHERE (
(
title LIKE '&#xse;x with this%'
AND title NOT LIKE '&#xma;n%'
)
OR (
text LIKE '&#xse;x with this%'
AND text NOT LIKE '&#xwo;man%'
)
OR (
tags LIKE '&#xse;x with this%'
AND tags NOT LIKE '&#xwo;man%'
)
)
GROUP BY column2
ORDER BY column1 ASC
LIMIT 0 , 10

EXAMPLE 2:

Code:

// The search string
$searchstring = 'birds -bees';

// will produce this MySQL-query:
SELECT id
FROM articles
WHERE (
(
title LIKE 'i;rd%'
AND title NOT LIKE '¾e%'
)
OR (
text LIKE 'i;rd%'
AND text NOT LIKE '¾e%'
)
OR (
tags LIKE 'i;rd%'
AND tags NOT LIKE '¾e%'
)
)
GROUP BY column2
ORDER BY column1 ASC
LIMIT 0 , 10

WHAT I WOULD BE REALLY GRATEFUL FOR:
It's the first version, man! Go - find a bug, there gotta be a lot ;D .
I don't consider myself to be a programmer nor a very logical thinking person.
I write the code I need (if I can) but I usually have to realize later, that there'd be 10.000 better and easier ways.
If you've some time, please read through it and if you've any advice about structure and solutions but also coding-style and possibilities to simplify - please let me know.

Otherwise, please be my guest and test it, if you have some use for it.

THANKS:
This class is inspired by the search-class of Stephen Bartholomew
-> (http://aspn.activestate.com/ASPN/Cookboo...ipe/125901)
and the PHP search engine from roscripts.com
-> (http://www.roscripts.com/PHP_search_engine-119.html)
It uses the Porter Stemmer algorithm for English by Jon Abernathy <[email protected]>

Cheers,
trice

//EDIT: Apparently I can't submit a query like "% man %" (without the spaces - I guess you know how the MySQL-query would look like anyways.