[ic] building OR statement in mysql for query tag not considering all options

Aaron interch at hazenet.net
Fri May 27 12:29:36 EDT 2005

Hi list..

I am stumped. I am passing (possible) multiple arguments from a multiple
select box to a page and from those arguments, need to create a mysql
statement to pass to the query tag.  I grab the cgi variable, play
around with it to create the structure that I need and the text of my
search looks perfect, no problem.

The problem is that only the FIRST possible match that it should check
actually gets returned.  If I take the already-parsed code and just test
with that query using the same query tag, I get all of the results that
I should.

Here's how the query gets built after checking and processing:

$qry = "SELECT * FROM services WHERE adv_user =
'$Scratch->{comp_adv_user}' AND brand = '$Scratch->{comp_brand}' AND
contract = '$Scratch->{comp_contract}' AND date BETWEEN
'$CGI->{comp_date1}' AND '$CGI->{comp_date2}' AND completed = '1' ";

$qry .= "AND ( type = '$types' ) ";

## $types will include one or more types to look for connected by 'or'

$qry .= "ORDER BY date, store";

Returning this variable to the screen would for example give you the
following query (line breaks added to make it easier to read):
FROM services 
WHERE adv_user = 'Acme Services' 
AND brand = 'General Brands' 
AND contract = 'Acme' 
AND date BETWEEN '20050101' AND '20050527' 
AND completed = '1' 
AND ( type = 'Buff-Floor' 
	OR type = 'Facade-Pressure-Wash' 
	OR type = 'HVAC-Maint.' 
	OR type = 'Lighting-Maint.' 
	OR type = 'Refrigeration-Maint.' 
	OR type = 'Strip-Floor' )
ORDER BY date, store

So only types matching "Buff-Floor" are returned when passing the
variable holding this information to the query tag.  Copying this output
text and entering that directly into the query tag gives results for
other types as it should.  If I remove Buff-Floor from the options
selected on the form on the previous page, then the second option like
'Facade-Pressure-Wash' will be returned and no others.  Only the first
is considered and there are no errors.

IC 5.0.0
Perl 5.8.2

Any ideas please?


More information about the interchange-users mailing list