Craig Hoffman <[EMAIL PROTECTED]> wrote on 01/18/2005 04:17:30 PM:
> Hi There,
> I have web form where a user can search certain fields and then have
> them displayed aggregated. For example, find all the routes I climbed
> with partner A in area(s) ALL (% - wildcard) between date1 and date2 --
> so on. See below for the complete query.
>
> I'm using pull down menu's and when a the user does not select
> something the default is ALL or <option value='%'> ALL </option> for
> all. Then all the other <options> are listed. Should I be using the
> % as a wildcard? I would like it to work if one, two, three... or all
> fields are selected. Obviously, the more options you select the more
> detailed your search becomes and vice versa. Any thoughts on what
> could be wrong with my query? Any help would be appreciated.
>
> Thanks - Craig
>
> Here is my query
> query = "SELECT routes.*, users.email, users.fname, users.lname,
> users.user_id, ranking.* FROM routes, users, ranking WHERE
> email='$email' AND area LIKE '%$area%'
> AND partner LIKE '%$partner%'
> AND id BETWEEN '$rating1' AND '$rating2'
> AND additional_rating IS NOT NULL LIKE '%$additional_rating%'
> AND pitchs LIKE '%$pitchs%'
> AND `type` LIKE '%$type%'
> AND style LIKE '%$style%'
> AND stars LIKE '%$stars%'
> AND fall LIKE '%$fall%'
> AND popular LIKE '%$popular%'
> AND date_climbed BETWEEN '$date_climbed1' AND '$date_climbed2'
> AND routes.rating = ranking.rating
> AND routes.user_id = users.user_id
> GROUP BY route_count ORDER BY area, date_climbed DESC";
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
Rather than construct the query all at once, as you are doing, I would
suggest you conditionally check for each search condition then add those
conditions to the WHERE statement if they exist. Also, if you don't give
the user the opportunity to enter their own values (as in your Area SELECT
box) use an = not a LIKE, you will have a better chance of using an index
if you do.
I am VERY noob with PHP (if this even is PHP) so please forgive my
horribly wrong syntax :)
$query = "SELECT routes.*, users.email, users.fname, users.lname,
users.user_id, ranking.* FROM routes, users, ranking WHERE
email='$email'";
if ($area > '') {
$query += " AND area='$area'";
}
if ($partner > '') {
$query += " AND partner LIKE '$partner'";
}
... the other tests for your other search fields ...
$query += " ORDER BY route_count, area, date_climbed DESC";
That way you only query on the fields the user enters and not every value
every time. This way, too, the user can decide if they want an exact
match, or some kind of partial match as LIKE will default to = if there
isn't a % in the comparator.
Also, you only need a GROUP BY if you are going to do some kind of
aggregation (sum, avg, max, min, etc...) but you weren't in this query so
I moved that term to the ORDER BY clause.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
