I have been trying to get the Links v2.0 (without Sql) doing searches using a SQL database.
i made the following modification in the nph-build.cgi, so the script will build the sql database on every rebuild.
That works fine. but i can't get the search.cgi running with that MySql Database. Is someone able to help me ?
a dump of all my files :

--- nph-build.cgi ---
sub build_search_index {
# --------------------------------------------------------
# This rountine dumps the flat file database into a sql table
# that can be used for fast searches. The process is: connect
# to sql server, drop the current (if any) search table, create
# a new search table, import the current values into the table.

my ($dbh, $sth, $query, $line, @lines, $i, @fields, $values, $errors);

# Connect to the database
$dbh = DBI->connect($DSN,$user,$pw) or &cgierr ("SQL: Unable to connect to database. Reason:
$DBI::errstr.");

# Drop the current table.
$query = qq!
DROP TABLE $sql_search
!;
# $sth = $dbh->Query ($query) or (print "\tTable not dropped! (Ignore if first time running..)\n");
$sth = $dbh->prepare($query) or (print "\tTable not dropped! (Ignore if first time running..)\n");
$sth->execute;


$sth && print "\tOld Table dropped.\n";

# Create the new table.
# $sth = $dbh->Query ($sql_create_search) or &cgierr ("SQL: Unable to perform query. Reason: $DBI::errstr.");

$sth = $dbh->prepare($sql_create_search) or &cgierr ("SQL: Unable to perform query. Reason: $DBI::errstr.");
$sth->execute;
$sth && print "\tNew Table Created.\n";

# Dump the flat-file table into the new empty SQL table.
open (DB, "<$db_file_name") or &cgierr("unable to open database: $db_file_name. Reason: $!");
@lines = <DB>;
close DB;
LINE: foreach $line (@lines) {
next LINE if ($line =~ /^#/); # Skip Comment Lines.
next LINE if ($line =~ /^\s*$/); # Skip Blank Lines.
chomp $line; # Remove new line char.

# Next we split the data and enclose in quotes. We have to escape the ' character.
@fields = &split_decode ($line);
$values = "";
for ($i=0; $i <=$#fields; $i++) {
$field = $fields[$i];
if ($sql_noquote{$i}) {
$values .= qq!$field,!;
}
else {
$field =~ s/'/\\'/g;
$values .= qq!'$field',!;
}
}
chop $values;

$query = qq!
INSERT INTO $sql_search
VALUES ($values)
!;

### $sth = $dbh->Query ($query);
$sth = $dbh->prepare($query);
$sth->execute;

if (!$sth) {
print "\tCouldn't add record: $DBI::errstr.\n\tQuery: $query\n";
$errors++;
if ($errors > 10) { &cgierr ("SQL: Too many errors! Quitting."); }
}
}
print "\tNew data imported.\n";
$dbh->disconnect;

}

-- sql.cfg ---

# Whether or not to even use SQL.
$use_sql = 1;
# Load SQL Libaries..
if ($use_sql) {
use DBI;
}
# Name of host machine.
$sql_host = 'host';
# Name of database.
$sql_database = 'efpornadm';
# Name of table to hold link information.
$sql_search = 'links';

# Fields which are integers and should not be quoted.. Defaults to the ID and
# the number of Hits..
%sql_noquote = (0 => 1, 8 => 1);

# SQL Fields to search over
@sql_search_fields = qw!Title URL Description Keywords!;

$DSN = "DBI:mysql:dbname";
$user = "user";
$pw = "pwd";

# SQL to create the table.
$sql_create_search = qq!
CREATE TABLE $sql_search (
ID BIGINT,
Title CHAR(75),
URL CHAR(255),
Date CHAR(15),
Category CHAR(255),
Description CHAR(255),
Contact_Name CHAR(75),
Contact_Email CHAR(75),
Hits INT,
isNew CHAR(5),
isPopular CHAR(5),
Rating INT,
Votes INT,
ReceiveMail CHAR(10),
Keywords CHAR(255),
isBest CHAR(5),
DateAdded CHAR(15)
)
!;


-- search.cgi (this is bugging me!) --
sub sql_search {
# --------------------------------------------------------
# Searches a sql table instead of the flat file..

my ($drh, $dbh, $sth, $query, $bool, $numhits, @results);

# Connect to the database
$dbh = DBI->connect($DSN,$user,$pw) or &cgierr ("SQL: Unable to connect to database. Reason:
$DBI::errstr.");

# Get the Link matches..
($in{'bool'} eq "or") ? ($bool = "OR") : ($bool = "AND");
$query = qq!
SELECT * FROM $sql_search
WHERE !;
foreach $term (@search_terms) {
$query .= qq!
(!;
foreach $field (@sql_search_fields) {
$query .= qq!
$field CLIKE '%$term%' OR!;
}

chop ($query); chop ($query);
$query .= qq!
) $bool!;
}
chop ($query); chop ($query); chop ($query);
$query .= qq!
ORDER BY Title !;

# Perform the Query..
# $sth = $dbh->Query ($query) or &cgierr ("SQL: Unable to query database. Reason: $Msql::db_e.\nQuery:
$query\n");
$sth = $dbh->prepare($query) or &cgierr ("SQL: Unable to query database. Reason: $DBI::errstr.\nQuery:
$query\n");
$sth->execute;

# while (@results = $sth->FetchRow) {;

while (@results = $sth->fetchrow_array) {

push (@{$link_results{$results[$db_category]}}, @results);
}

# Temp
$link_query = $query;

# Get the Category Matches (only if we are not getting a next set of hits..)
$query = qq!
SELECT DISTINCT Category
FROM $sql_search
WHERE !;
foreach $term (@search_terms) {
$term =~ s/\s/_/g; # We replace spaces with _ as Category names don't have spaces..
$query .= qq!
Category CLIKE '%$term%' $bool!;
}
chop ($query); chop ($query); chop ($query);
$query .= qq!
ORDER BY Category !;
# Perform the Query..
$sth = $dbh->prepare($query) or &cgierr ("SQL: Unable to query database. Reason: $DBI::errstr.\nQuery:
$query\n");
$sth->execute;

while ($category = $sth->fetchrow_array) {
push (@category_results, $category);
}

# Temp
$category_query = $query;
$dbh->disconnect;
}

c ya
-rapper

[This message has been edited by rapper (edited March 29, 2000).]