Re: A function for building a where clause.

Ari Nepon wrote:
>
> Does anyone know of a function that I could use to take form data and build
> a where clause for my SQL query based on their choices (which can be from
> text fields, memo fields, dropdowns of numbers or letters, and radio buttons
> or check boxes).
>
> Here is something I wrote, but i know is not correct, and would fail unless
> the first choice is chosen. But it gives an idea of what I am trying to do.
>
> function searchparams()
> {
> global $f_you;
> global $f_clients;
> global $f_project;
> global $f_task;
> global $f_description;
> global $f_hours;
> global $f_date;
>
> if(!empty($f_you))
> $sort1="WHERE tbl_all.employee_ID = \"$f_you\"";
> if(!empty($f_clients))
> $sort2="AND tbl_all.client_ID = \"$f_clients\"";
> if(!empty($f_project))
> $sort3="AND tbl_all.project_ID = \"$f_project\"";
> if(!empty($f_task))
> $sort4="AND tbl_all.task_ID = \"$f_task\";
> if(!empty($f_description))
> $sort5="AND tbl_all.description LIKE '$f_description'";
> if(!empty($f_hours))
> $sort6="AND bl_all.hours = \"$f_hours\"";
> if(!empty($f_date))
> $sort7="AND tbl_all.date LIKE \"$f_project\"";
> $finalsort="$sort1 $sort2 $sort3 $sort4 $sort5 $sort6 $sort7";
> echo "final sort:$finalort $f_project";
> return $finalsort;
>
> }
>
> Problem with this function is that I am getting a where clause that has AND
> sometable.somecolumn="" and its screwing up my query.

In cases like these I add a trivially true clause on there, if it seems the right
thing to do, so that there is always a "WHERE a = b" and then the optional clauses
can all start with "AND ...".

Alternatively (if it seems it will work better) I just build the clauses as "AND
..." and then cut off the first four characters and replace with "WHERE ":
$where = "WHERE " . substr( $where, 4);

I actually find the first method works well because in most cases I am doing a join
between two tables and can simply have the joined fields as the first criteria.

Here's an actual piece of code from one of my more complicated ones:

$query = "SELECT request_id, brief, fullname, email, lookup_desc AS status_desc,
last_activity, detailed ";
$query .= "FROM request, usr, lookup_code AS status ";

$query .= " WHERE request.request_by=usr.username ";
if ( "$inactive" == "" ) $query .= " AND active ";
if (! ($roles['wrms']['Manage'] || $roles['wrms']['Admin']) )
$query .= " AND org_code = '$session->org_code' ";
else if ( "$org_code" != "" )
$query .= " AND org_code='$org_code' ";

if ( "$user_no" <> "" )
$query .= " AND requester_id = $user_no ";
else if ( "$interested" <> "" )
$query .= " AND request_interested.request_id=request.request_id AND
request_interested.user_no = $interested ";
else if ( "$allocated_to" <> "" )
$query .= " AND request_allocated.request_id=request.request_id AND
request_allocated.allocated_to_id = $allocated_to ";

if ( "$search_for" != "" ) {
$query .= " AND (brief ~* '$search_for' ";
$query .= " OR detailed ~* '$search_for' ) ";
}
if ( "$system_code" != "" ) $query .= " AND system_code='$system_code' ";
if ( "$type_code" != "" ) $query .= " AND request_type=" .
intval($type_code);
error_log( "type_code = >>$type_code<<", 0);

if ( "$from_date" != "" ) $query .= " AND
request.last_activity>='$from_date' ";

if ( "$to_date" != "" ) $query .= " AND request.last_activity<='$to_date' ";

if ( isset( $incstat) ) {
$query .= " AND (request.last_status ~* '[";
while( list( $k, $v) = each( $incstat ) ) {
$query .= $k ;
}
$query .= "]') ";
}

$query .= " AND status.source_table='request' AND
status.source_field='status_code' AND status.lookup_code=request.last_status ";
$query .= " ORDER BY request_id DESC ";
$query .= " LIMIT 100 ";
$result = awm_pgexec( $wrms_db, $query, "requestlist", false, 7 );

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709