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

How do i alter the sql search

Quote Reply
How do i alter the sql search
I wanna retrieve all links where a certain cetegory is ALWAYS present
for instance, i wanna search for bikes, where a certain field (location) is equal to the selected field from a drop down in the search page

eg is on http://www.shoplinks.com.au/cgi-bin/search.cgi

thanks for any one who can help me!
Quote Reply
Re: How do i alter the sql search In reply to
Ok,

I wrote two answers to this... and didn't like either of them.

In looking at search.cgi I don't see where the parameter 'bool' is actually used in the search routines....

Anyway, if you only want to all "and" searches on your site, not "or" searches, doing what you want is a lot easier...

have the drop down box pass a parameter 'query2'

At the top of search.cgi where

Code:
if ($in->param('query')) {
&search ($in, $dynamic);
}

insert something like:

$in->param('query') = $in->param('query') . ' ' . $in->param('query2')'

That appends the value of the search field to the query term, and things are good.

If you want to allow "or" as you do on your search page, the theory is the same, but down in the lower bowels of the routine, you want to pass 'query2' to the dbsql.pm query routine, and modify that code a bit such that if query2 exists, you append:

' AND ' . query2

to the select statement.

It's more complex.... but not terribly.

Let me know if it works Wink

I'm sure there is going to be an easier way, but right now that's how I'd do it if I needed it done <G>



------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: How do i alter the sql search In reply to
Ive added the line you suggest like so

sub main {
# ---------------------------------------------------
# Determine what to do.
#
my $in = new CGI;
my $dynamic = $in->param('d') ? $in : undef;
print $in->header();

if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2')') {
&search ($in, $dynamic);
}
else {
&site_html_search_form ( { query => '' }, $dynamic );
}
}
# ==============================================================

I am getting the following error;

Software error:
Bad name after mh' at /usr/local/etc/httpd/vhosts/www.shoplinks.com.au/cgi-bin/search.cgi line 67.

Before I just the the State field past onto the search.cgi like this state=melbourne but this wouldn't work if it did not find any result and a software error appeared.

Can you contact me on icq if you have time #20750400

Thanks for your help
Quote Reply
Re: How do i alter the sql search In reply to
This
if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2')')

Should be
if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2'))

------------------
LookHard Search
lookhard.hypermart.net
Lavon Russell

Quote Reply
Re: How do i alter the sql search In reply to
Ive fixed that error in the line and now I am receiving this error when running the search.cgi

--------------------------------------------
Software error:
Execution of /usr/local/etc/httpd/vhosts/www.shoplinks.com.au/cgi-bin/search.cgi aborted due to compilation errors.
--------------------------------------------

Before I modified the search.cgi I just had the location field being passed onto the search.cgi. This worked fine when it found a result but when it didnt find a result for that location I had also another software error message.

If you are able to help me adding a geographical location can you contact me on ICQ # 20750400.

Thank you
Jason
Quote Reply
Re: How do i alter the sql search In reply to
pugdog I must say thanks for your help you reply so quickly.

My logs file says

-----------------------------------------
Can't modify subroutine entry in scalar assignment at /usr/local/etc/httpd/vhosts/www.shoplinks.com.au/cgi-bin/search.cgi line 48, near ")) "
[Mon Feb 28 22:33:23 2000] search.cgi: Execution of /usr/local/etc/httpd/vhosts/www.shoplinks.com.au/cgi-bin/search.cgi aborted due to compilation errors.
---------------------------------------------

I've tried deleting the () {} but it doesn't help and I just get even more errors.

The code I have in the section I've modified is


sub main {
# ---------------------------------------------------
# Determine what to do.
#
my $in = new CGI;
my $dynamic = $in->param('d') ? $in : undef;
print $in->header();

if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2')) {
&search ($in, $dynamic);
}
else {
&site_html_search_form ( { query => '' }, $dynamic );
}

}
# ==============================================================

I think it has something to do with the $in->param('query2'))

I havent gotten a syntax error and all the brackets seem to match up fine. I have probally forgotten to enter a " somewhere along the lines.

The error log is here
http://www.shoplinks.com.au/logs/error_log

and the search.cgi is here

http://www.shoplinks.com.au/cgi-bin/search.cgi


Thanks once again pugdog

Jason
Quote Reply
Re: How do i alter the sql search In reply to
Hi Jason and all. Real novice here so keep that in mind with anything I type in reply to this thread. I likely have more questions than helpful info on resolving your problem.

help me understand here . . .
if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2')') {

it appears to me this if statement is asking whether param('query') = itself AND/OR param('query2')? Wouldn't that defeat the purpose of your "bikes AND locale" targeted searches?

wouldn't something like this work?
my param('query') = param('query1') . " AND " . param('query2')


pugdog had wrote:
Quote:
In looking at search.cgi I don't see where the parameter 'bool' is actually used in the search routines....
and really piqued my interest. Surely, bool combinations had to be in there somewhere . . . after several attempts at various find combinations, it looks like (again, I may be wrong - I honestly don't know what I'm doing!) in:
sub log_query
at my $time = $db->get_date . " " . $db->get_time;
may be an example you can use.
(way at the bottom of search.cgi)

Please accept my apologies if I've been out in left field on the problem you've identified and my reply on your topic.




------------------
~ ~ ~ ~ ~
Karen


[This message has been edited by Karen (edited February 28, 2000).]
Quote Reply
Re: How do i alter the sql search In reply to
Check your server error logs.... right after you run that, the most recent errors will be at the end.

You'll find out where, and often times why the program failed to run.

------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: How do i alter the sql search In reply to
Ok, obviously it was something I said....

but:

Code:
if ($in->param('query') = $in->param('query') . ' ' . $in->param('query2')) {
&search ($in, $dynamic);
}
else {
&site_html_search_form ( { query => '' }, $dynamic );
}

is not a valid statement....

When I said "insert something like" I meant it literally!!

Code:

if ($in->param('query')) {
if ($in->param('query2') {
$in->param('query') = $in->param('query') . ' ' . $in->param('query2');
}
&search ($in, $dynamic);
}
else {
&site_html_search_form ( { query => '' }, $dynamic );
}

What you want to do is test to see if there is a 'query' if there is a query,
then you want to test to see if there is query2 -- if not, ignore it, if there is, append it to the end of the query string and go do something with the new query. Else, there is no query, so print out the search
form again.

(This is assuming a default "AND" connectory in the search query string from the form.)

When I had originally put the line:

$in->param('query') = $in->param('query') . ' ' . $in->param('query2');

It was because I was thinking the second "if" test is really not needed, since
if query2 is null, it's not going to do anything anyway, but obviously it made
readability really bad!

Sorry,



[This message has been edited by pugdog (edited February 28, 2000).]
Quote Reply
Re: How do i alter the sql search In reply to
I don't seem to getting anywhere modifying the search.cgi

What happens if I just pass on the value state="value" to the search.cgi and give that a bigger weight in the table.

Would this retrieve products that are more relevant to that state. Would it return say all listings with Melbourne firstly then any other products in different states?

I know this may not seem right but I have no idea with PERL besides modifying basic attributes and the parth to perl.

Alex if you get my email can you have a look at this because I asked you before purchasing links SQL if searching by location would easily be done and you said if could be done.

Any help much appreciated.

Thanks pugdog you reply very quickly.

Jason
Quote Reply
Re: How do i alter the sql search In reply to
Hi,

You could do that... if you gave the location field a weight of, say 100, then it would eclipse anything else, and your sorts would essentially be by location and within that by other points.

The code I gave before didn't work? By adding the location field into the query string, and forcing an "and" it should have worked, albeit a bit restrictive on the searches.

Something changed in the search routines in the last release, and I think it's being worked on, since a lot of people (me included) wanted to be able to return the searches based on something other than "score" order.

I _know_ there is a way to pass extra information to the search routines. Did you check the FAQ for "search" ?? That's probably about where I left off, (and I need to catch up!!)

What didn't work in the code above?

On your form, get rid of the 'or' and 'and' options, and make the field a "hidden" field and have the value set to "and". That will force the two fields to be joined with "and" but it will also mean you can't do searches like "rain or snow" ...

It _should_ work, and is at least a temporary solution.

You should only get records that have that as a match.

Let me know what is happening, and maybe I can fix the code I gave to work right. Check the logic I gave above... and make sure your location drop down box is called query2!






------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: How do i alter the sql search In reply to
You can't assign something to $in->param like that. What you need to do is:

$in->param('query', $in->param('query') . $in->param('query2'));

which is pretty ugly. =)

Am I missing something, or can you not just do:

search.cgi?query=bikes&location=Vancouver

That will search for all records that match 'bikes' and are in location=Vancouver where location is a field.

Cheers,

Alex
Quote Reply
Re: How do i alter the sql search In reply to
Alex,

I've changed to what you suggested. I did do this from the start but the only reason why I didn't like it is because of an error I'm getting. When entering a multiple word query that is over 30 characters I normally receive an error like so.

-------------------------------------------
Content-type: text/html
Software error:
Search (69151): Fatal: query: Can't execute: SELECT count(*) FROM Links WHERE ID in () and (( location = "Melbourne")) (You have an error in your SQL syntax near ') and (( location = "Melbourne"))' at line 1) at admin/Links/DBSQL.pm line 1492
For help, please send mail to the webmaster (webmaster@weblinks.com.au), giving this error message and the time and date of the error.
-------------------------------------------

I don't think this is a misconfiguration error. The logs files also reported the exact same error and you can view the log files here.

-------------------------------------------
http://www.shoplinks.com.au/logs/error_log
-------------------------------------------

Do I need to edit something in the DBSql.pm file.

Thanks
Jason
Quote Reply
Re: How do i alter the sql search In reply to
What's the whole statement?

MySQL is kinda picky about parentheses sometimes. It looks like it would work, but it doesn't -- getting rid of the parentheses where possible can often fix it.

I don't know why... but if the statement "looks" correct, it may just be you tried to group the query with a set of () the MySQL parser didn't like.

When I was working on the "load" and "insert" queries I posted last month, I hit that problem. That's why they don't have the extra () that might seem to make them clearer to read.



------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: How do i alter the sql search In reply to
Hi Jason,

I'm pretty sure I had the same problem. And it's been fixed in another thread. Check out
www.gossamer-threads.com/scripts/forum/resources/Forum9/HTML/000685.html

That should fix that bug. For me, it only happened when I searched for something that wasn't in the database and only intermittently did it give me that error. Since I implemented the bug fix, I haven't had a problem.

Hope it works for you too.

Peace.

Kyle