Re: Using PHP to generate SQL statement
| From: | Eduardo Sampaio | Date: | Thu, 23 Sep 2004 12:58:03 +0000 |
| Subject: | Re: Using PHP to generate SQL statement | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-36354@lists.php.net to get a copy of this message | ||
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:
>
> > 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
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>