RE: [PHP-DB] Advice On Building Dynamic MySQL Queries
I actually created an array of different conditions like:
$ConditionsArray = array("Var1 500","Var2 1800","ID=43");
and then:
$Conditions = join(" AND ", $ConditionsArray);
This constructs:
$Conditions = "Var1 500 AND Var2 1800 AND ID=43";
shrug Worked well for me.
Jonathan Hilgeman
-Original Message-
From: JD Daniels [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 19, 2001 10:41 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Advice On Building Dynamic MySQL Queries
I am building a query from a search form. This is the code I have now:
if (!empty($whereclause))
{
$whereclause=urldecode($whereclause);
$whereclause=stripslashes($whereclause);
}
extract($HTTP_POST_VARS);
if (!empty($Price))
{
if(empty($whereclause))
{
$whereclause.="Price$Price";
}
else
{
$whereclause.=" AND 'Price $Price";
}
}
if (!empty($Area))
{
if(empty($whereclause))
{
$whereclause.="Area LIKE '$Area'";
}
else
{
$whereclause.=" AND Area LIKE '$Area'";
}
}
if (!empty($MLS_Number))
{
if(empty($whereclause))
{
$whereclause.="MLS_Number LIKE
'$MLS_Number'";
}
else
{
$whereclause.=" AND MLS_Number LIKE
'$MLS_Number'";
}
}
if (!empty($File_Number))
{
if(empty($whereclause))
{
$whereclause.="File_Number LIKE
'$File_Number'";
}
else
{
$whereclause.=" AND File_Number LIKE
'$File_Number'";
}
}
if (!empty($Description))
{
if(empty($whereclause))
{
$whereclause.="Description LIKE
'%$Description%'";
}
else
{
$whereclause.=" AND Description LIKE
'%$Description%'";
}
}
$query="SELECT
lid,Price,Address,Area,Description,File_Number,Realtor_First_name FROM
listings LEFT JOIN areas ON (listings.aid=areas.aid) LEFT JOIN realtors ON
(listings.rid1=realtors.rid) LEFT JOIN prop_types ON
(listings.ptid=prop_types.ptid) LEFT JOIN prop_styles ON
(listings.psid=prop_styles.psid) WHERE $whereclause order by Price ASC";
#echo "$querybr";
if(empty($whereclause))
{
echo"centerh2You Did Not Specify Anything To Search
For!/h2br
a href=\"javascript:history.back()\"Back/a/center";
}
else
{
$results=$CONNECTION-Execute("$query") or
DIE($CONNECTION-ErrorMsg());
$results__numRows=0;
$results__totalRows=$results-RecordCount();
echo $results-Fields("aid");
echo "$querybrbr";
while ($results__numRows$results__totalRows)
{
#SNIP Display stuff
$results__numRows++;
$results-MoveNext();
}
$results-Close();
}
It Just became apparent that I am going to need to add a whole bunch more
search criteria.. Does anyone have any advice/suggestions for getting rid of
all those if statements?
JD
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Advice On Building Dynamic MySQL Queries
Thanks for the replies :)
Victor's suggestion put me on the right track. (I still have trouble with
language constructs)
Heres what I have now.. All I need to do is name my form fields with the
same name as the column names from the mysql table, and presto! now I can
just add/remove from the search form without changing the code. (I hope.. It
has worked so far).
while (list ($key, $val) = each ($HTTP_POST_VARS))
{
if (!is_int($key) $key!="Submit" $key!="submit"
$key!="offset")
{
if (!empty($val))
{
if (!empty($whereclause))
{
if($key=="Price")
{
$whereclause.=" AND Price$val";
}else{
$whereclause.=" AND $key='$val'";
}
}
else
{
if($key=="Price")
{
$whereclause.=" Price$val";
}else{
$whereclause.=" $key='$val'";
}
}
}
}
}
Thnaks
JD
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Advice On Building Dynamic MySQL Queries
Hello JD,
on 19.04.2001 you wrote:
I am building a query from a search form. This is the code I have now:
if (!empty($whereclause))
{
$whereclause=urldecode($whereclause);
$whereclause=stripslashes($whereclause);
}
extract($HTTP_POST_VARS);
if (!empty($Price))
{
if(empty($whereclause))
--- blah blah blah ---
It Just became apparent that I am going to need to add a whole bunch more
search criteria.. Does anyone have any advice/suggestions for getting rid of
all those if statements?
JD
what about using a foreach statement and going through all the
variables you need like this ?
$myvars = array('Price', 'Stuff', 'Thing');
foreach ($myvars as $varname) {
if (!empty($$varname)) {
// do funky stuff here
}
}
HTH
Victor
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Advice On Building Dynamic MySQL Queries
My solution was almost as elegent...
$query="select * from customers";
$queryand=false;
if(!empty($f_Account)) {
$query.=" WHERE upper(P.Account) like upper('$f_Account')";
$queryand=true;
}
if(!empty($f_First_Name)) {
$query.=($queryand?" AND ":" WHERE ")."upper(p.First_Name) like
upper('$f_First_Name')";
$queryand=true;
}
...
For searchs, case doesn't matter (in my example), but you do need to index
your table accordingly for best results.
Also note upper() is the oracle uppercase function. I believe the uppercase
function varies from one RDBMS to another.
"Victor Foitzik" [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
Hello JD,
on 19.04.2001 you wrote:
if (!empty($Price))
{
if(empty($whereclause))
{
$whereclause.="Price$Price";
}
else
{
$whereclause.=" AND 'Price $Price";
}
}
and even better would be:
if (!empty($$varname)) {
$whereclause .= ($whereclause ? ' AND ' : '').
"$varname =" . $$varname;
}
or something like this ;-)
HTH
Vic
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
