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

Stefan Hornburg racke at linuxia.de
Fri May 27 13:16:07 EDT 2005


On Fri, 27 May 2005 12:29:36 -0400
"Aaron" <interch at hazenet.net> wrote:

> 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'
> statements
> 
> $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):
> SELECT * 
> 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.

One possibility to trace this down is enable debug within Interchange
and enable DataTrace. This will write all DBI scatter to the debug file.

DebugFile /var/log/interchange/debug.log
DataTrace 1

Bye
	Racke


-- 
LinuXia Systems => http://www.linuxia.de/
Expert Interchange Consulting and System Administration
ICDEVGROUP => http://www.icdevgroup.org/
Interchange Development Team



More information about the interchange-users mailing list