Hi Stuart,
Getting your brains warped by logical statements, eh?
If I remember correctly AND has precedence over OR. That means that the
statement "A or B and C " evaluates to "A or (B and C) " which means that
the statement will be true if A is true or if both B and C are true.
Because of this precedence issue, you need to use parentheses to specify a
new order of evaluation. This phrase, "(A or B) AND C" will be true only
if either A or B is true at the same time that C is also true. That is a
much different meaning than if the same statement were written without
parentheses.
Basically OR adds records to your results (less restrictive), AND takes
them away (more restrictive).
The easiest place to deal with the widely different request of your users
is in your input processing script. Use your scripting language to build
an appropriate query based on the options they provide. No single SQL
statement will handle BOTH every combination of user input AND process
quickly. You should build custom WHERE clauses based on your user's
input. This is definitely NOT a one-size-fits-all situation.
Remember to use parentheses. When in doubt, spell it out. Don't make the
query engine read your mind.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 09/22/2004 09:46:46 AM:
> I'm back, trying to figure my way through a somewhat
> confusing set of conditons.
> Sort of doing a sanity check here.
> First explanation:
> I've put together a search (the database) form that
> has 6 inputs.
> Just to clarify, the fields are
> 1-JobTitle
> 2-City
> 3-Start
> 4-Industry
> 5-State
> 6-TaxTerm.
>
> The idea is that a user can choose one , two or all
> fields to put criteria into. Regardless, whichever
> one they choose , there should be a records return.
> More criteria of course adds more refinement.
>
> Right now I only have 4 of the 6 fields rigged for
> action- JobTitle, Start, Industry and State.
> I half see what's going on, but unclear on how to set
> it all up.
> So right now, since Title and Start are divided by an
> OR, if I chose both, then there is not refinement, I
> get records that meet both criteria , not both
> criteria combined , savvy ?
> Industry is sort of seperate right now, and State can
> be combined with industry to refine the results to
> match up all industries within whatever particular
> states.
>
> I guess ultimately I need a very long series of OR's
> and AND's but not sure if there is something neater
> and then operator precedence in a series of AND's and
> OR's would probably throw me. At least I think so.
>
> ALright, so I"m sure this is one of my inance babbles
> again, but if anyone can decipher my cry for
> assistance, feedback, morsels of wisdom and knowledge,
> I'm listening.
>
> Stuart
> code:
>
> where JobTitle like '%{s_JobTitle}%'
> or
> PostStart >= DATE_SUB(CurDate(), Interval
> ({s_PostStart}) day )
> $VendorJobs->ds->SQL.= " OR (`VendorJobs`.Industry IN
> (".$Projects."))";
> $VendorJobs->ds->SQL.= " AND
> (`VendorJobs`.LocationState IN (".$Projs."))";
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
