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

[Ed Lazor]

> -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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

[Eduardo Sampaio]

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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> 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
> 
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

[Ford, Mike]

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: [EMAIL PROTECTED]
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