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

[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

[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