Re: A function for building a where clause. - Mailing list pgsql-php

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@catalyst.net.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