List:General Discussion« Previous MessageNext Message »
From:Rhino Date:August 22 2004 1:23pm
Subject:Re: Dynamic Queries
View as plain text  
----- Original Message ----- 
From: "Stuart Felenstein" <stuart4m@stripped>
To: <mysql@stripped>
Sent: Sunday, August 22, 2004 7:32 AM
Subject: Dynamic Queries

> Not sure what to call what I'm attempting to do,
> decided on dynamic queries. I should mention that I've
> been working with databases for just a little over a
> month.
> Example could be many web sites, but let's say Expedia
> (the travel flights, cars, etc)
> My understaning is that the SQL statements are
> contained in a recordset, but variables would be
> passed back into the sql statement based on the
> particulars of the user's input?  Is that correct, is
> that the only way ?
I've been working with relational databases, mostly DB2, since 1985 and I'm
not entirely sure of the precise meaning of the term "recordset". I've never
seen a formal definition of that term. However, I'm pretty sure that a
"recordset" is not the query that you pass to the database but the result
that you get back. It's always been my practice to refer to the result of
the query as a result set, not a recordset. I'm not trying to tell you not
to use the term recordset - it might be widely used by some groups of people
for all I know - I'm just trying to explain where I'm coming from.

As to your specific question, yes, SQL statements can be written to contain
variables. For example, if you were searching on Expedia, you could have a
query that tells the database to return hotel names and addresses given a
specific city and date, which the user supplies at execution time. The
result set will then contain all of the rows that represent the prices of
hotel rooms in that city on that date.

In this case, the query would typically contain placeholders for the
variables and the query would be prepared (compiled) without knowing the
values of those placeholders. In other words, when the statement was
prepared, the computer wouldn't know what city or date the user wanted. For
example, the query might look like this:

String query = "select hotel_name, hotel_address, hotel_cost_per_night from
hotels where city = ? and date = ?";

That's how I would do it in Java. The question marks are the placeholders
representing the variables for city name and date.

It would also be possible to do it via string concatenation, although this
can be bit problematic when the variables contain quotes or other special
characters. For example:

String query = "select hotel_name, hotel_address, hotel_cost_per_night from
hotels where city = " + city_name + " and date = " + stay_date;

You can also combine the two approaches. For example,

String query = "select hotel_name, hotel_address, cost_per_night from " +
table_name + " where city = ? and date = ?";

Naturally, in all of these approaches, the values of the variables have to
be determined at execution time. There are a variety of ways that this could
be accomplished but the most common is for the user to
supply all variable values in text fields on a form before pressing the "go"
button that launches the query. Then the firm (should) validate the data and
return error messages if it finds any of the input variable values to be
inappropriate. For example, if the city name was left blank or if the date
supplied was "fred", the query probably should even be attempted since you
know it will produce an unrealistic or empty result. Instead, your program
should tell the user what problems were found and let them modify the
variable values and try again. When you get valid-looking values, you can
then attempt to execute the statement with those values replacing the
placeholders in the query.

There are other approaches to getting the values of the variables but they
are not usually appropriate or would be much less attractive to the user.
For example, the program could ask for the value of each variable *after*
the user has pressed the "go" button but I think most users would prefer to
give the values first, then press the "go" button.

> Further along, when someone wants to save a search ,
> say like on Monster, is that done via a view or a
> "personal" recordset they can call too when needed?
> I'm reading around and trying to determine the best
> way.
What you're describing could be done and sometimes is. For example, an
initial search might turn up all of the hotels in a city on a given date
that had rooms available regardless of the specific location within the city
or the price of the room. A user might very well want to do that to get an
initial sense of the range and variety of rooms available. However, the user
might want to refine their search after getting that overview to get
something a little more specific to their needs. For example, they might
want to search just the set that they got with their initial query but then
filter out rooms over a certain price and hotels that were more than a
certain distance from a given tourist attraction.

It would certainly be possible to save the initial result and then query
that initial result rather than the full table that the initial query
targetted. I'm not sure of the best mechanism to do that in MySQL though
since I've never had to do that in MySQL. DB2 provides for temporary tables
but I don't recall if MySQL has those. I doubt I would use a view for this
purpose, even if you were using a version of MySQL that supported views.
(Remember, MySQL is only just acquiring views in the Version 4.1.x stream,
which is still in beta. Older versions don't have views at all.) If
temporary tables are not available, I would likely use a new table and then
delete it when I know I don't need it any more. However, I'd want to do some
benchmarking before locking in on that solution: it might actually be
cheaper to do the second query against the full table, even though it would
have more rows, than to create and populate a new table and pay for the
additional storage that the second table would need.

You also need to be careful about a proliferation of tables; if you save
every query result for every query ever written, in case the user wants to
run an additional query against that result, HOW LONG DO YOU SAVE THEM? Just
until the user goes to the next screen, after which he starts over? Or do
you assume that they might need to check with their spouse first and that
the spouse might be unavailable at the moment? In the latter case, you'd
likely have to save the result somewhere and save it specifically for that
person because it might be hours or days before they had gotten an answer
from their spouse. You would also have to decide how long you hang on to the
table if the user never comes back to do the second query and then
periodically delete "forgotten" tables, otherwise you'd gradually (or maybe
quickly!) accumulate a large collection of those tables which would fill up
your hard drives over time.

Also, remember that saving a result set for a second query is only going to
be useful if the subsequent query is going after a subset of the data in the
first query result; if it is going after a superset, you'll need to run the
second query against the full table. For example, if the first query asked
for all hotels in Berlin on a certain date and the second query wanted only
luxury Berlin hotels that were downtown on that same date, it might make
sense to save that result set. However, if your second query wanted hotels
in Berlin *and surrounding area* on a larger range of dates, you are going
after a superset and need to query the full table, not the result of the
first query.

> One last item, if my assumptions are correct in the
> passing of variables back to the recordset. In one of
> my tables, I have 5 different fields for say "dog
> types".  dog1 dog2 dog3 dog4 dog5.  Can I just use one
> user input field and then scan all 5 fields in the
> record ?
I'm not 100% clear on what you have in mind here. I'm going to assume that
you mean the *table* has one column for "dog" and that query form you are
using has five input fields in which users can specify the breed of a dog.
(The other possibility is that you have five columns in the table, *each* of
which could contain the breed of a dog. That is a lot less likely because a
properly normalized table should not have a repeating group like that in the
table design under normal circumstances.) If I'm right about the assumption,
the answer to your question is "it depends".

If the query was always going to be something like:
select kennel
from breeders
where breed = 'terrier' or breed = 'poodle' or breed = 'rottweiler' or breed
= 'chihuahua' or breed = 'collie'

you should be fine with having a single field on your input form in which
they could enter a list of breeds. You'd have to give them some rules on how
to enter the data and you'd have to enforce them. For example, you could
insist on columns after each breed name or on putting a separate set of
apostrophes around each breed name. Otherwise, you'd find it potentially
difficult to parse the answers that they gave you. For example, if your
input list was:

terrier poodle german shepherd

you would have trouble recognizing that there are only three breeds in this
list; you would probably treat 'german' and 'shepherd' as two different
breeds when only a single breed 'german shepherd' is intended.

However, things would get much harder for you if your query was sometimes
going to be a list of conditions separated by OR -or- AND, like this:

select kennel
from breeders
where breed = 'terrior' and breed = 'poodle' or breed = 'rottweiler' or
breed = 'chihuahua' and breed = 'collie'

If you put all the breeds in a single input field, you'd have to have some
way of figuring out which of the breeds were intended for ANDing and which
were intended for ORing. That could get quite ugly, although you may figure
out something you could live with. I'd probably have at least two input
fields in that case, one for breeds which should be included in the query
and one for breeds which should be excluded.

> Thank you,
> Stuart
> Sorry for the beginner questions.
There's nothing wrong with not knowing things. We were all beginners once.

> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Dynamic QueriesStuart Felenstein22 Aug
  • Re: Dynamic QueriesRhino22 Aug
    • Re: Dynamic QueriesStuart Felenstein22 Aug
      • Re: Dynamic QueriesStuart Felenstein22 Aug