mysql query - select any

[alex.kemsley]

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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%s' AND
hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_ search

I have only been doing php about a month so go gentle!

Alex

[naixn]

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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%s' AND
hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_ search

I have only been doing php about a month so go gentle!

Alex

Well, I just looked a bit, that should work.
But you know, the best way to know if it works is to test it yourself :)

--
Naixn
http://fma-fr.net

[pangea33]

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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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($tNote ID, $debugMode, $orderBy, $noteTypeID) {
$tNoteID = ( isset($tNoteID) ? $tNoteID : 0 );
$noteTypeID = ( isset($noteType ID) ? $noteTypeID : 0 );
$queryStr = "
SELECT
n.noteID
, n.noteTitle
, n.noteURL
, n.noteText
, n.createDate
, DATE_FORMAT(n.c reateDate, '%d-%b-%Y') as fmtCreateDate
, n.updateDate
, DATE_FORMAT(n.u pdateDate, '%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 = ".$noteType ID;
}

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

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

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

[Moot]

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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.ty pe = '%s'";
$where[] = "hottubs.dimlen gth <= '%s'";
$where[] = "hottubs.dimwid th <= '%s'";
$where[] = "hottubs.dimhig ht <= '%s'";
$where[] = "hottubs.seatst o <= '%s'";
IF ($_GET['shape'] != 'any' ) $where[] = "hottubs.sh ape = '%s'";

$strWhere = '';
if (count($where) 0) $strWhere = " WHERE " . implode(' AND ',
$where);

$qry = "SELECT *
FROM hottubs, manufacturers
" . $strWhere . "
ORDER BY " . $thesearchtype_ search;
- Moot

[alex.kemsley]

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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.m anid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimleng th <= '%s' AND
hottubs.dimwidt h <= '%s' AND hottubs.dimhigh t <= '%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.ty pe = '%s'";
$where[] = "hottubs.dimlen gth <= '%s'";
$where[] = "hottubs.dimwid th <= '%s'";
$where[] = "hottubs.dimhig ht <= '%s'";
$where[] = "hottubs.seatst o <= '%s'";
IF ($_GET['shape'] != 'any' ) $where[] = "hottubs.sh ape = '%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

[naixn]

Moot wrote :

>
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.ty pe = '%s'";
$where[] = "hottubs.dimlen gth <= '%s'";
$where[] = "hottubs.dimwid th <= '%s'";
$where[] = "hottubs.dimhig ht <= '%s'";
$where[] = "hottubs.seatst o <= '%s'";
IF ($_GET['shape'] != 'any' ) $where[] = "hottubs.sh ape = '%s'";

$strWhere = '';
if (count($where) 0) $strWhere = " WHERE " . implode(' AND ',
$where);

$qry = "SELECT *
FROM hottubs, manufacturers
" . $strWhere . "
ORDER BY " . $thesearchtype_ search;
- Moot

In fact, that is cleaner. But the use of implode is what annoys me. When
you get a really big request, it may be, for sure, the cleanest, but surely
not the fastest... Not that it would be a great deal, but well.

But it is clean. I admit, approve, and I'll test and maybe adopt. :p

Thx.

--
Naixn
http://fma-fr.net

[pangea33]

naixn wrote:

Moot wrote :

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.ty pe = '%s'";
$where[] = "hottubs.dimlen gth <= '%s'";
$where[] = "hottubs.dimwid th <= '%s'";
$where[] = "hottubs.dimhig ht <= '%s'";
$where[] = "hottubs.seatst o <= '%s'";
IF ($_GET['shape'] != 'any' ) $where[] = "hottubs.sh ape = '%s'";

$strWhere = '';
if (count($where) 0) $strWhere = " WHERE " . implode(' AND ',
$where);

$qry = "SELECT *
FROM hottubs, manufacturers
" . $strWhere . "
ORDER BY " . $thesearchtype_ search;
- Moot

In fact, that is cleaner. But the use of implode is what annoys me. When
you get a really big request, it may be, for sure, the cleanest, but surely
not the fastest... Not that it would be a great deal, but well.

But it is clean. I admit, approve, and I'll test and maybe adopt. :p

Thx.

--
Naixn
http://fma-fr.net

Agreed, Moot's code is very sleek. Although, one wonders what happens
in the event of an "or" condition. Just teasing cause you got mad
style. ;-)