RE: [PHP] Building a query on multiple variables, how to test for which variable

2002-08-29 Thread @ Edwin 
Does this fit your idea of a "more elegant solution"?

This is just an enhanced and tested version of the one I posted earlier 
(somehow I had time and was curious about it).

?php
  
if (isset($_POST['var'])){ // if POSTed

  // initial query
  // make sure "WHERE" not included when none is selected
  $qpon = "SELECT BillDate, StateInd, BAN, Type, PON, Phrase,";
  $qpon .= " PhraseLine1, USOC, Description, RateZone, Rate";
  $qpon .= " FROM tblUSOCChargesDetail";

  $its_set = 1; // my super "switch"

  foreach ($_POST['var'] as $key = $value){ // walk
if ($value != 0){ // make sure the "--- Select ---" is not included
  if ($its_set == 1){
$qpon .= " WHERE USOC = '" . $value . "'";
$its_set = 2; // just to make sure "WHERE" is only added once
  } else {
$qpon .= " AND USOC = '" . $value . "'";
  }
}
  } // end of foreach

}

  $qpon .= " ORDER BY BillDate";
  echo "$qpon"; // Check the generated query

?

And this is what I used for testing:

// html headers here

form action="testpulldown.php" method="post"
First option
select name="var[]"
  option value="0"--- Select ---/option
  option value="1a"1a/option
  option value="1b"1b/option
  option value="1c"1c/option
/select
br /
Second option
select name="var[]"
  option value="0"--- Select ---/option
  option value="2a"2a/option
  option value="2b"2b/option
  option value="2c"2c/option
/select
br /
Third option
select name="var[]"
  option value="0"--- Select ---/option
  option value="3a"3a/option
  option value="3b"3b/option
  option value="3c"3c/option
/select
br /
Fourth option
select name="var[]"
  option value="0"--- Select ---/option
  option value="4a"4a/option
  option value="4b"4b/option
  option value="4c"4c/option
/select
br /
input type="submit" name="submit" value="Submit" /
/form

// end of html

Put them in the same file and name the file "testpulldown.php". (Of course, 
you know...)

The "beauty", I thought is, the query would still work even though none of 
the "pull-downs" were selected--of course, I don't how much data will be 
shown...

NOTE: You can even have MORE pull-downs with this kind of approach. (...and 
less code...)

BTW, who is "Todd"? I didn't see his post... Just curious...

- E



Todd came in with a good suggestion which I had started towards, so this 
is
what I have arrived at for the moment. I will be looking to make this more
elegant soon, but since they are screaming for the report now this is what 
I
did;

?php
   if($usoc1  "--- Select ---"){
   $usoc[] = $usoc1;
   }
   if($usoc2  "--- Select ---"){
   $usoc[] = $usoc2;
   }
   if($usoc3  "--- Select ---"){
   $usoc[] = $usoc3;
   }
   if($usoc4  "--- Select ---"){
   $usoc[] = $usoc4;
   }
   $usoc_count = count($usoc);

   // query details
   $qpon = "SELECT BillDate, StateInd, BAN, Type, PON, Phrase, PhraseLine1,
USOC, Description, RateZone, Rate ";
   $qpon .= "FROM tblUSOCChargesDetail WHERE ";
   $first = 0;
   for($i = 0; $i  $usoc_count; $i++){
   if($first == 0){
   $qpon .= "USOC = '" . $usoc[$i] . "' ";
   $first = 1;
   } else {
   $qpon .= "AND USOC = '" . $usoc[$i] . "' ";
   }
   }
   $qpon .= "ORDER BY BillDate ";
   if(!($dbpon = mysql_query($qpon, $dbconnect))){
   print("MySQL reports: " . mysql_error() . "\n");
   exit();
   }
?

I am sure that there is a more elegant solution, if I find it I will let 
you
know.

Thanks!

Jay



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php





_
かわいくて愉快なイラスト満載 MSN キャラクター http://character.msn.co.jp/


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Building a query on multiple variables, how to test for which variable

2002-08-28 Thread @ Edwin 
There must be a better way how to do it but this is what I would do: (Not 
tested--just the "idea"...)

Instead of $var1, $var2... I'd use an array for the drop downs thus,

  $var[]
  $var[]
  $var[]
  $var[]

Then, check if any is set, then... anyway, this is how it goes:

  // from here
  $query = "SELECT foo ";
  $query .= "FROM bar";
  if (isset($_POST['var']){  // if POSTed
$its_set = 1; // my super "switch"
foreach ($var as $key = $value){
  if ($its_set == 1){
$query .= " WHERE thus = '" . $value . "'";
$its_set = 2; // just to make sure "WHERE" is only added once
  } else {
$query .= " AND thus = '" . $value . "'";
  }
}
  }

HTH,

-E


So these 4 variables walk into a bar :^]

I have a form with 4 drop downs. Each of these drop downs might have a 
value
and there is no order in which they might have a value. A query needs to
built (the WHERE and AND portions) that takes into account which ever
variables have been set. A user may choose a variable that would not be in
the first location.

$var1 = is not set
$var2 = is set
$var3 = is set
$var4 = is not set

(or any other combination) The query, of course, needs to be in order 
taking
into account the above example;

$query = "SELECT foo ";
$query .= "FROM bar ";
$query .= "WHERE thus = '" . $var2 . "' ";
$query .= "AND thus = '" . $var3 . "' ";

If three of the variables are set then there would need to be an 
additional
AND statement, etc. It would be foolhardy to construct an if or case
statement that would take into account each possible combination. I have
been thinking this through for an hour or so, but I am not seeing a clear,
elegant, and efficient piece of code. Has anyone done anything like this, 
or
does anyone have an idea of how this might be done?

TIA!

Jay

*
* Texas PHP Developers Conf  Spring 2003*
* T Bar M Resort  Conference Center*
* New Braunfels, Texas  *
* Contact [EMAIL PROTECTED]   *
*   *
* Want to present a paper or workshop? Contact now! *
*



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




_
ウィルスメール、迷惑メール対策なら MSN Hotmail http://www.hotmail.com/JA


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Building a query on multiple variables, how to test for which variable A SOLUTION

2002-08-28 Thread Jay Blanchard 
Todd came in with a good suggestion which I had started towards, so this is
what I have arrived at for the moment. I will be looking to make this more
elegant soon, but since they are screaming for the report now this is what I
did;

?php
if($usoc1  "--- Select ---"){
$usoc[] = $usoc1;
}
if($usoc2  "--- Select ---"){
$usoc[] = $usoc2;
}
if($usoc3  "--- Select ---"){
$usoc[] = $usoc3;
}
if($usoc4  "--- Select ---"){
$usoc[] = $usoc4;
}
$usoc_count = count($usoc);

// query details
$qpon = "SELECT BillDate, StateInd, BAN, Type, PON, Phrase, PhraseLine1,
USOC, Description, RateZone, Rate ";
$qpon .= "FROM tblUSOCChargesDetail WHERE ";
$first = 0;
for($i = 0; $i  $usoc_count; $i++){
if($first == 0){
$qpon .= "USOC = '" . $usoc[$i] . "' ";
$first = 1;
} else {
$qpon .= "AND USOC = '" . $usoc[$i] . "' ";
}
}
$qpon .= "ORDER BY BillDate ";
if(!($dbpon = mysql_query($qpon, $dbconnect))){
print("MySQL reports: " . mysql_error() . "\n");
exit();
}
?

I am sure that there is a more elegant solution, if I find it I will let you
know.

Thanks!

Jay



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Building a query on multiple variables, how to test for which variable A SOLUTION

2002-08-28 Thread Joseph W. Goff 
I have done this two different ways.
The first involves a function that creates a dropdown from the database.
What is special about this function is that I assign it's own condition as
part of the value for that option.  Take this sql statement: select distinct
column_name from the_table
and it would generate this for a select statement:
select name="column_name"
option value=" "All/option
option value=" and column_name='some_value'"some_value/option
option value=" and column_name='another_value'"another_value/option
option value=" and
column_name='a_different_value'"a_different_value/option
/select

Then, when the form is processed, the sql statement would be as follows:
$sql="select whatever_columns from the_table where 1=1$column_name";

The second method is somewhat similar in that the sql statement would remain
the same, but you would have to check each select value to see if you needed
to add a "and column_name ='$column_value'" to it.  i.e.
select statement would be like:
select name="column_name"
option value=" "All/option
option value="some_value"some_value/option
option value="another_value"another_value/option
option value="a_different_value"a_different_value/option
/select

processing would be like:
if (trim($column_name))
$column_name=" and column_name='$column_name'";
$sql="select whatever_columns from the_table where 1=1$column_name";

The important part on this is that you don't have to be concerned with
whether or not to use and or where on the statement, always use and, and in
your sql statement put a where clause that is always true.  i.e. where 1=1.


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php