CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Improved Search

Print topic Send  topic

Author Message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.