Hello sql-pro's,

I have read a lot about search in the sql-database, but didn't find a answer on my following question:

- Is it possible too search on fields instead off all fields in onces.

Example:

In 1 html file i want 3 possible search options:

SEARCH 1: Title <input data title> [button search here]

SEARCH 2: Date bigger then [input data start date] and smaller then [input data end date] - [button search here]

SEARCH 3: Date [select box with create date's, example may 2000, juni 2000] - [button search]

I hope you know what i mean.
Maybe i can't use sqllinks and have to use dbman sql ? Or have to use sql command?

Allready thanks.

Hi,

Alex did his best to make the search routines as variable as possible. But he was focused on getting quick search results with indexed search terms.

I tried to change the search scrips to do range searches like (price between 100$ .. 200$) and modified the sripts. The result was embarassing slow.

So I split up the searches in different scripts (subs) for doing different requests. SQL is made for searching! and is fast!

For your problem I would make different search routines with the apropriate SQL commands for each search. And NOT try to modify the original search routines (In german we would call these routines "Eierlegende Wollmilchsau") I dont know an english word for something "trying to do everything for all purposes".

regards, alexander

Thanks for the answer, can you post a script for searching with sql commands which uses the sqllinks layout ? I am a SQL newbie b.t.w. :)...

How can you do it within sqllinks btw ? I wanna try (versuche:)) it.

Hi,

This is no sub to copy!! It is just meant to explain how to use SQL commands within your cgi scripts.

# ==============================================================
sub search_date {
# ---------------------------------------------------
# Performs a specified search for date regions
# only a short guide
#
# define ALL Variables used!

my ($in, $dynamic) = @_;
my ($linkdb, $link_hits, $link_results, $found, $start_date, $end_date, $query, $Links);

# Read the passed parameters
$start_date = $in->param('Start_Date') || undef;
$end_date = $in->param('End_Date') || undef;

# Check the parameters for the right format
#
#
#


# Connect to the database
#
$linkdb = new Links::DBSQL "$LINKS{admin_root_path}/defs/Links.def";

# Create your SQL Query String
# read http://www.mysql.com/documentation/mysql/commented/manual.php?section=Reference
# for SQL Language reference
# Important: - Only select the fields you need
# - Always Limit the results

$query= qq|
SELECT Title, Description, URL, Add_Date
FROM Links
WHERE Add_Date < ? AND Add_Date > ?
ORDER BY Add_Date
LIMIT ?

|;
# prepare the database

$link_results = $linkdb->prepare($query);

# execute the query
$link_results->execute( $start_date, $end_date, 100);

# convert the results to html

while ($found = $link_results->fetchrow_hashref){
$Links .= &site_html_link ($found, $dynamic);
}

# Print out the HTML results.
&site_html_search_results ({$Links},$dynamic);

}

Happy modding!

regards, alexander

Thanks again you turned a little lightbutton in my head on:)... I have to study it but i got your clue!

Any know if this will be easier in the (i hope very soon) coming new sql links version ?


(Is dbman sql a better solution if i want to use it 'only' (with top 10) for searching in the database ?)