RE: [PHP-DB] Using PHP to generate SQL statement
| From: | Ford, Mike | Date: | Thu, 23 Sep 2004 12:25:18 +0000 |
| Subject: | RE: [PHP-DB] Using PHP to generate SQL statement | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-36352@lists.php.net to get a copy of this message | ||
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