Posted by Moot on 12/04/06 13:23

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

[Back to original message]