Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

search-ni.cgi search in this cat? (Links SQL 1.13)

Quote Reply
search-ni.cgi search in this cat? (Links SQL 1.13)
From Announcements:

>There is also one new feature: search only in this category feature.
>You can get this new feature by just replacing your search.cgi, Search.pm and DBSQL.pm file.

I have not try it, how about search-ni.cgi? Can search-ni.cgi also search only in this category?




Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Hi,

No, it does not work. But it shouldn't be big deal for you to change it.

1. Insert the part of search.cgi where the list of CatID's is built.

2. change the part where the "Where" clause is built to accept also a list of entrys. Analog to the changes in search.pm (http://www.gossamer-threads.com/...w=collapsed&sb=5

[ If there a ',' in the field make "and ( Row in ( List))
else make "and ( Row LIKE '%Value%') ]

regards, alexander


Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
I've upgraded the files search.cgi, Search.pm, and DBSQL.pm, but I'm not sure how to make it search on a specific field. Does someone know how it would look? I tried:

http://www.domain.com/links/search.cgi?field=x

But this didn't seem to work correctly. Any help would be much appreciated.

-jwm

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Oops...

I just realized that the new feature added was search only in this category. I thought it was a way to search on specific fields. Any hope of that becoming available soon?

-jwm

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Well one nice effect of the update is that you now can search on specific fields via search.cgi.

But not with the query string! You need to add to your search template a textfield with the exact name of the field in the Links Table. (it does not need to be weighted)

e.g. Links Table:

ID..................Weight 0
Title...............Weight 3
Description.........Weight 2
URL.................Weight 0
Country.............Weight 0

Now you want to search the Links for "Webhosting" in Canada

the search template has to call search.cgi with following params:

.../search.cgi?query=webhosting&Country=Canada..
(and of course the rest ww, bool, substring)

In former versions the Country=Canada had to match exactly the requested Field. In version 1.13 (I know it cause they use my code) Country=anada, Country=Cana will also return the Links with County=Canada.

Even numbers or "/" in URLS will be found. But searches with a number range, <number or >number will not work without modifying Search.pm.

regards, alexander

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Hi Alex, i have tried to modify the thing for searching a range (there is a long thread about somewhere in the forum), but i fail. Do you have an answer how to do?

So i would like to search: wehosting in canada, but with prices between 5 and 10$ for example.

Robert

BTW: I have read somewhere i only have to change the search.cgi for having search in cat only?
Cause i have to manually compare MY files with the 1.13; it is very important (and lot of work more) to do this only with search.cgi and/or with search.pm and dbsql.

Hope to hear from you.

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Hi Robert,

In Reply To:
: I have read somewhere i only have to change the search.cgi for having search in cat only?
No. You need to replace Search.cgi, DBSQL.pm and Search.pm, DB_utils.pm

As for your modded old files.

DBSQL.pm:sub query, sub query_indexed
Search.cgi: whole file
Search.pm: sub query
DB_Utils.pm: sub get_category_id_list is new

these are the files changed ( don't nail me down on that)

In Reply To:
search: wehosting in canada, but with prices between 5 and 10$
To achieve this you need to modify the filterbuilding routines of sub query in Search.pm. At the moment it builds filters either " AND Row LIKE '%search%' " or " AND Row in (list) ".

It is hard to post a general solution for that. If you contact me by mail maybe I can help you.

regards, alexander


Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Hi Robert,

To do this, you are probably better off not using the the search index, but rather DBSQL. i.e.

my $results = $db->query ( price-lt => '10', price-gt => '5', country => 'Canada', mh => 25 );

would do the search you want. This means you lose the ability to do +word -word +"some phrase" etc. Does this do what you need?

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
I am using search-ni.cgi but even after upgrading to version 1.13, the AND connector does not work for me.
Has this been fixed or the changes concern search.cgi only?

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Hi,

the search-ni.cgi was not changed. AND / OR search works. But you need to be carefull about the params you pass with your query.

Search-ni.cgi evaluates the param bool and if bool=and it will perform the search with AND. (If the bool=AND or bool=And or anything else it will do the OR search)

Please check yout templates.

regards, alexaner

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Thanks for your reply.
I checked the search.html template and bool is and (lower case).
The problem is that when somebody makes a search with and connector, no results are returned.
I have renamed search-ni.cgi to search.cgi to use it. Could this be a problem?

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
I am using search-ni.cgi. Yes, I found the AND connector can not work proply. There is a logic bug in search-ni.cgi. You can correct it as below:

change line 99 ~ 102:

$cwhere .= " c.$field LIKE '%$word%' OR ";
}
chop $cwhere; chop $cwhere; chop $cwhere;
$cwhere .= ") $bool";

to:

$cwhere .= " c.$field LIKE '%$word%' $bool ";
}
chop $cwhere; chop $cwhere; chop $cwhere; chop $cwhere;
$cwhere .= ") OR ";


and change line 109 ~ 112:

$lwhere .= " l.$field LIKE '%$word%' OR ";
}
chop $lwhere; chop $lwhere; chop $lwhere;
$lwhere .= ") $bool";

to:

$lwhere .= " l.$field LIKE '%$word%' $bool ";
}
chop $lwhere; chop $lwhere; chop $lwhere; chop $lwhere;
$lwhere .= ") OR ";

That is all.

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Due to the delay of Links sql NG, I would like to modify the search-ni.cgi to support "only search under this category".

Yes, I can simply add cat search, such as "and (l.CategoryID in ($list))", to $cwhere and $lwhere. But How about CategoryAlternates? Using this method, all CategoryAlternates of all links will not valid for cat only search.

I have tested the Links SQL 1.13, it also have this bug.

Can anyone provide help in SQL of cat only search?

Is this correct?

SELECT l.*, c.Name
FROM Links AS l, Category AS c, CategoryAlternates AS a
WHERE (... ...) AND
l.CategoryID = c.ID AND
(l.CategoryID in ($list) or
(a.LinkID = l.ID and a.CategoryID in ($list)))
LIMIT 0, 1000

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Thank you very much for this post.
I made the changes and it works as it should:-)

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
You are welcome.

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
The speed of this SQL is very very slow, and the result is not correct: have duplicated Links.

I think I shoud use 2 step:

First: select out all Link ID under this category and construct $LinkID_under_cat.
Then, and to original SQL "and l.ID in ($LinkID_under_cat)";

How do you think so? If succeed, I will post the mod.

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
Yes, I finish it. And it works better than search.cgi: it can work proply with altcateory.

All modify is based on original Links SQL 1.13. You can modify search-ni.cgi as below:

change line 62

my ($mh, $nh, $bool, $substring, $original, $query, $offset, $links, $cat, $link, $cwhere, $lwhere, $sth, $phrase,

to:

my ($mh, $nh, $bool, $substring, $original, $query, $offset, $links, $cat, $link, $cwhere, $lwhere, $sth, $phrase, $catid, $sub_list, $cat_name, $altlinks, $altlinkid,

before line 87:

# Set the offset and limit.

add:

$catid = $in->param('catid');
# Get a list of catgeory ID's for 'only this cat' type of search
if ($catid) {
# Get Category Name
$cat_name = &get_category_name ($catid);
# Get a list of category IDs where the name starts with $cat_name
if ($cat_name) {
$sub_list = &get_category_id_list ($cat_name);
chop($sub_list);
# $in->param ( 'CategoryID' => $sub_list );
}
else {$sub_list = "";}
}

before line 117:

# Get the category matches.

add:

if ($sub_list) {$cwhere .= " AND c.ID in ($sub_list) ";}

before line 141:

# Get the link matches.

add:

if ($sub_list) {
$altlinks = new Links::DBSQL "$LINKS{admin_root_path}/defs/CategoryAlternates.def";
$query = qq! SELECT LinkID FROM CategoryAlternates WHERE CategoryID in ($sub_list) !;
$sth = $altlinks->prepare ($query);
$sth->execute();
my $id;
while (($id) = $sth->fetchrow_array) {($altlinkid .= "$id,");}
$sth->finish;
chop ($altlinkid);
if ($altlinkid) {$lwhere = " (l.CategoryID in ($sub_list) OR l.ID in ($altlinkid)) AND (" . $lwhere . ") ";}
else {$lwhere = " l.CategoryID in ($sub_list) AND (" . $lwhere .") ";}
}
else { $lwhere = "(" . $lwhere . ") ";}

change line 145:

WHERE ($lwhere) AND

to:

WHERE $lwhere AND

That's all!

Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
it doesn't work for me if using 'and' bool, but ok if using 'or' bool.

i've also modified search-ni.cgi to work properly with the lines you've post at '1-Nov-00 00:04 AM' in this thread.


Quote Reply
Re: search-ni.cgi search in this cat? (Links SQL 1.13) In reply to
It works on my site. Can you provide your URL, I will check.