1
xgarb
Criteria class for SQL 'OR' - solved!
  • 2003/10/27 14:40

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


Hi all.

Anybody any idea how to make a Criteria class that will do a MySQL 'OR' rather than the available '=' or 'LIKE'.

I'd like to have members be able to search on one term across 4 columns. ie WHERE column1 = searchitem OR column2 = searchitem OR column3...etc

Thanks!

Andrew

2
onokazu
Re: Criteria class for SQL 'OR'
  • 2003/10/27 14:54

  • onokazu

  • XOOPS Founder

  • Posts: 617

  • Since: 2001/12/13


$criteria = new CriteriaCompo(new Criteria('column1', 'searchitem'));
$criteria->add(new Criteria('column2', 'searchitem'), 'OR');


3
ackbarr
Re: Criteria class for SQL 'OR'

and if you want to use a different comparison operator you can do it like this:

$criteria = new CriteriaCompo(new Criteria('column1''searchitem', [b]'IN'[/b]));
$criteria->add(new Criteria('column2''searchitem', [b]'>'[/b]), 'OR');


4
xgarb
Re: Criteria class for SQL 'OR'
  • 2003/10/27 16:10

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


Hi,

Doesn't seem to work ...

I've the following in xoopsmembers/index.php...

if ( !empty($HTTP_POST_VARS['interest']) ) {
$criteria = new CriteriaCompo(new Criteria('interest1', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%'));
$criteria->add(new Criteria('interest2', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%'), 'OR');
}

'interest' is the form variable and 'interest1/interest2' are column headings.

The following works, but only for column interest1 of course

if ( !empty($HTTP_POST_VARS['interest']) ) {
$criteria->add(new Criteria('interest1', '%'.addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'));
}

Andrew

5
onokazu
Re: Criteria class for SQL 'OR'
  • 2003/10/27 17:40

  • onokazu

  • XOOPS Founder

  • Posts: 617

  • Since: 2001/12/13


Try it like this:

if ( !empty($HTTP_POST_VARS['interest']) ) {
$criteria = new CriteriaCompo(new Criteria('interest1', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'));
$criteria->add(new Criteria('interest2', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'), 'OR');
}

6
xgarb
Re: Criteria class for SQL 'OR'
  • 2003/10/27 18:04

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


That works perfectly!

Many thanks,

Andrew

PS Code for 4 columns below for anyone that's following this..

____________________________________

if ( !empty($HTTP_POST_VARS['interest']) ) {
$criteria = new CriteriaCompo(new Criteria('interest1', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'));
$criteria->add(new Criteria('interest2', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'), 'OR');
$criteria->add(new Criteria('interest3', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'), 'OR');
$criteria->add(new Criteria('interest4', '%'.$myts->addSlashes(trim($HTTP_POST_VARS['interest'])).'%', 'LIKE'), 'OR');
}

7
xgarb
Re: Criteria class for SQL 'OR'
  • 2003/10/27 18:33

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


oops! Spoke too soon.

Using this code stops any of the other membersearch form elements from having any effect.

ie. Searching for a username and an interest returns all the members with that interest, not just those with a username match.

oh bother!

8
xgarb
Re: Criteria class for SQL 'OR'
  • 2003/10/28 11:38

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


It gets weirder!...

I've tidied the code a little. Don't need the mySQL wildcards so...

if ( !empty($HTTP_POST_VARS['interest']) ) {
$criteria = new CriteriaCompo(new Criteria('interest1',$myts->addSlashes(trim($HTTP_POST_VARS['interest']))));
$criteria->add(new Criteria('interest2', $myts->addSlashes(trim($HTTP_POST_VARS['interest']))), 'OR');
$criteria->add(new Criteria('interest3', $myts->addSlashes(trim($HTTP_POST_VARS['interest']))), 'OR');
$criteria->add(new Criteria('interest4', $myts->addSlashes(trim($HTTP_POST_VARS['interest']))), 'OR');
}

add to this another form field choice for location - another select pull down

if ( !empty($HTTP_POST_VARS['location']) ) {
$criteria->add(new Criteria('location', $myts->addSlashes(trim($HTTP_POST_VARS['location']))));
}

and in the sql debug window you get the expected SQL...

SELECT * FROM xoops_users WHERE (interest1 = 'Camping' OR interest2 = 'Camping' OR interest3 = 'Camping' OR interest4 = 'Camping' AND location = 'Andorra' AND level > 0) ORDER BY uname ASC LIMIT 0, 20

but XOOPS ( here, I imagine, $foundusers =& $member_handler->getUsers($criteria, true); ) returns all users who like camping not just the one in Andorra.

Huh?!

Help!

Andrew

9
xgarb
Re: Criteria class for SQL 'OR' - solved
  • 2003/10/28 16:37

  • xgarb

  • Not too shy to talk

  • Posts: 154

  • Since: 2003/3/30


OK. I've done it now.

MySQL's order of operations is AND before OR which is why the code selects all the people from Andorra. (http://mysql.progen.com.tr/doc/en/Selecting_rows.html)

I had to create 2 new criteria classes with a slightly different render function to create a bracket either side of the 'OR' statement.

My code now looks like this...

if ( !empty($HTTP_POST_VARS['interest']) ) {

$choseninterest=$myts->addSlashes(trim($HTTP_POST_VARS['interest']));

$criteria = new CriteriaCompoBegin(new Criteria('interest1', $choseninterest));
$criteria->add(new Criteria('interest2', $choseninterest), 'OR');
$criteria->add(new Criteria('interest3', $choseninterest), 'OR');
$criteria->add(new CriteriaEnd('interest4', $choseninterest), 'OR');

}

And the sql it generates (including the location part)...

SELECT * FROM xoops_users WHERE ((interest1 = 'Camping' OR interest2 = 'Camping' OR interest3 = 'Camping' OR interest4 = 'Camping') AND location = 'Andorra' AND level > 0) ORDER BY uname ASC LIMIT 0, 20

Hurrah!


10
lubdub
Re: Criteria class for SQL 'OR' - solved
  • 2003/10/28 21:31

  • lubdub

  • Just popping in

  • Posts: 64

  • Since: 2002/2/28


You don't need to create a subclass, just order and create your instance properly (CriteriaCompo renders hierachically)

Quick and Dirty Example:
$criteria1 = new Criteria('interest1''Camping');
$criteria2 = new Criteria('interest2''Camping');
$criteria3 = new Criteria('interest3''Camping');
$criteria4 = new Criteria('interest4''Camping');

$compo1 = new CriteriaCompo($criteria1);
$compo1->add($criteria2'OR');
$compo1->add($criteria3'OR');
$compo1->add($criteria4'OR');

$criteria5 = new Criteria('location''Andorra');
$criteria6 = new Criteria('level'0'>');
$compo2 = new CriteriaCompo($criteria5);
$compo2->add($criteria6'AND');

$compo3 = new CriteriaCompo($compo1)
$compo3->add($compo2'AND');


$compo3 should, with the syntax errors corrected, render roughly your WHERE clause...

Login

Who's Online

134 user(s) are online (76 user(s) are browsing Support Forums)


Members: 0


Guests: 134


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits