List:General Discussion« Previous MessageNext Message »
From:Don Read Date:March 12 2006 11:27am
Subject:Re: query problem
View as plain text  
On Wed, 8 Mar 2006 10:12:22 -0000 <ross@stripped> wrote:

<snip one column select query>

> but I have two other filters which may or may not be chosen. (area, and 
> interest).
> 
>  $query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND 
> area='area' AND interest='interest' ORDER BY fname $type";
> 
> but what if nether is chosen, or only one? Is there an AND/OR operator or 
> similar in mysql?

Your app needs to build the query.

In my libsql.php file I have:

function andclause($qry, $fld, $val, $op='=') {
    $fmt = " %s %s $op '%s'";

    $qry .= sprintf($fmt,
          ( preg_match('!\bWHERE\b!mi', $qry) ? 'AND' : 'WHERE'), $fld, $val);
    return $qry;
}

With this, you can construct your initial query:
$qry = "SELECT * FROM foo WHERE blah LIKE '$baz%'";

// then test, case by case, to see if you need more selection clauses:

if (! empty($area))
	$qry = andclause($qry, 'area', $area);
if (! empty($interest))
	$qry = andclause($qry, 'interest', $interest);

echo '<span class="ddt">', $qry, '</span>';
$res = SQLQuery($qry);
 ...

 
Have fun.
-- 
Don Read                                     dread@stripped
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
Thread
Data type in MySQL / ASP.netDewald Troskie29 Nov
  • Re: Data type in MySQL / ASP.netStephen Moretti29 Nov
  • query problemross8 Mar
    • Re: query problemPeter Brawley8 Mar
    • Re: query problemDon Read12 Mar