[ic] SQL query returning no results
Daniel Davenport
DDavenport at newagedigital.com
Fri Oct 27 12:35:19 EDT 2006
> -----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
More information about the interchange-users
mailing list