@Calvin: I do like the idea of using array_sum() and storing the options as an INT, I've taken this approach in the past with a different app (once) and it worked out just fine. In this case let's say you have a value of 3 stored in the db representing 2 options ... '1' => 'video, '2' => 'audio. A search on that column would only hit on an exact match, no? I only need the stored value and the search criteria to have one of their values in common, not all. In this case if a user searched just '1' => 'video' it should return the column containing the value of 3 because both the search and the stored data have '1' => 'video' in common.
I won't stress the checkbox search too much at this time. I did get it working without writing any kind of custom query. The query itself is to the point but checking the values of the array and setting up the $orOptions to account for each possibility was a tad lengthy. About the checkboxes ... well ... I was submitting them as separate input fields instead of one 'multiple' ... don't ask! 8-). The query giving me problems at this time is the advanced search. It's not much a problem more than I'm unsure of how to approach it. I want to toy around with it for a bit then I'll let know what issues I'm having. @John: [quote] When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID.. [/quote] Errr ... Something came up and I have to leave the house but I have a few questions for ya' ... I'll get back you when I get back in. On Tue, Jun 8, 2010 at 3:38 AM, John Andersen <j.andersen...@gmail.com>wrote: > This may not be relevant to your issue, but maybe to your solution. I > will try to explain how I did my search functionality. > > In my application, the user will search for a specific object > (Article, Author, Blog, etc.), not a combination of these. > > The search form provides the following entries: > Words [text] - one or more words > > Word criteria [radiogroup] - : > 1) must contain all words. > 2) must contain at least one word. > 3) must contain exact phrase. > > Search criteria [checkbox] - : > 1) In title (default). > 2) In summary. > 3) In body. > > Category(ies) [checkbox] - all categories. > > When the user submits the search, the first thing I do, is to save the > search parameters in the database (Enquiry model), so as to get an ID. > > The search parameters are then passed on to the responsible model > (Article, Author, Blog, etc), which then performs the actual search. > The resulting rows (ids) are passed back to the Enquiry model, which > saves the result (creates relationships between Enquiry model and > responsible model). > > I then uses the ID to paginate the result, when presenting it to the > user. > > Building the search query: > 1) I split into single words from the Words entry. > 2) Based on the Word criteria, I prepare the respective AND, OR, or > phrase condition(s) for all possible Search criteria. That is one for > Title, one for Summary and one for Body. > 3) Based on the Search criteria, I include the respective conditions > for Title, Summary and/or Body. > > The resulting find conditions looks like this (Words equal "a b c", > Word criteria "must contain all words", Search critera "In title, In > summary, In body": > [code] > Array > ( > [OR] => Array > ( > [0] => Array > ( > [AND] => Array > ( > [0] => Array > ( > [LOWER(Article.title) LIKE] => %a% > ) > [1] => Array > ( > [LOWER(Article.title) LIKE] => %b% > ) > [2] => Array > ( > [LOWER(Article.title) LIKE] => %c% > ) > ) > ) > [1] => Array > ( > [AND] => Array > ( > [0] => Array > ( > [LOWER(Article.summary) LIKE] => %a% > ) > [1] => Array > ( > [LOWER(Article.summary) LIKE] => %b% > ) > [2] => Array > ( > [LOWER(Article.summary) LIKE] => %c% > ) > ) > ) > [2] => Array > ( > [AND] => Array > ( > [0] => Array > ( > [LOWER(Article.content) LIKE] => %a% > ) > [1] => Array > ( > [LOWER(Article.content) LIKE] => %b% > ) > [2] => Array > ( > [LOWER(Article.content) LIKE] => %c% > ) > ) > ) > ) > [Article.state] => 2 > ) > [/code] > > If you have questions to the above, or just curious about other > things, feel free to ask :) Hope you will get your search up and > running! > Enjoy, > John > > > [snip] > > 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 cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com<cake-php%2bunsubscr...@googlegroups.com>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 cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
