Received: from socrates.catalyst.net.nz (socrates.catalyst.net.nz [203.96.153.226]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6K0O9a57901 for ; Thu, 19 Jul 2001 20:24:09 -0400 (EDT) (envelope-from andrew@catalyst.net.nz) Received: from heidegger.catalyst.net.nz (hume.catalyst.net.nz [203.96.153.228]) by socrates.catalyst.net.nz (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id MAA12048; Fri, 20 Jul 2001 12:24:01 +1200 X-Authentication-Warning: socrates.catalyst.net.nz: Host hume.catalyst.net.nz [203.96.153.228] claimed to be heidegger.catalyst.net.nz Received: from 127.0.0.1 (ident=andrew) by heidegger.catalyst.net.nz with esmtp (MasqMail 0.1.14) id 15NO4k-18R-00; Fri, 20 Jul 2001 12:23:26 +1200 Message-ID: <3B5779FE.74C87025@catalyst.net.nz> Date: Fri, 20 Jul 2001 12:23:26 +1200 From: Andrew McMillan Organization: Catalyst IT Ltd X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.4-win4lin-kant i686) X-Accept-Language: en MIME-Version: 1.0 To: anepon@verveinternet.com CC: pgsql-php@postgresql.org Subject: Re: A function for building a where clause. References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/16 X-Sequence-Number: 473 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