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).]

The short answer.... and you won't like it, is to upgrade to Links 2.0. Looking at that code, there is a _reason_ why I haven't checked the Links 2.0 forum in months!

It's doing it the hard way! Really. What you would gain from the upgrade is apparant in the code you've shown.

But, in the interest of SQL queries... what part of the search.cgi isn't working? Where are you hitting the errors? What is it not doing for you that you think it should (or are trying to get it to do)?

I've integrated a few flat-file programs into SQL, but after doing one the way you are doing it, I have had all the others go through the DBSQL.pm module! For "links" it's set up to handle the format, record structure, and all the things we want to do with it, and return the results ready to output to the templates.

What you are doing is a fairly complex thing, and the more you try to improve it the more complex it will get. If you do eventually upgrade to Links SQL, you'll kick yourself for wasting the all the time! Really.

Putting this at the top of any new database access script:


Saves a world of frustration, and hours upon hours of time!

yes i know i'm doing it the hard way

the problem is that there is a mistake in the
search query, because it replys with the
whole database and not with the entrys that
contain the search string.

i'm not sure if i have those librarys.
where can i get them from, or could you mail
them to me ? (griessm@gmx.net)
also the board inserted a smiley instead of
some chars ... so what are does !?

Ps.: I do not want to switch to Link SQL,
mainly because i have been working on this
script (Link v2) for like 1 month. improved
all kinds of stuff. Installed new mods, and
if i buy that Links SQL i will need to start
from new, because it's a complete rewrite.
So i thought i just optimize the search
script by using MySql ...

c ya
-rapper

The modules I mentioned are part of the Links SQL script -- that is the "heart" of what makes the database access so easy.

I think you will find if you have access to the MySQL database, that _any_ time you spend trying to modify or make Links 2.0 work is wasted time. This is _not_ criticising Alex, Links 2.0 was amazing, but there is a reason he went to SQL

What you are trying to do in Links 2.0 could be done simply, and very, very easily with the SQL version. Most of the mods you made are either part of the SQL version, or could be added simply and easily.

Granted, some of the more elaborate mods -- like the review/ratings are not available yet, but they will be. And when they are, they will simply blow away the flat file version.

Why? With SQL you can dynamically create relations as you build the database. Thus, you don't have to try to maintain relations while running the database.

Where is your site? If you want a quick evaluation, I can look at it and tell you how much of it could be ported to Links SQL and what is not available right now (at least the parts that are visible).

On the back end, there is simply no way the flat file system can offer the flexibility and accounting that the SQL version does.

I've modified jump.cgi in a few lines to track HITS and VISITS, so I can tell how much is a detailed page view and how many of those go on to look at the site.

Because of these easy modifications to the main database, jump.cgi can be used for internal advertising, and advertising links, as well as context-level re-direction.

Anyway, since your reply wasn't "I can't afford it" (which is only a partially valid answer anyway) there are so many more reasons why you SHOULD upgrade than not -- ESPECIALLY since you have access to MySQL and want to use it!

for now i need to work with that script.
i'm not in the position to pay $450 for a
script, without knowing to get that money
back from the script.

It's pretty easy. If this script works out,
and people visit my site and i can get some
money of sponsoring, etc. then i will say
"yes ok, why not. this script made me some
money, why not upgrading it... "
... but i got no money from it for now ...

don't get me wrong. you all made a very good
work, and yes i will pay for it.
but i'm always having so many ideas. and
sometimes in the next minute i have a better
idea than the previous minute ... and then
i pay that money for nothing ...

c ya
-Rapper