@John:
[quote]
Can you explain why you have not separated/normalized the
"SomeCol.contents"?
And do you have other such columns in your database?
[/quote]
No, that is the only column in the db that stores multiple values in one
field (that aren't for display only). At one point in time the data in that
column wasn't searchable and it never presented an issue. A little hard to
explain but even though there are separate values, with the exception of the
recently added query they get treated as a whole within the app. I figured
that if the app ever changes down the road and needs to handle the values
individually it would be easy enough to explode the data. So to answer your
question of why? To be honest ... I thought I had good enough reasons but in
the end I suppose I didn't think it through well enough or far enough ahead.
If need be a restructuring of the db is not out of the question I just
assumed it wouldn't be a real stretch to come up with a creative solution
which ultimately ended up being the case. Just for the record, I do
recognize that a 'creative' solution is not always the best solution 8-).
@Calvin:
About the queries ... there was a basic and an advanced. The basic is done
and working. With the advanced search since I don't know fields elements are
being submitted for the search I would normally concatenate the query
itself.
[example]
$query= " SELECT something FROM somewhere WHERE etc. etc. etc. ";
if (isset($_GET['some_value']) && $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= "AND something = '".addslashes($value)."'";
}
if (isset($_GET['some_other_value']) && $_GET['some_other_value'] != '' )
{
$value = $_GET['some_other_value'];
$query .= "AND something = '".addslashes($value)."'";
}
$query .= "WHERE some_col = some_criteria"
mysql_query($query);
[/example]
This worked well enough in the past. I agree that if the conditions were all
'OR' I would be good to go, unfortunately they all need to be 'AND'. The
closest example I can find in the book
( http://book.cakephp.org/view/1030/Complex-Find-Conditions ) that seems
even remotely close would be $dbo->buildStatement but to be brutally honest
I have no clue how I would implement that (or something similar) in this
case. Cake will accept the query whichever way I present it but the real
problem lies in 'paginate' and 'containable', both of which I need. I
suppose both could be written manually but then I would be well off the
beaten path as far as Cake conventions and given my track record it would
undoubtedly create some unforeseen problem somewhere down the line.
As far as URL-encoded array data goes I just set the form method to GET and
let Cake do it's thing. In the controller I pull the data from the URL using
$this->params['url']['something'].
If you are saying I can configure the routes to convert it over to named
params I'll play around with it some more. The furthest I experimenting I've
done with routes is to change from mySite/thisIsMyPage to
my-site/this-is-my-page.
- Ed
On Mon, Jun 7, 2010 at 11:55 AM, calvin <[email protected]> wrote:
> If all of your search options are OR conditions, then you could
> theoretically do something like this:
> SELECT ... WHERE contents REGEXP '(opt1|opt2|opt3|opt4|opt5|opt6)';
>
> http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
>
> On Jun 6, 1:24 pm, Ed Propsner <[email protected]> wrote:
> > I found a usable solution, a bit exhaustive and long-winded perhaps, but
> > usable nonetheless.
> >
> > I still need to put together a dynamic query and I'm finding myself
> avoiding
> > having to do it at this time.
> >
> > I need to build a query dynamically based on what elements a user chooses
> > from a form. There could be 20 choices or there could be 2.
> >
> > Every solution I'm coming up with is ridiculously excessive for something
> > that should be so simple. I'm sure the answer is there, I'm just not
> > familiar enough with Cake at this point to see it.
> >
> > - Ed
> >
> > On Sat, Jun 5, 2010 at 4:36 PM, Ed Propsner <[email protected]>
> wrote:
> > > Perhaps I'm over-complicating this but I'm still having some problems
> with
> > > building my query.
> >
> > > I'm looking to do something like:
> >
> > > $array = array (A, B, C, D, E);
> > > $list = implode( ',' , $array);
> >
> > > 'conditions' => array(
> > > 'SomeCol.contents' => array($list)
> > > )
> >
> > > The problem I'm running into is that $array can contain any number and
> > > combination of values (A, C, E), (A, B), (E) ... etc.
> > > And 'SomeCol.contents' can also contain any number and combination of
> > > values stored as a comma separated string. (it was originally stored as
> a
> > > serialized array).
> >
> > > I need the query to return a result if 'SomeCol.contents' and $array
> have
> > > any one of their values in common.
> >
> > > I was thinking along the lines of :
> >
> > > 'conditions' => array(
> > > 'SomeCol.contents LIKE' =>
> '%'.$array[0].'%'
> > > OR
> > > 'SomeCol.contents LIKE' =>
> > > '%'.$array[1].'%' etc, etc // this way should include any record in
> the
> > > result that shares a value with $array
> >
> > > but I assume I would run into an issue with an undefined index by not
> > > knowing how many values $array contains. (I do know it has the
> potential to
> > > store a max of 7 values)
> >
> > > I had this all worked out with conventional PHP but now I need to use
> > > Paginate and containable with this query as well as a bunch of other
> > > conditions (with a set value) and I'm confusing myself more than
> anything
> > > else which isn't a real stretch for me to begin with 8-).
> >
> > > Any suggestions are appreciated.
> >
> > > Thanks.
> >
> > > - Ed
> >
> > > On Fri, Jun 4, 2010 at 11:54 PM, Ed Propsner <[email protected]>
> wrote:
> >
> > >> Thanks Calvin, point well taken. I'll just stick with GET like I
> always
> > >> have for searches.
> >
> > >> I tweaked the routing and got things cleaned up a bit so I guess I'm
> okay
> > >> with it.
> > >> I'm not quite sure what I was expecting in the first place? 8-)
> >
> > >> The search uses a lot of checkboxes and results in something like
> > >>http://mysite.com/search/results/value1=0&value1=1&value2=0&value2=1.
> > >> I can't seem to clean it up any more when submitting with GET, or am I
> > >> still missing something?
> >
> > >> Anyhow, at least it's working the way that I want it to. I'm still
> having
> > >> some issues with query so I'll quit fussing with the url for now.
> >
> > >> - Ed
> >
> > >> On Fri, Jun 4, 2010 at 10:15 PM, calvin <[email protected]>
> wrote:
> >
> > >>> POST requests are generally not cached, but you can force it to be
> > >>> cached using the Cache-Control and Expires headers. However, I've
> > >>> never tried this so I don't know if the browser will still show the
> > >>> form submission dialog (it may need to resend the form data to check
> > >>> to see if the document has changed since last requested). It probably
> > >>> won't, but I would still strongly advise against this.
> >
> > >>> A search request is generally an idempotent operation (multiple
> > >>> requests do not have any side-effects), which is precisely what the
> > >>> GET request is designed for. There's no reason to use POST in this
> > >>> case. Cake has a perfectly good way of hiding ugly URL-encoding using
> > >>> its REST-style routing patterns, e.g.:
> >
> > >>>http://yoursite.com/pages/search/foo/bar/foo2/bar2/...
> >
> > >>> You can also use named parameters to make the search URL more
> > >>> readable, e.g.:
> >
> > >>>
> http://yoursite.com/products/search/q:paegan/artist:acid+bath/categor...
> >
> > >>> Unless you have a ton of search options, I see no reason to use POST.
> > >>> And even if you use POST and generate shorter/cleaner URL--what's the
> > >>> point? What will that clean URL achieve? The user can't bookmark it.
> > >>> They can't link a friend to it. They can't do anything with it.
> >
> > >>> A happy compromise would be to do what a lot of forums do, and cache
> > >>> each search server-side. Then when the user performs a search (with
> > >>> either POST or GET), they get redirected to the cached search result,
> > >>> which might be something like:
> >
> > >>>http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b
> >
> > >>> That will decrease your server load, allow you to use a POST form,
> and
> > >>> still allow the user to bookmark/link the search results (at least
> for
> > >>> a time).
> >
> > >>> On Jun 4, 10:40 am, Ed Propsner <[email protected]> wrote:
> > >>> > I was checking out the book on complex queries and not really
> finding
> > >>> what
> > >>> > I'm looking for.
> >
> > >>> > I'm trying to create a query that covers both a basic and advanced
> > >>> search.
> > >>> > The form may be submitting all or just some of the options
> available on
> > >>> the
> > >>> > page depending on what the user chooses.
> >
> > >>> > Once the form has been submitted clicking any one of the query
> results
> > >>> would
> > >>> > navigate the user away from the 'results page'.
> >
> > >>> > To avoid getting hit with the "form submission" dialogue when the
> users
> > >>> > clicks the back button to return to the search results page
> > >>> > and to cover the 'conditional query' ... I used to use a combo of
> $_GET
> > >>> and
> > >>> > if(isset.
> >
> > >>> > ie:
> >
> > >>> > $query= " SELECT something FROM somewhere WHERE etc. etc. etc. AND
> ";
> >
> > >>> > if (isset($_GET['some_value']) && $_GET['some_value'] != '' )
> > >>> > {
> > >>> > $value = $_GET['some_value'];
> > >>> > $query .= "AND something = '".addslashes($value)."'";
> > >>> > }
> >
> > >>> > And so on.
> >
> > >>> > I'm not familiar with using cache for anything. To avoid using get
> and
> > >>> the
> > >>> > ugly urls would I be able to use post and cache the results also
> > >>> avoiding
> > >>> > the "form submission" dialogue when returning to the results page?
> >
> > >>> > Also, what is the best approach to setting up a 'conditional' query
> > >>> similar
> > >>> > to what I posted above?
> >
> > >>> > I'll spend some more time digging through the book and
> experimenting if
> > >>> > someone can point me in the right direction.
> >
> > >>> > Thanks,
> >
> > >>> > - Ed
> >
> > >>> Check out the new CakePHP Questions sitehttp://cakeqs.organd help
> > >>> others with their CakePHP related questions.
> >
> > >>> You received this message because you are subscribed to the Google
> Groups
> > >>> "CakePHP" group.
> > >>> To post to this group, send email to [email protected]
> > >>> To unsubscribe from this group, send email to
> > >>> [email protected]<cake-php%[email protected]>
> <cake-php%[email protected]<cake-php%[email protected]>>For
> more options, visit this group at
> > >>>http://groups.google.com/group/cake-php?hl=en
>
> Check out the new CakePHP Questions site http://cakeqs.org and help others
> with their CakePHP related questions.
>
> You received this message because you are subscribed to the Google Groups
> "CakePHP" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]<cake-php%[email protected]>For
> more options, visit this group at
> http://groups.google.com/group/cake-php?hl=en
>
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
