feha
Posts: 712
|
| Posted: 11/06/2008, 8:52 AM |
|
I really like this new feature, it saves lot of time (no more custom search code) 
I do have a problem to search when i join more tables ...
SELECT login_logger.*, concat_ws(" ",first_name,last_name) AS first_last_name, user_groups.name AS group_name, languages.name AS language,
printable_name AS country
FROM (((login_logger INNER JOIN users ON
login_logger.user_id = users.user_id) INNER JOIN languages ON
login_logger.lang = languages.lang) INNER JOIN user_groups ON
login_logger.group_id = user_groups.group_id) INNER JOIN countries ON
login_logger.country = countries.id
ORDER BY login_logger.login_time desc
If i add a field for example "first_last_name" in to s_keyword search options (Action before build select in record) i do get error ...
I wonder if is possible at all to use this advanced search for this kind of query ?!
Anyone has similar problem, any ideas ?
Thanks
_________________
Regards
feha
www.vision.to
feedpixel.com |
 |
 |
melvyn
Posts: 333
|
| Posted: 11/06/2008, 9:18 PM |
|
Let's see:
1) You told you get an error... Which error do you get? (
2) The error occur when you put something into s_keyword... well, I don't see a "WHERE" clause in your query.
Please clarify that.
By the other way, I'm not sure if you can search into a field generated by concat_ws(), if can, will be slow, very slow. The where condition in the s_keyword must be "LIKE" and must match exactly anyways. It can be difficult to get it working.
It's interesting to test this, I like it, will do my own test in order to check your technique.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
feha
Posts: 712
|
| Posted: 11/07/2008, 12:27 AM |
|
Hi melvyn
This query works to display data.
The "Where" builds later on in before select event., example:
//Advanced Search @14-C3AE92B6
global $login_loggerSearch;
$s_keyword = CCGetParam("s_keyword", "");
$searchConditions = CCGetParam("searchConditions", "");
if (!in_array($searchConditions, array("1", "2", "3"))) $searchConditions = 1;
$keywords = split(" ", trim($s_keyword));
if (strlen($s_keyword)) {
$f_ip = "";
$f_host = "";
$f_country = "";
$f_first_last_name = "";
// Any of words
if ($searchConditions == "1") {
foreach ($keywords as $keyword) {
$keyword = str_replace("'", "''", trim($keyword));
if (strlen($f_ip)) $f_ip .= " OR ";
if (strlen($f_host)) $f_host .= " OR ";
if (strlen($f_country)) $f_country .= " OR ";
if (strlen($f_first_last_name)) $f_first_last_name .= " OR ";
$f_ip .= "ip LIKE '%" . $keyword . "%'";
$f_host .= "host LIKE '%" . $keyword . "%'";
$f_country .= "country LIKE '%" . $keyword . "%'";
$f_first_last_name .= "first_last_name LIKE '%" . $keyword . "%'";
}
// All words
} else if ($searchConditions == "2") {
foreach ($keywords as $keyword) {
$keyword = str_replace("'", "''", trim($keyword));
if (strlen($f_ip)) $f_ip .= " AND ";
if (strlen($f_host)) $f_host .= " AND ";
if (strlen($f_country)) $f_country .= " AND ";
if (strlen($f_first_last_name)) $f_first_last_name .= " AND ";
$f_ip .= "ip LIKE '%" . $keyword . "%'";
$f_host .= "host LIKE '%" . $keyword . "%'";
$f_country .= "country LIKE '%" . $keyword . "%'";
$f_first_last_name .= "first_last_name LIKE '%" . $keyword . "%'";
}
// Exact Phrase
} else if ($searchConditions == "3") {
$keyword = str_replace("'", "''", $s_keyword);
$f_ip = "ip LIKE '%" . $keyword . "%'";
$f_host = "host LIKE '%" . $keyword . "%'";
$f_country = "country LIKE '%" . $keyword . "%'";
$f_first_last_name = "first_last_name LIKE '%" . $keyword . "%'";
}
if (strlen($Container->DataSource->Where) > 0 ) $Container->DataSource->Where .= " AND ";
$Container->DataSource->Where .= "((" . $f_ip . ")";
if (strlen($f_host))
$Container->DataSource->Where .= " OR (". $f_host .")";
if (strlen($f_country))
$Container->DataSource->Where .= " OR (". $f_country .")";
if (strlen($f_first_last_name))
$Container->DataSource->Where .= " OR (". $f_first_last_name .")";
$Container->DataSource->Where .= " ) ";
} else {
$login_loggerSearch->s_keyword->SetValue("");
}
//End Advanced Search
The code is generated by action ...
I do not want to write a custom code, I want to see if it possible to do it with CCS action ...
Or at least to add some "Where .=" to this query ...
_________________
Regards
feha
www.vision.to
feedpixel.com |
|
|
|
melvyn
Posts: 333
|
| Posted: 11/07/2008, 7:29 AM |
|
Now I see. You did your homework.
Sorry, I was guessing you were trying to get a magical result from s_keyword. Now I see you did a hard job, very developed and meticulous.
Maybe the error above came from some of those simple things that get programmers crazy.
My only recommendation now is "Get enough coffee!"
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 11/07/2008, 9:25 AM |
|
Hi feha,
I suspect that the Advanced Search is not supposed to work with the SQL DataSource. If your DataSource type is set to Table then I'd recommend contacting product support - they can check if this is a special case or something should be fixed permanently.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
feha
Posts: 712
|
| Posted: 11/08/2008, 3:21 PM |
|
Hi Peter
It is Grid and data source is set to Table
For viewing data there is no problem ...
I will solve this problem, but I'm not sure it will work with concat_ws ...
I want to make it work with concat_ws else i do have solution per filed ...
Thank You
If I manage to fix it with concat_ws i will let you know ...
_________________
Regards
feha
www.vision.to
feedpixel.com |
|
|
|
maxhugen
Posts: 272
|
| Posted: 11/08/2008, 5:42 PM |
|
Hi feha
I did something a bit similar... in my case I created a fulltext index of multiple fields (ISAM tables only, though) to search on one or multiple keywords, plus search individual fields for "like" condition.
Just thinking that the fulltext index might be more useful for your address fields, rather than using concat_ws?
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
feha
Posts: 712
|
| Posted: 11/09/2008, 1:36 AM |
|
Hi maxugen
Thanks, I have done long time ago a custom coded text search:
http://www.vision.to/search.php
But I wanted to make it without custom coding and new built in feature of CCS by using just Action on BeforeSelect event.
If I add to the "Action" filed
.. countries.country, users.first_name,users.last_name etc ...
it searches O.K.
And I'm impressed with this new search feature in CCS.
_________________
Regards
feha
www.vision.to
feedpixel.com |
|
|
|
|
