crow_nv
asked on
Html search
I've got simple database and url search on it.
Search engine is simple. One main search field there i can enter keywords.
How can i separate this field for some fields (fields for one keyword) to make search more comfortable for users.
How should i change CGI script to make so. Can post a CGI script too.
Search engine is simple. One main search field there i can enter keywords.
How can i separate this field for some fields (fields for one keyword) to make search more comfortable for users.
How should i change CGI script to make so. Can post a CGI script too.
post the script
ASKER
Here is the script.
#!/usr/bin/perl
# $linktitle, $linkdescrip, $linkwords, $linkemail, $linkurl
# define some global variables
$fields = 6; # Number of fields in each record
$filename = "urls.csv"; # The database text file
$results = 1000; # maximum number of results to display
$var1="/images/1";
$var2="gif";
$var3="$var1\.$var2";
&parse_form;
$searchstring = $FORM{'searchstring'};
&addrecord if ($searchstring eq "**ADD RECORD**");
&open_file("FILE1","",$fil
print "Content-type: text/html\n\n";
print "<HTML>\n";
print "<BODY BGCOLOR=#FFFFFF TEXT=#000000 LINK=#0000FF VLINK=#800040 ALINK=#800040>\n";
print "<TITLE>Search Results</TITLE>\n";
print "<CENTER><BR>\n";
print "<HR width=80% noshade><BR><UL>\n";
$counter = 0;
while (($line = &read_file("FILE1")) && ($counter < $results)) {
# split the fields at the ; character
@tabledata = split(/\s*\;\s*/,$line ,$fields);
&check_record;
if ($found == 1) {
$counter++;
&print_record;
}
}
close(FILE1);
print "</UL>\n";
if ($counter == 0) {
print "<BR><B> Sorry, No Matches were found.</B>\n";
}
print "<CENTER>\n";
print "<HR width=80% noshade>\n";
print "</CENTER>\n";
print "</A></BODY></HTML>\n";
##########################
#
# Print the matched record
#
##########################
sub print_record {
print "<BR>\n";
print " $linkdescrip $linkwords $linkmail $linkurl ";
print "<img src=\"$var3\" width=100 height=100>\n";
}
##########################
#
# Check to see if record matches search criteria
#
##########################
sub check_record {
# get the data from the record read from the file. $tabledata
$linktitle = $tabledata[0];
$linkdescrip = $tabledata[1];
$linkwords = $tabledata[2];
$linkmail = $tabledata[3];
$linkurl = $tabledata[4];
#chop($linkurl); just a comment by Roman
#build the search line with all fields we want to search in
$searchline = $linktitle . " " . $linkdescrip . " " . $linkwords. " " .$linkmail. " ".$linkur;
#search by keywords
# only perform the keyword search if the length of the search string is greater than 2
# don't think we want people to search for and or or etc.
$sfound = 0;
$found = 0;
$notfound = 1;
$stlen = length($searchstring);
if ($stlen > 1) {
@words = split(/ +/,$searchstring);
foreach $aword (@words) {
if ($searchline =~ /\b$aword/i) {
$sfound = 1;
}
else {
$notfound = 0;
}
}
}
if ($sfound == 1 && $notfound == 1) {
$found = 1;
}
# if search string is too small .. set found to 1
if ($stlen <= 1) {
$found = 1;
}
#if page doesn't have a title then return not found
$tlen = length($linktitle);
if ($tlen < 1) {
$found = 0;
}
}
sub parse_form {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
if (length($buffer) < 5) {
$buffer = $ENV{QUERY_STRING};
}
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9]
$FORM{$name} = $value;
}
}
sub open_file {
local ($filevar, $filemode, $filename) = @_;
open ($filevar,$filemode . $filename) ||
die ("Can't open $filename");
}
sub read_file {
local ($filevar) = @_;
<$filevar>;
}
sub write_file {
local ($filevar, $line) = @_;
print $filevar ($line);
}
#!/usr/bin/perl
# $linktitle, $linkdescrip, $linkwords, $linkemail, $linkurl
# define some global variables
$fields = 6; # Number of fields in each record
$filename = "urls.csv"; # The database text file
$results = 1000; # maximum number of results to display
$var1="/images/1";
$var2="gif";
$var3="$var1\.$var2";
&parse_form;
$searchstring = $FORM{'searchstring'};
&addrecord if ($searchstring eq "**ADD RECORD**");
&open_file("FILE1","",$fil
print "Content-type: text/html\n\n";
print "<HTML>\n";
print "<BODY BGCOLOR=#FFFFFF TEXT=#000000 LINK=#0000FF VLINK=#800040 ALINK=#800040>\n";
print "<TITLE>Search Results</TITLE>\n";
print "<CENTER><BR>\n";
print "<HR width=80% noshade><BR><UL>\n";
$counter = 0;
while (($line = &read_file("FILE1")) && ($counter < $results)) {
# split the fields at the ; character
@tabledata = split(/\s*\;\s*/,$line ,$fields);
&check_record;
if ($found == 1) {
$counter++;
&print_record;
}
}
close(FILE1);
print "</UL>\n";
if ($counter == 0) {
print "<BR><B> Sorry, No Matches were found.</B>\n";
}
print "<CENTER>\n";
print "<HR width=80% noshade>\n";
print "</CENTER>\n";
print "</A></BODY></HTML>\n";
##########################
#
# Print the matched record
#
##########################
sub print_record {
print "<BR>\n";
print " $linkdescrip $linkwords $linkmail $linkurl ";
print "<img src=\"$var3\" width=100 height=100>\n";
}
##########################
#
# Check to see if record matches search criteria
#
##########################
sub check_record {
# get the data from the record read from the file. $tabledata
$linktitle = $tabledata[0];
$linkdescrip = $tabledata[1];
$linkwords = $tabledata[2];
$linkmail = $tabledata[3];
$linkurl = $tabledata[4];
#chop($linkurl); just a comment by Roman
#build the search line with all fields we want to search in
$searchline = $linktitle . " " . $linkdescrip . " " . $linkwords. " " .$linkmail. " ".$linkur;
#search by keywords
# only perform the keyword search if the length of the search string is greater than 2
# don't think we want people to search for and or or etc.
$sfound = 0;
$found = 0;
$notfound = 1;
$stlen = length($searchstring);
if ($stlen > 1) {
@words = split(/ +/,$searchstring);
foreach $aword (@words) {
if ($searchline =~ /\b$aword/i) {
$sfound = 1;
}
else {
$notfound = 0;
}
}
}
if ($sfound == 1 && $notfound == 1) {
$found = 1;
}
# if search string is too small .. set found to 1
if ($stlen <= 1) {
$found = 1;
}
#if page doesn't have a title then return not found
$tlen = length($linktitle);
if ($tlen < 1) {
$found = 0;
}
}
sub parse_form {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
if (length($buffer) < 5) {
$buffer = $ENV{QUERY_STRING};
}
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9]
$FORM{$name} = $value;
}
}
sub open_file {
local ($filevar, $filemode, $filename) = @_;
open ($filevar,$filemode . $filename) ||
die ("Can't open $filename");
}
sub read_file {
local ($filevar) = @_;
<$filevar>;
}
sub write_file {
local ($filevar, $line) = @_;
print $filevar ($line);
}
ASKER
Adjusted points from 25 to 60
ASKER
Adjusted points from 60 to 100
use some input fields named inp1...inp4 for ex.
then check_record looks like this:
--------8<----------------
$sfound = 0;
$found = 0;
$notfound = 1;
$inp[1]="inp1";
$inp[2]="inp2";
$inp[3]="inp3";
$inp[4]="inp4";
foreach $key in (@inp) {
$aword=$FORM{$inp[$key]}
$stlen = length($aword);
if ($stlen > 1) {
if ($searchline =~ /\b$aword/i) {
--------8<----------------
hope this helps,
maf
then check_record looks like this:
--------8<----------------
$sfound = 0;
$found = 0;
$notfound = 1;
$inp[1]="inp1";
$inp[2]="inp2";
$inp[3]="inp3";
$inp[4]="inp4";
foreach $key in (@inp) {
$aword=$FORM{$inp[$key]}
$stlen = length($aword);
if ($stlen > 1) {
if ($searchline =~ /\b$aword/i) {
--------8<----------------
hope this helps,
maf
ASKER
May be you have some other scripts?
Something better could be very usefull.
I want to use Database modul (DBI)and SQL expressions to search within a database.
Now I am trying your variant. Thank you for help.
Something better could be very usefull.
I want to use Database modul (DBI)and SQL expressions to search within a database.
Now I am trying your variant. Thank you for help.
ASKER
If you want I can increase points.
heres a dbi demo i done
# make use of the dbi module. for activeperl go to www.activestate.com/packages/zips and
# download the DBI package first and then download what ever DBD drivers you what to use
# this script makes use of the odbc driver module
use DBI;
# the dbd driver we actually want to use
$DBI_DRIVER = "ODBC";
# the dsn info
$DATA_SOURCE = "test_db";
$DATA_SOURCE_USERNAME = "";
$DATA_SOURCE_PASSWORD = "";
# global database handle for this script
my $dbh;
# connect to database, the first parameter will take the form of 'dbi:ODBC:test_db'
$dbh = DBI->connect('dbi:'.$DBI_D
|| die("Connection error : ".$DBI::errstr);
# set the LongReadLen really high incase we have meno fields
$dbh->{LongReadLen} = 30000;
# prepare a stmt, this returns a statment handle
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);
# use some of the stmt handle vars to determine the fields that
# will be returned. this is usfull if we dont know in advance
# the number/name of fields that will be returned i.e 'select *'
print("Expected fields to be returned from first query.......\n");
$fi = $sth->{'NAME'};
for($i=0;$i<$sth->{'NUM_OF
{
#print("field : @{$fi[0]}[$i]\n");
print("field : $fi->[$i]\n");
}
print("\n\n");
# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);
# if we are debugging then we may wish to dump our result in one hit
# the dump_results function uses default values for row delimiter's etc
# these can be overridden
# dump_results(st_handle,max
print("Dump of the results returned by stmt, for debugging purposes\n");
DBI::dump_results($sth);
print("\nNormal results retrival using a loop to go through each row returned\n");
# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);
# fetch and print the rows, returns undef on failure, hash reference on success
while($dat = $sth->fetchrow_hashref())
{
# get the name field from our hashref
print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}
# finish the statement handle, if we dont do this the disconnect function will spew some errors
$sth->finish();
print("\nDoing some inserts into the database\n");
# set auto commit to false so we have to explicetly call commit on the database handle
$dbh->{"AutoCommit"} = 0;
# we can prepare a stmt and execute it several times with different values in the place holders like this
$sth = $dbh->prepare("INSERT INTO table1 (name,address) VALUES (?,?)") || die("Stmt error : ".$dbh->errstr);
# execute it but this time pass the values we want to replace the place holders
$sth->execute("bart","satu
# commit it
$dbh->commit() || die("Stmt error : ".$dbh->errstr);
# turn auto commit back on
$dbh->{"AutoCommit"} = 1;
# finish with the stmt handle
$sth->finish();
print("\nDoing an update on all records (changing address to mars)\n");
# we can use the do function to execute a stmt and get the number of rows it affected
$rows = $dbh->do("UPDATE table1 SET address = 'mars'") || die("Stmt error : ".$dbh->errstr);
print("That affected ".$rows." rows\n");
# now print the updated table out
print("\nUpdated table contents are : \n");
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);
$sth->execute() || die("Stmt error : ".$dbh->errstr);
while($dat = $sth->fetchrow_hashref())
{
# get the name field from our hashref
print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}
$sth->finish();
print("\n\tBye bye.... Shutting up shop !!!!");
# closes the database
$dbh->disconnect();
# make use of the dbi module. for activeperl go to www.activestate.com/packages/zips and
# download the DBI package first and then download what ever DBD drivers you what to use
# this script makes use of the odbc driver module
use DBI;
# the dbd driver we actually want to use
$DBI_DRIVER = "ODBC";
# the dsn info
$DATA_SOURCE = "test_db";
$DATA_SOURCE_USERNAME = "";
$DATA_SOURCE_PASSWORD = "";
# global database handle for this script
my $dbh;
# connect to database, the first parameter will take the form of 'dbi:ODBC:test_db'
$dbh = DBI->connect('dbi:'.$DBI_D
|| die("Connection error : ".$DBI::errstr);
# set the LongReadLen really high incase we have meno fields
$dbh->{LongReadLen} = 30000;
# prepare a stmt, this returns a statment handle
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);
# use some of the stmt handle vars to determine the fields that
# will be returned. this is usfull if we dont know in advance
# the number/name of fields that will be returned i.e 'select *'
print("Expected fields to be returned from first query.......\n");
$fi = $sth->{'NAME'};
for($i=0;$i<$sth->{'NUM_OF
{
#print("field : @{$fi[0]}[$i]\n");
print("field : $fi->[$i]\n");
}
print("\n\n");
# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);
# if we are debugging then we may wish to dump our result in one hit
# the dump_results function uses default values for row delimiter's etc
# these can be overridden
# dump_results(st_handle,max
print("Dump of the results returned by stmt, for debugging purposes\n");
DBI::dump_results($sth);
print("\nNormal results retrival using a loop to go through each row returned\n");
# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);
# fetch and print the rows, returns undef on failure, hash reference on success
while($dat = $sth->fetchrow_hashref())
{
# get the name field from our hashref
print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}
# finish the statement handle, if we dont do this the disconnect function will spew some errors
$sth->finish();
print("\nDoing some inserts into the database\n");
# set auto commit to false so we have to explicetly call commit on the database handle
$dbh->{"AutoCommit"} = 0;
# we can prepare a stmt and execute it several times with different values in the place holders like this
$sth = $dbh->prepare("INSERT INTO table1 (name,address) VALUES (?,?)") || die("Stmt error : ".$dbh->errstr);
# execute it but this time pass the values we want to replace the place holders
$sth->execute("bart","satu
# commit it
$dbh->commit() || die("Stmt error : ".$dbh->errstr);
# turn auto commit back on
$dbh->{"AutoCommit"} = 1;
# finish with the stmt handle
$sth->finish();
print("\nDoing an update on all records (changing address to mars)\n");
# we can use the do function to execute a stmt and get the number of rows it affected
$rows = $dbh->do("UPDATE table1 SET address = 'mars'") || die("Stmt error : ".$dbh->errstr);
print("That affected ".$rows." rows\n");
# now print the updated table out
print("\nUpdated table contents are : \n");
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);
$sth->execute() || die("Stmt error : ".$dbh->errstr);
while($dat = $sth->fetchrow_hashref())
{
# get the name field from our hashref
print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}
$sth->finish();
print("\n\tBye bye.... Shutting up shop !!!!");
# closes the database
$dbh->disconnect();
i can do a quick serach engine demo for you ??
ASKER
May be you have some other scripts for
such purpose ? I trying to make search
engine using SQL expressions. What language to use ?
such purpose ? I trying to make search
engine using SQL expressions. What language to use ?
ASKER
sorry for last comment. My page was not updated.
i have an example of a very simple serach script that uses a simple access data base with a single table.
its written in perl. for what your doing there is no reason not to use perl. the dbi module is really easy to use
its written in perl. for what your doing there is no reason not to use perl. the dbi module is really easy to use
it uses sql
ASKER
to makerp and maf >
I can undestand a scripts but I have problems when I trying to make HTML file. That do you think about
maf's answer?
$searchstring was FORM{searchstring}
How it should be now?
Can I write something like
inp1...n are text fields in html FORM
$searchstring= FORM{"inp1/inp2/inp3"}
I have 60 point and I will give it to
you. I can open another account and give
you more points if you ask about it.
Can I ask you using e-mail? Please answer me to roman.ch@mail.ee
Thank you both
I can undestand a scripts but I have problems when I trying to make HTML file. That do you think about
maf's answer?
$searchstring was FORM{searchstring}
How it should be now?
Can I write something like
inp1...n are text fields in html FORM
$searchstring= FORM{"inp1/inp2/inp3"}
I have 60 point and I will give it to
you. I can open another account and give
you more points if you ask about it.
Can I ask you using e-mail? Please answer me to roman.ch@mail.ee
Thank you both
heres an example of a simple search script. set it up on your box and run from a broweser. then mess about with it. using a database will make your life so much easier...
to query on multiple fields build your sql string dynamically before exection..
:)
# Wriiten by : Paul Maker
#
# very simple serach engine demo using DBI. the data base is a simple
# access db with one table called sites. each row has a url column and
# keyword column
use CGI;
use DBI;
# dsn i have created in odbc contol panel
$DSN = "test_db";
# dbd driver to use
$DRIVER = "ODBC";
$query = new CGI;
print $query->header();
# if we have no params then display the form
if(!$query->param())
{
print("
<B>Enter a keyword to search ON : </B>
<FORM ACTION=search_engine.pl METHOD=POST>
<INPUT TYPE=TEXT NAME=search>
<INPUT TYPE=SUBMIT VALUE=Search>
</FORM>
");
}
# else they have entered a keyword so lets search and display some results
else
{
# open a connection to the database
$dbh = DBI->connect('dbi:'.$DRIVE
# create a stmt
$sth = $dbh->prepare("SELECT * FROM sites WHERE keyword = '".$query->param('search')
#exe it
$sth->execute() || die("Stmt error : ".$dbh->errstr);
print("RESULTS are : <BR><HR><TABLE BORDER=1><TR><TH>URL's</TH
while($dat = $sth->fetchrow_hashref())
{
print("<TR><TD>".$dat->{'u
}
print("</TABLE><BR><A HREF=search_engine.pl>Sear
$sth->finish();
}
to query on multiple fields build your sql string dynamically before exection..
:)
# Wriiten by : Paul Maker
#
# very simple serach engine demo using DBI. the data base is a simple
# access db with one table called sites. each row has a url column and
# keyword column
use CGI;
use DBI;
# dsn i have created in odbc contol panel
$DSN = "test_db";
# dbd driver to use
$DRIVER = "ODBC";
$query = new CGI;
print $query->header();
# if we have no params then display the form
if(!$query->param())
{
print("
<B>Enter a keyword to search ON : </B>
<FORM ACTION=search_engine.pl METHOD=POST>
<INPUT TYPE=TEXT NAME=search>
<INPUT TYPE=SUBMIT VALUE=Search>
</FORM>
");
}
# else they have entered a keyword so lets search and display some results
else
{
# open a connection to the database
$dbh = DBI->connect('dbi:'.$DRIVE
# create a stmt
$sth = $dbh->prepare("SELECT * FROM sites WHERE keyword = '".$query->param('search')
#exe it
$sth->execute() || die("Stmt error : ".$dbh->errstr);
print("RESULTS are : <BR><HR><TABLE BORDER=1><TR><TH>URL's</TH
while($dat = $sth->fetchrow_hashref())
{
print("<TR><TD>".$dat->{'u
}
print("</TABLE><BR><A HREF=search_engine.pl>Sear
$sth->finish();
}
ASKER
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
What is search_engine.pl ?
search_engine.pl is an example of a .pl script which sercahes a very simple database and displays the results. i wrote it to give you an idea of how to use a database as a datastore and then write a serach engine to query it.
if you create a simple access db and create a dsn (odbc contol panel, it must be a system dsn you create) for it called 'test_db'. the data base must have a table called sites with two text columns called url and keyword. put some test data in it and your off.
you will need to get dbi.pm and then the dbd-odbc.pm. both can be obtained from http://www.activestate.com/packages/zips
if your on win32 platform then i strongly recomemnd activestate's perl. the html help it build is good and module instrallation is so easy..
:)
let us know how you get on.. ive armed you with enough demo scripting to get you going...
if you create a simple access db and create a dsn (odbc contol panel, it must be a system dsn you create) for it called 'test_db'. the data base must have a table called sites with two text columns called url and keyword. put some test data in it and your off.
you will need to get dbi.pm and then the dbd-odbc.pm. both can be obtained from http://www.activestate.com/packages/zips
if your on win32 platform then i strongly recomemnd activestate's perl. the html help it build is good and module instrallation is so easy..
:)
let us know how you get on.. ive armed you with enough demo scripting to get you going...
ASKER
Adjusted points from 100 to 160
ASKER
I understand now SQL and can write expessions in it so I think that now
I am able to make my own search engine
using SQL to catch data from database and perl or C logic.Only problem that
I don't have SQL server - my provider doesn't support it.
May be built my own server?
Thank you for support.
Have you a web site?
May be I can touch you throu the internet?
roman.ch@mail.ee
I am able to make my own search engine
using SQL to catch data from database and perl or C logic.Only problem that
I don't have SQL server - my provider doesn't support it.
May be built my own server?
Thank you for support.
Have you a web site?
May be I can touch you throu the internet?
roman.ch@mail.ee
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have access installed.
how I'll execute sql?
how I'll execute sql?
ASKER
I have access installed.
how I'll execute sql?
how I'll execute sql?
you need to set up a system dsn in control panel. go to odbc and click on it. this will bring up a dialogue. select the system tab and go to add. select the access driver then go finish. enter a dsn name and then select the database from the filesystem that you want to talk to.
create the db i suggested in my example. be sure to name the dsn in the script the same as the dsn you have set up.
you will need to set the web server to allow exe access in the dir the script is in. also if the db is in different dir be sure the web server has the correct permissions .. this will only probably matter if your on an NT server,, i which case your system admin will sort this (if there any good :))
create the db i suggested in my example. be sure to name the dsn in the script the same as the dsn you have set up.
you will need to set the web server to allow exe access in the dir the script is in. also if the db is in different dir be sure the web server has the correct permissions .. this will only probably matter if your on an NT server,, i which case your system admin will sort this (if there any good :))
ASKER
Thank you makerp!!
also can you change the search script or is this something that you just use