From: Andrew McMillan <andrew@catalyst.net.nz>
To: anepon@verveinternet.com
Cc: pgsql-php@postgresql.org
Subject: Re: A function for building a where clause.
Date: Fri, 20 Jul 2001 12:23:26 +1200
Message-ID: <3B5779FE.74C87025@catalyst.net.nz> (raw)
References: <JMEEKPBGBOBMKNANAGILKECACEAA.anepon@verveinternet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: andrew@catalyst.net.nz, anepon@verveinternet.com
Subject: Re: A function for building a where clause.
In-Reply-To: <3B5779FE.74C87025@catalyst.net.nz>
References: <JMEEKPBGBOBMKNANAGILKECACEAA.anepon@verveinternet.com> <3B5779FE.74C87025@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox