viktor
|
| Posted: 03/11/2005, 5:14 AM |
|
How can I use more then one word to search through a 'memo' or 'text' field?
I use contains (like '%..%') but in the end it is limited to ONE word only.
I've tried to setup two similar parameters with 'or' as well with 'and' in between them on the same column to be search - all in vain...
is there any trick?
I've read http://forums.codecharge.com/posts.php?post_id=27257 - useful - but I can not make it working.
Please advise,
Viktor
|
|
|
 |
mrachow
Posts: 509
|
| Posted: 03/11/2005, 5:35 AM |
|
Thats a matter of SQL.
If you say '%at home%'
it will look exactly for 'at home' somewehere in your text.
You would have to enter at%home into the search field to find
'at my home'
for example.
But both word have to be present.
_________________
Best regards,
Michael |
 |
|
Martin K.
|
| Posted: 03/11/2005, 11:35 AM |
|
Hello.
First, sorry about my english.
You can make a search for more than one Word like this.
You need 2 Funktions.
1. Funktion
Split the Searchwords from the URL and create from counting the split a new Where.
Example:
YOUR_ZELL_NAME means that hear are your Tablezellname comes.
##########################################
function CCGetMultiWord($value)
{
$Result = " ";
if(strlen($value) > 0) {
$string_to_split = $value;
$tok = split(" ", $string_to_split);
$size = sizeof($tok);
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result .= " YOUR_ZELL_NAME like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result .= " (YOUR_ZELL_NAME like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " or YOUR_ZELL_NAME like '%".$tok[$i]."%')";
else
$Result .= " or YOUR_ZELL_NAME like '%".$tok[$i]."%'";
}
}
return $Result;
}
##################################################
2. Funktion
Build the Where from the Grid New
$YOUR_FORM_NAME means the Name of your Form.
s_name means the stringname from the URL with the Searchwords.
##############################################
function My_MoreWhere_ds_BeforeBuildSelect() {
global $YOUR_FORM_NAME;
if ($YOUR_FORM_NAME->ds->Where <> "") {
$YOUR_FORM_NAME->ds->Where .= " AND ";
}
$YOUR_FORM_NAME->ds->Where .= CCGetMultiWord(CCGetFromGet("s_name", ""));
}
############################################
Then select Your Listing Grid and put this Custom Code in BeforeBuildSelect:
###############################
if(strlen(CCGetFromGet("s_name", "")))
My_MoreWhere_ds_BeforeBuildSelect();
#####################################
and put the 2 Funktions add the End from the EventsSide.
Now you can search for more than one Word. 
Greets Martin
|
|
|
|
viktor
|
| Posted: 03/12/2005, 12:05 PM |
|
Hello Martin!
Your advise sound good. Can you please clarify / confirm the follwing:
Where does it go?
1. SearchBox or SearchResultsGrid?
2. In the Events?
Which specifically : - Before Select, Before Show, Before Show Row, Before Build Select, Before Executre Select, After Execute Select?
3. YOUR_ZELL_NAME - what is this? Connections name or form name?
Quote Martin K.:
##########################################
function CCGetMultiWord($value)
{
$Result = " ";
if(strlen($value) > 0) {
$string_to_split = $value;
$tok = split(" ", $string_to_split);
$size = sizeof($tok);
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result .= " YOUR_ZELL_NAME like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result .= " (YOUR_ZELL_NAME like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " or YOUR_ZELL_NAME like '%".$tok[$i]."%')";
else
$Result .= " or YOUR_ZELL_NAME like '%".$tok[$i]."%'";
}
}
return $Result;
}
##################################################
Where does it go?
1. SearchBox or SearchResultsGrid?
2. Does it go to BeforeBuildSelect? Correct
Quote Martin K.:
2. Funktion
Build the Where from the Grid New
$YOUR_FORM_NAME means the Name of your Form.
s_name means the stringname from the URL with the Searchwords.
##############################################
function My_MoreWhere_ds_BeforeBuildSelect() {
global $YOUR_FORM_NAME;
if ($YOUR_FORM_NAME->ds->Where <> "") {
$YOUR_FORM_NAME->ds->Where .= " AND ";
}
$YOUR_FORM_NAME->ds->Where .= CCGetMultiWord(CCGetFromGet("s_name", ""));
}
############################################
And then this goes to BeforeBuildSelect to SearchResultsGrid. Correct?
Quote Martin K.:
###############################
if(strlen(CCGetFromGet("s_name", "")))
My_MoreWhere_ds_BeforeBuildSelect();
#####################################
|
|
|
|
viktor
|
| Posted: 03/12/2005, 12:18 PM |
|
Also - who knows how the search for this forum works?
Has it been done in a similar way?
|
|
|
|
Martin K.
|
| Posted: 03/12/2005, 12:48 PM |
|
Hello Victor.
The only Code that you bring in your SearchResultsGrid is in BeforeBuildSelect:
###############################
if(strlen(CCGetFromGet("s_name", ""))) // if there is a search Word?
My_MoreWhere_ds_BeforeBuildSelect(); // 2. Funktion Go
#####################################
Then you can put the 2 Funktions at the end from the EventsSide where you bring in just the 2 Lines Code. Just before the ?> Or in Your Common.php
3. YOUR_ZELL_NAME - what is this? Connections name or form name?
No.
YOUR_ZELL_NAME means that there comes your Table-Zell-Name for the Searchword you will look for.
The 1. Funktion.
Example:
You will search for Peter Paul Mary
The 1. Funktion will split the Words and make the SQL for searching for every Word.
Return from the 1. Funktion if you search for Peter Paul Mary
#############################################
AND (YOUR_ZELL_NAME LIKE '%Peter%' OR YOUR_ZELL_NAME LIKE '%Paul%' OR YOUR_ZELL_NAME LIKE '%Mary%');
############################################
The First AND comes from the 2. Funkion.
Now i hope you understand what YOUR_ZELL_NAME is.
The Table Zell Field Name of you Connection where do you look for the search Words.
The 2. Funktion open the 1. Funktion and if there are searchwords in the url the 2. Funktion add the SQL from the 1. Funktion to the Where from the Grid.
My english is not so well and i hope you understand me.
greets martin
|
|
|
|
viktor
|
| Posted: 03/14/2005, 4:00 AM |
|
Many thanks Martin!
It worked! I did not expect myself to be able to make it form the first try.
Last question.
I've tried to make search searching through more then one field.
And I have done the following - but it would not work.
The first field ("YOUR_ZELL_NAME") is "para_text" and the second "para_news".
I can now do to make search through one field. How do I do it for more then one field?
Thanks!
##########################################
function CCGetMultiWord($value)
{
$Result = " ";
if(strlen($value) > 0) {
$string_to_split = $value;
$tok = split(" ", $string_to_split);
$size = sizeof($tok);
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result .= " para_text like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result .= " (para_text like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " or para_text like '%".$tok[$i]."%')";
//elseif (($i == 0) && ($i == $size-1))
//$Result .= " para_news like '%".$tok[$i]."%'";
//elseif ($i == 0)
//$Result .= " (para_news like '%".$tok[$i]."%'";
//elseif ($i == $size-1)
//$Result .= " or para_news like '%".$tok[$i]."%')";
//elseif
//$Result .= " or para_news like '%".$tok[$i]."%'";
else
$Result .= " or para_text like '%".$tok[$i]."%'";
}
}
return $Result;
}
##################################################
|
|
|
|
Martin K.
|
| Posted: 03/14/2005, 6:02 AM |
|
Hello Victor.
Nice to hear thats working.
hear are the Funktion for 2 Searchfields:
##########################################
function CCGetMultiWord($value)
{
$Result = " ";
if(strlen($value) > 0) {
$string_to_split = $value;
$tok = split(" ", $string_to_split);
$size = sizeof($tok);
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result = " AND para_text like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result = " AND (para_text like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " OR para_text like '%".$tok[$i]."%')";
else
$Result .= " OR para_text like '%".$tok[$i]."%'";
}
// Second Field + AND or OR
$Result .= " OR";
// Second Field to search Where begin
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result = " AND para_news like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result = " AND (para_news like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " OR para_news like '%".$tok[$i]."%')";
else
$Result .= " OR para_news like '%".$tok[$i]."%'";
}
}
return $Result;
}
##################################################
greets martin
|
|
|
|
Martin K.
|
| Posted: 03/14/2005, 6:14 AM |
|
Thats better. Little Mistake before
##########################################
function CCGetMultiWord($value)
{
$Result = " ";
if(strlen($value) > 0) {
$string_to_split = $value;
$tok = split(" ", $string_to_split);
$size = sizeof($tok);
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result = " para_text like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result = " (para_text like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " OR para_text like '%".$tok[$i]."%')";
else
$Result .= " OR para_text like '%".$tok[$i]."%'";
}
// Second Field + AND or OR
$Result .= " OR";
// Second Field to search Where begin
for ($i = 0; $i< $size; $i++)
{
if (($i == 0) && ($i == $size-1))
$Result = " para_news like '%".$tok[$i]."%'";
elseif ($i == 0)
$Result = " (para_news like '%".$tok[$i]."%'";
elseif ($i == $size-1)
$Result .= " OR para_news like '%".$tok[$i]."%')";
else
$Result .= " OR para_news like '%".$tok[$i]."%'";
}
}
return $Result;
}
##################################################
greets martin
|
|
|
|
viktor
|
| Posted: 03/15/2005, 3:20 AM |
|
Martin!
Many THANK!
It works!
This Tip / Advise can easily be included into Official Help for CCS!
Thank you again, Viktor
|
|
|
|
