Posted by alex.kemsley on 12/04/06 23:12

Moot wrote:
> alex.kemsley wrote:
> > Hi guys,
> >
> > I have the following sql statemant to search a mysql database that gets
> > if values from a form with combo box's in.
> >
> > SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
> > hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
> > hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
> > hottubs.seatsto <= '%s' AND hottubs.shape = '%s' ORDER BY
> > $thesearchtype_search.
> >
> > Everything works fine except I want to add a select "any" from the
> > shape combo box. I really need a way of cutting out the last " AND
> > hottubs.shape = '%s' " if the $_GET['shape'] = 'any'
> > Will this work using a variable as shown below??
> >
> > IF ($_GET['shape'] != 'any' )
> > {
> > $shape = AND hottubs.shape = '%s'
> > }
> >
> > SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
> > hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
> > hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
> > hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_search
> >
> > I have only been doing php about a month so go gentle!
> >
> > Alex
>
> Personally, I don't like building the where clause for a query all in
> one go like this. I also don't even like building the clause with if's
> and string concatenation. To get the cleanest, easiest where clause
> construction, I typically use an array and implode it at the end.
> Since I usually need to juggle a half-dozen or more conditions which
> may or may not be used in a particular query, this method lets me very
> easily build up which conditions I need set.
>
> So I would do your example in a manner like this:
>
> $where = array();
> $where[] = "manufacturers.manid = hottubs.manid";
> $where[] = "hottubs.type = '%s'";
> $where[] = "hottubs.dimlength <= '%s'";
> $where[] = "hottubs.dimwidth <= '%s'";
> $where[] = "hottubs.dimhight <= '%s'";
> $where[] = "hottubs.seatsto <= '%s'";
> IF ($_GET['shape'] != 'any' ) $where[] = "hottubs.shape = '%s'";
>
> $strWhere = '';
> if (count($where) > 0) $strWhere = " WHERE " . implode(' AND ',
> $where);
>
> $qry = "SELECT *
> FROM hottubs, manufacturers
> " . $strWhere . "
> ORDER BY " . $thesearchtype_search;
>
>
> - Moot

Thanks moot,
I like your style its very neat.
Alex

[Back to original message]