Posted by pangea33 on 12/04/06 04:27

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

I don't really see how this works when using %s in multiple places.
Personally I like to construct the querystring as a variable using
conditional logic in php, then pass the variable to MySQL. Here's an
example since I'm a little unclear like I said...

function getNotes($tNoteID, $debugMode, $orderBy, $noteTypeID) {
$tNoteID = ( isset($tNoteID) ? $tNoteID : 0 );
$noteTypeID = ( isset($noteTypeID) ? $noteTypeID : 0 );
$queryStr = "
SELECT
n.noteID
, n.noteTitle
, n.noteURL
, n.noteText
, n.createDate
, DATE_FORMAT(n.createDate, '%d-%b-%Y') as fmtCreateDate
, n.updateDate
, DATE_FORMAT(n.updateDate, '%d-%b-%Y') as fmtupdateDate
, n.noteTypeID
, nt.noteType
FROM
notes n
LEFT JOIN noteTypes nt ON n.noteTypeID = nt.noteTypeID
WHERE
0=0";
if ( $noteTypeID > 0) {
$queryStr = $queryStr." AND n.noteTypeID = ".$noteTypeID;
}

if ( $tNoteID > 0) {
$queryStr = $queryStr." AND n.noteID = ".$tNoteID;
}

$orderBy = ( strlen($orderBy) > 0 ? $orderBy : "nt.noteType,
n.noteTitle desc");
$queryStr = $queryStr." ORDER BY ".$orderBy;

if ( $debugMode ) {
echo "<br>qryDtl: ".$queryStr."<br>";
}
return mysql_query($queryStr);

[Back to original message]