Re: [PHP-DB] Using PHP to generate SQL statement
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] mysql to postgres migration blues...
Its because the function cannot access your connection object...
Try passing it to the function..
function makelistboxentries($link, $title, $mytable, $mycolumn, $othertable,
$wherestring)
On Thu, 23 Sep 2004 19:44:51 +0200, Antoine [EMAIL PROTECTED] wrote:
Hi,
I am trying to get skilled up on postgres and decided to port a little
movie database I wrote for mysql with php to access it.
I used an auto converter for the bulk and then tried to tweak. The
following code does not work but the error message it gives me is
strange. It tells me that the connection string used on line 82 (where
it calls pg_query in makelistboxentries) is invalid... but the same
string/connection works just great in another script... any pointers
appreciated.
Cheers
Antoine
?php
/* Connect to database */
$link = pg_connect(dbname=movies host=localhost user=anton
password=password)
or die(Could not connect : . pg_result_error($link));
print TII
div id=mybody
Search by:
BR
TABLE border=1 cellpadding=10 cellspacing=10TR
TD align=center
ID:
form name=input action=searchout.php method=post
input type=text name=mychoice
input type=hidden name = wherestring value= t1.ID =
input type=hidden name = othertable value=movies
input type=submit value=Submit
/form
/TD
TD align=center
Name:
form name=input action=searchname.php method=post
input type=text name=mychoice
input type=submit value=Submit
/form
/TD/TR
/TABLEBR
TABLE border=1 cellpadding=10 cellspacing=10TR
TII;
makelistboxentries(Audio Language, lang, lang, audio ,
t2.movie = t1.ID and t2.lang = );
makelistboxentries(Subtitle Language, lang, lang, subtitles ,
t2.movie = t1.ID and t2.lang = );
makelistboxentries(Genre, Genre, Genre, moviegenre ,
t2.movie = t1.ID and t2.genre = );
print TINI
/TD
/TR/TABLE
brbr
PShow All:/P
form name=input action=allout.php
method=post
input type=submit value=Submit
/form
/div
div id=mymenu
PA href=index.phpH3Back to main page/H3/A/P
PA href=movie.phpAdd a movie/A/P
Pa href=audio.phpAdd a movie's audio languages/a/P
Pa href=subtitles.phpAdd a movie's subtitle languages/a/P
Pa href=searchin.phpSearch the movies/a/P
/div
TINI;
/* Close connection */
pg_close($link);
function makelistboxentries($title, $mytable, $mycolumn, $othertable,
$wherestring)
{
print TD align=\center\;
print $title:;
/* Perform SQL query */
$query = SELECT $mycolumn FROM $mytable;
$result = pg_query($link,$query)
or die(Query failed : . pg_result_error($link));
print form name=\input\ action=\searchout.php\ method=\post\;
print SELECT NAME=\mychoice\;
print option value=\zz\ SELECTEDSelect $title/option;
while ($line = pg_fetch_array($result,$result_countt++,PGSQL_ASSOC)) {
foreach ($line as $col_value) {
print (OPTION VALUE=\.$col_value.\);
print $col_value;
print (/OPTION);
}
}
print /select;
print input type=hidden name = wherestring value=\$wherestring\;
print input type=hidden name = othertable value=$othertable;
print input type=\submit\ value=\Submit\;
print /form;
pg_free_result($result);
}
?
--
G System, The Evolving GUniverse - http://www.g-system.at
--
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
