Thread: A function for building a where clause.

A function for building a where clause.

From
"Ari Nepon"
Date:
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.

Thanks,

Ari


~~~~~~~~~~~~~~~~~~~~~~~~~
Ari Nepon
MRB Communications
4520 Wilde Street, Ste. 2
Philadelphia, PA 19127
p: 215.508.4920
f: 215.508.4590
http://www.mrbcomm.com

-----------------------------------------------------------------------
Sign up for our email list and receive free information about
topics of interest to nonprofit communications, marketing, and
community building professionals. Free resources, articles, tips.
Go to http://www.mrbcomm.com and use the Mailing List form.
-----------------------------------------------------------------------


Re: A function for building a where clause.

From
Timothy_Maguire@hartehanks.com
Date:
I would not include the WHERE and AND statements in your snippet here.
Take all the "table.field = '$some_var'" and push them into an array and
then create the query on the fly and plug the WHERE and AND as you go.

for example,
$query_array = array();
if (!empty($f_you))
{
     array_push($query_array, "tbl_all.employee_id = '$f_you'");
}
if (!empty($next_var))
{
     array_push($query_array, "tbl_all.somefield = '$some_var'");
}
...and so on.....

then take that array and plug the WHERE and AND in.

$query = "SELECT * FROM tbl_all WHERE ";
for($i=0;$i<sizeof($query_array);$i++)
{
     $query .= $query_array[$i];
     if($i < sizeof($query_array) - 1)
     {
          // this will plug the AND into the $query inbetween all the array
elements except for the last one
          $query .= " AND ";
     }
}

There could be a better way to do this.  I would be interested in any other
solutions.

Tim.

Timothy P. Maguire
Web Developer II
Harte-Hanks
978 436 3325




                    "Ari Nepon"
                    <anepon@verveinterne       To:     <pgsql-php@postgresql.org>
                    t.com>                     cc:
                    Sent by:                   Subject:     A function for building a where clause.
                    pgsql-php-owner@post
                    gresql.org


                    07/19/01 12:46 PM
                    Please respond to
                    anepon






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.

Thanks,

Ari


~~~~~~~~~~~~~~~~~~~~~~~~~
Ari Nepon
MRB Communications
4520 Wilde Street, Ste. 2
Philadelphia, PA 19127
p: 215.508.4920
f: 215.508.4590
http://www.mrbcomm.com

-----------------------------------------------------------------------
Sign up for our email list and receive free information about
topics of interest to nonprofit communications, marketing, and
community building professionals. Free resources, articles, tips.
Go to http://www.mrbcomm.com and use the Mailing List form.
-----------------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly




Re: A function for building a where clause.

From
Andrew McMillan
Date:
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

how to determine where a select statement fails

From
"Heather Johnson"
Date:
I am using php to do a select query which returns rows on the condition that
a conjunction is true in the WHERE clause. This is the SELECT statement:

SELECT low_range, high_range, st_name, city, zip FROM router
WHERE st_name = '$st_name' AND city = '$city' AND zip = '$zip';

In the event that the query fails to return any rows, I'd like to be able to
determine which conjunct caused it to fail. So, for example, if the
user-entered $st_name isn't in the router table, I'd like to know that
st_name = '$st_name' is what made the conjunction false and caused the query
to fail. $pg_errormsg isn't this specific about query failures though. Does
anyone know how I might be able to get this information?

Thanks!
Heather Johnson


Re: how to determine where a select statement fails

From
"Adam Lang"
Date:
I really don't think that is SQL's job.  I think you have to code conditions
like that yourself.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Heather Johnson" <hjohnson@nypost.com>
To: <pgsql-php@postgresql.org>
Sent: Thursday, July 26, 2001 11:15 AM
Subject: [PHP] how to determine where a select statement fails


> I am using php to do a select query which returns rows on the condition
that
> a conjunction is true in the WHERE clause. This is the SELECT statement:
>
> SELECT low_range, high_range, st_name, city, zip FROM router
> WHERE st_name = '$st_name' AND city = '$city' AND zip = '$zip';
>
> In the event that the query fails to return any rows, I'd like to be able
to
> determine which conjunct caused it to fail. So, for example, if the
> user-entered $st_name isn't in the router table, I'd like to know that
> st_name = '$st_name' is what made the conjunction false and caused the
query
> to fail. $pg_errormsg isn't this specific about query failures though.
Does
> anyone know how I might be able to get this information?
>
> Thanks!
> Heather Johnson
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


Re: how to determine where a select statement fails

From
"Brent R. Matzelle"
Date:
--- Heather Johnson <hjohnson@nypost.com> wrote:
> I am using php to do a select query which returns rows on the
> condition that
> a conjunction is true in the WHERE clause. This is the SELECT
> statement:
>
> SELECT low_range, high_range, st_name, city, zip FROM router
> WHERE st_name = '$st_name' AND city = '$city' AND zip =
> '$zip';
>
> In the event that the query fails to return any rows, I'd like
> to be able to
> determine which conjunct caused it to fail.

I'm not sure if I understand what your problem is.  If a query
does not return any rows (pg_numrows() < 1) then that is not
really a "failure".  If the SQL was incorrect then I would
suggest printing it to the browser (echo $query) and pasting it
in a query program like psql or winsql to determine which line
of code was incorrect.

Brent

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Re: Re: how to determine where a select statement fails

From
"Adam Lang"
Date:
Basically, the person wants to know which where clause caused the query to
return 0 results.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Brent R. Matzelle" <bmatzelle@yahoo.com>
To: "Heather Johnson" <hjohnson@nypost.com>; <pgsql-php@postgresql.org>
Sent: Thursday, July 26, 2001 2:05 PM
Subject: [PHP] Re: how to determine where a select statement fails


> I'm not sure if I understand what your problem is.  If a query
> does not return any rows (pg_numrows() < 1) then that is not
> really a "failure".  If the SQL was incorrect then I would
> suggest printing it to the browser (echo $query) and pasting it
> in a query program like psql or winsql to determine which line
> of code was incorrect.
>
> Brent



Re: A function for building a where clause.

From
"Gyozo Papp"
Date:
Hello,

the best I can suggest to you is to gather your search conditions into an array and use some magic array function such
asjoin. Look what I'm talking about: 

         if(!empty($f_you))
             $sort[] = "tbl_all.employee_ID = \"$f_you\"";
         if(!empty($f_clients))
             $sort[] = "tbl_all.client_ID = \"$f_clients\"";
         if(!empty($f_project))
             $sort[] = "tbl_all.project_ID = \"$f_project\"";
         if(!empty($f_task))
             $sort[] = "tbl_all.task_ID = \"$f_task\";
         if(!empty($f_description))
             $sort[] = "tbl_all.description LIKE '$f_description'";
         if(!empty($f_hours))
             $sort[] = "bl_all.hours = \"$f_hours\"";
         if(!empty($f_date))
             $sort[] = "tbl_all.date LIKE \"$f_project\"";
        $finalsort = join(' AND ', $sort);
        echo "final sort:$finalsort";
        return $finalsort;

If you have to use such functions in a lot of place over in your site, you may arrange a co-called controll-array which
tellsyour function what to do with what variables. This array holds information about what variable is bound to what
columnof the table 

example (this is only a scratch not tested and not full-featured :)
<?php
$search = array(
    array ('var' => 'f_you', 'col' => 'employee_ID', 'rel' =>'='),
 /* ... other variable-column pairs for $f_clients, $f_project, $f_task ... */
    array ('var' => 'f_description', 'col' => 'description', 'rel' =>' LIKE '));
    array ('var' => 'f_hours', 'col' => 'hours', 'rel' => '='),

/* $ctl is the control array,
 * $scope is the array which contains the values to be substituted
 *  with unique keys such as variable names. ie.: $GLOBALS
 */
function make_where(&$ctl, &$scope)
{
    foreach ($ctl => $row)
    {
        $value = $scope[$row['var']];
        $conditions[] = $row['col'].' '.$row['rel'].' '.$value;
    }

    return 'WHERE '.join(' AND ', $conditions);
}
?>