Damian Hupfeld
|
Posted: 04/12/2006, 12:07 AM |
|
For MySQL
Searches all words not the whole string, and orders by the result with the
most number of search words
In Results Grid change Data Source to SQL and modify the query below to
match your data
SELECT *, Match field against ('%{s_text}%' in boolean mode) AS Score
FROM table
WHERE Match field against ('%{s_text}%' in boolean mode)
ORDER BY Score desc, mydate desc
You can search multiple fields by replacing
field
with
(field1, field2, field3)
regards
Damian Hupfeld http://www.nexthost.com.au/services.php
|
|
|
Damian Hupfeld
|
Posted: 04/12/2006, 8:17 PM |
|
Ummm, improved upon again after discoverring some limitations.... Thanks to
DonB and Walter.
Solution -
Create Grid.
Change Data Source Type to SQL.
Set Data Source to something valud such as
select * from news
Then add Custom Code in Before Execute Select such as:
//global $newssearch;
$newssearch = CCGetParam('s_newstext','');
if (CCGetParam('s_newstext','') == "") {
$newstring1 = "select * from news ";
}
else {
$newstring1 = "select *, Match newstext against ('".$newssearch."' in
boolean mode) AS Score FROM news ";
}
//echo $newstring1;
if (CCGetParam('s_newstext','') == "") {
$newstring2 = "";
}
else {
$newstring2 = "Match newstext against ('".$newssearch."' in boolean mode)";
}
//echo $newstring2;
if (CCGetParam('s_newstext','') == "") {
$newstring3 = "newsdate desc";
}
else {
$newstring3 = "Score desc, newsdate desc";
}
//echo $newstring3;
$news1->DataSource->SQL = "$newstring1";
$news1->DataSource->Where = "$newstring2";
$news1->DataSource->Order = "$newstring3";
//echo $newssearch;
//$news1->DataSource->Debug=true;
This checks if anything has been searched on - if not it sets the base
search parameters of
select * from news order by date desc
If there are search terms it uses the following
SELECT *,
Match newstext against ('searchterms' in boolean mode) AS Score
FROM news
WHERE Match newstext against ('searchterms' in boolean mode)
ORDER BY Score desc, newsdate desc
Which also returns results with the most matches first :)
"Damian Hupfeld" <damian.hupfeld@itng.com.au> wrote in message
news:e1i8v0$6gu$1@news.codecharge.com...
> For MySQL
>
> Searches all words not the whole string, and orders by the result with the
> most number of search words
>
> In Results Grid change Data Source to SQL and modify the query below to
> match your data
>
>
> SELECT *, Match field against ('%{s_text}%' in boolean mode) AS Score
> FROM table
> WHERE Match field against ('%{s_text}%' in boolean mode)
> ORDER BY Score desc, mydate desc
>
>
> You can search multiple fields by replacing
> field
> with
> (field1, field2, field3)
>
> regards
> Damian Hupfeld
> http://www.nexthost.com.au/services.php
>
>
>
>
|
|
|
feha
Posts: 712
|
Posted: 04/18/2006, 7:55 AM |
|
Very helpful, thanks
_________________
Regards
feha
www.vision.to
feedpixel.com |
|
|
|