Be sure to use bind parameters for the args and set up the query so that there is a
defined universe of choices.
See an example of one of my apps -- http://cdec.water.ca.gov/cgi-progs/staSearch
What I did was set up separate parameters for each checkbox (and of course, separate
parameters for each arg). As you selected each checkbox, the query joins more tables
in order to narrow the field of results.
sample psuedo code:
$query = "select title, year, band.name from cd, band where band_id=band.id";
if ($band_ck) {
$query .= " and band.name = ?";
push(@args,$band);
}
if ($title_ck) {
$query .= " and title = ?";
push(@args,$title);
}
# add more $_ck here ...
my $sth = $dbh->prepare($query);
my $rc = $sth->execute(@args);
while ...
$sth->finish;
-----Original Message-----
From: Olivier BILLET [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 5:10 AM
To: [EMAIL PROTECTED]
Subject: Query
Hi,
Does anybody know what is the best way to full text query
some database with a many to many table efficiently?
(This is for a web front end written in perl.)
Let me precise the question with a simple example:
TABLE Artist
id | Name | Surname | BirthDate
TABLE InBand
band_id | artist_id
FOREIGN band_id
FOREIGN artist_id
TABLE Band
id | Name
TABLE CD
id | title | year | band_id
FOREIGN band_id
Now a web user come and input:
find CDs with (artist.age=23 and title="hello world") or
(artist.name=smi.* and year=2003 or
artist.surname=b?ob)
Since users come with incredible queries (that is with a somehow
random number of args and structure), it seems to be impossible
to prepare SQL statements (caching)?
So the question is what is the best way to perform such queries efficiently?
Thanks in advance,
Olivier.
