RE: [PHP-DB] Using PHP to generate SQL statement

On 23 September 2004 07:47, Ed Lazor wrote:

> I keep looking at the following code and thinking there's
> gotta be a better
> way.  I've been in front of the computer all day tho and I'm drawing
> a blank.  Any ideas?

Seems to me we've just answered a very similar question to this (and I'd be
surprised it there weren't several relevant threads in the list archives).
Nonetheless:

> $sql = "select ID from products where ";
>
> if ($webpage->parameter_isset("CategoryID")) {

Two possible approaches that spring to mind are:


$sql = "select ID from products where 1=1";

if ($webpage->parameter_isset("CategoryID")) {
$sql .= " AND CategoryID = '{$webpage->CategoryID}'";
}

if ($webpage->parameter_isset("CompanyID")) {
$sql .= " AND CompanyID = '{$webpage->CompanyID}'";
}

if ($webpage->parameter_isset("SettingID")) {
$sql .= " AND SettingID = '{$webpage->SettingID}'";
}

if ($webpage->parameter_isset("SystemID")) {
$sql .= "AND SystemID = '{$webpage->SystemID}'";
}

Or:

$where = ''
foreach (array('CategoryID', 'CompanyID', 'SettingID', 'SystemID')
as $field):
if ($webpage->parameter_isset($field)):
$where .= ($where?' AND':'')." $field = '{$webpage->$field}'";
endif;
endforeach;

if ($where):
$sql = "select ID from products where$where";
..
else:
// no where information -- major error
endif;

Cheers!

Mike

---------------------------------------------------------------------
Mike Ford,  Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Headingley Campus, LEEDS,  LS6 3QS,  United Kingdom
Email: m.ford@leedsmet.ac.uk
Tel: +44 113 283 2600 extn 4730      Fax:  +44 113 283 3211

Or you could use a more flexible way, allowing you to easily add other
filters to the query.

$sql = "select ID from products";
if ($webpage->parameter_isset("CategoryID"))
$sql = addFilter($sql,"CategoryID",$webpage->CategoryID);
if ($webpage->parameter_isset("CompanyID"))
$sql = addFilter($sql,"CompanyID",$webpage->CompanyID);
if ($webpage->parameter_isset("SettingID"))
$sql = addFilter($sql,"SettingID",$webpage->SettingID);
if ($webpage->parameter_isset("SystemID"))
$sql = addFilter($sql,"SystemID",$webpage->SystemID);

$sql .= " limit 10";
return $sql;

function addFilter($qry,$fld,$val,$cnd='=') {
if ($qry && $fld) {
if (preg_match("/where/i",$qry)) {
$qry.= ' and';
} else {
$qry.= ' where';
}
$qry.= " $fld $cnd '$val'";
}
return $qry;
}


On Thu, 23 Sep 2004 13:25:18 +0100, Ford, Mike <m.ford@leedsmet.ac.uk> wrote:
> On 23 September 2004 07:47, Ed Lazor wrote:
> 
>
> Seems to me we've just answered a very similar question to this (and I'd b
e
> surprised it there weren't several relevant threads in the list archives).
> Nonetheless:
> 
>
> Two possible approaches that spring to mind are:
>
>  $sql = "select ID from products where 1=1";
>
>  if ($webpage->parameter_isset("CategoryID")) {
>     $sql .= " AND CategoryID = '{$webpage->CategoryID}'";
>  }
>
>  if ($webpage->parameter_isset("CompanyID")) {
>     $sql .= " AND CompanyID = '{$webpage->CompanyID}'";
>  }
>
>  if ($webpage->parameter_isset("SettingID")) {
>     $sql .= " AND SettingID = '{$webpage->SettingID}'";
>  }
>
>  if ($webpage->parameter_isset("SystemID")) {
>     $sql .= "AND SystemID = '{$webpage->SystemID}'";
>  }
>
> Or:
>
>  $where = ''
>  foreach (array('CategoryID', 'CompanyID', 'SettingID', 'SystemID')
>           as $field):
>     if ($webpage->parameter_isset($field)):
>        $where .= ($where?' AND':'')." $field = '{$webpage->$field}'";
>     endif;
>  endforeach;
>
>  if ($where):
>     $sql = "select ID from products where$where";
>     ...
>  else:
>     // no where information -- major error
>  endif;
>
> Cheers!
>
> Mike
>
> ---------------------------------------------------------------------
> Mike Ford,  Electronic Information Services Adviser,
> Learning Support Services, Learning & Information Services,
> JG125, James Graham Building, Leeds Metropolitan University,
> Headingley Campus, LEEDS,  LS6 3QS,  United Kingdom
> Email: m.ford@leedsmet.ac.uk
> Tel: +44 113 283 2600 extn 4730      Fax:  +44 113 283 3211
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

> -----Original Message-----
> Seems to me we've just answered a very similar question to this (and I'd
> be
> surprised it there weren't several relevant threads in the list archives).
> Nonetheless:

I was so tired last night that I don't even remember if I checked the
archives first - my bad.  Thanks for helping Mike.  And thanks to Manuel and
Eduardo.  I'll explore these options and go from there.

-Ed