[ic] SQL query returning no results

[Daniel Davenport]

> -----Original Message-----
> From: interchange-users-bounces at icdevgroup.org 
> [mailto:interchange-users-bounces at icdevgroup.org] On Behalf 
> Of ic at 3edge.com
> Sent: 2006 October 27 -- Friday 8:59 AM
> To: interchange-users at icdevgroup.org
> Subject: Re: [ic] SQL query returning no results
> 
> graham hadgraft writes: 
> 
> > I am trying to perform a query that takes the string from a 
> series of
> > checkboxes and returns rowes which have a field which contains all
> > these strings in any order. 
> > 
> > This is the code i am using: 
> > 
> > [perl tables="products"]					
> 					
> > 	@array = split(/-/,$CGI->{from});
> > 	my $res = '';
> > 	$sql = "SELECT * FROM products WHERE icons LIKE '%'"; 
> > 
> > 	foreach(@array){
> > 		$sql .= " AND icons LIKE '%" . $_ . "%'";
> > 	}
> > 								
> 		
> > 	$Tag->query({sql=>$sql,hashref=>'results',table=>'products'});
> > 								
> 		
> > 	foreach my $row (@{$Tmp->{results}})
> > 	{
> > 		$res .= $row->{description} . "test<br>";
> > 	}
> > 	return $res;
> > [/perl] 
> > 
> > 
> > This returns no string.  I have changed the return to return the sql
> > string and then ran the same sql query in directsql and this returns
> > the correct products.  Changing it back to return res brings back no
> > string. 
> > 
> > This does not make any sense to me.
> 
> Kevin gave the solution to change:
>   @array = split(/-/,$CGI->{from});
> in
>   @array = split(/\0/,$CGI->{from}); 
> 
> What confuses me in this is that above you say that you have 
> returned the 
> sql string from this perl block and with that you got a 
> correct query. 
> 
> Perhaps Kevin can shed a light on that one aswell?  I'd think 
> that you would 
> not be able to have done a split with the - , so there would 
> not have been a 
> correct sql query in the first place? Or is there something 
> magic going on? 

If multiple values are passed with the same name, IC joins them together
as one long string with a null char (C "\0", ASCII 0) between them.

Things probably looked right because "\0" doesn't often show up as
anything in a page.  You'd have to check the source with a text editor
that can see non-whitespace control chars -- if i were to look at the
page in vim, for instance, i might see ^@ characters before and/or after
each word to be searched for.

If that were passed to the db, one of two things would likely happen.
Either (1) any C libs actually handling the query would see a null char
(which in C marks the end of a string) and try to process the first part
of the query, eventually failing miserably....or (2) the query would
search for a string containing a null at the beginning and/or end of the
word (which it would very likely never find).

Why no one's mentioned trying to use $CGI_array->{from}, i'm not sure.
IC would put an arrayref of the multiple values there.  No need to split
them manually.  :)

	@array = @{$CGI_array->{from}};

--
Daniel Davenport
New Age Digital
http://www.newagedigital.com