"Or" operator

Databases
1

Hi.

I have this piece of code to select users in a Joomla! site:

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('user_id','field_2')))
      ->from($db->quoteName('#_users'))
      ->where($db->quoteName('user_id') . ' = '. $db->quote($user->id). ' AND '.$db->quoteName('field_2') . ' = '. $db->quote('0'));

(...)
}
?>

It sets a conditional that includes the user_id and the value in “field_2”, defined as “0” (’ = '. $db->quote(‘0’)).

The problem is that I need the same result with different possible values for “field_2”, (1, 2, 3, 65, etc.), so I am trying to include an “or” operator somewhere in the code without any result so far.

Obviously, neither of these options work:

$db->quote('0' or '1')

>quoteName('field_2') . ' = '. $db->quote('0') . ' OR '. $db->quote('1'));

Thanks for any help.

2

Whilst in English, you might say, for example, “If a equals 3 or 6”, when writing code, you will need to say (in pseudo code)

if a = 3 or a = 6

HTH

3

If you have several values, you could also look at the “IN” clause.

1 Like
4

Yes, I have read something about WHEREIN, but I am a newbie. Is there anyway to integrate it in my code?

Regards.

5

there is no WHEREIN in SQL. IN() is simply a function in SQL that you can use like any other expression.

2 Likes
6

Ok.

I tried the code below for the 0, 1, 2 and 4 options:

<?php
$field_where_in =  ' AND  IN (0,1,2,4)';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('user_id','field_2')))
      ->from($db->quoteName('#_users'))
      ->where($db->quoteName('user_id') . ' = '. $db->quote($user->id). ' AND '.$db->quoteName('field_2') . ' = '. $db->quote('0') .$field_where_in );  
$db->setQuery($query);
$result = $db->loadObjectList();

(...)
}
?>

But it doesn’t work, and I do not know why.

7

What is $field_where_in? Is that a field in your database?

You should use the IN clause like this:

AND database_field_name IN (0,1,2,4);
8

field_2 is the field involved in the database.

The code below works only for the first option: (0).

<?php
$field_where_in =  ' AND field_2  IN (0,1,2,4)';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('user_id','field_2')))
      ->from($db->quoteName('#_users'))
      ->where($db->quoteName('user_id') . ' = '. $db->quote($user->id). ' AND '.$db->quoteName('field_2') . ' = '. $db->quote('0') .$field_where_in );  
$db->setQuery($query);
$result = $db->loadObjectList();

(...)
}
?>
9

Either one or both of us are confused about the logic. It looks like the query is saying something like

if variable is zero 
and 
if variable is either zero, one, two, or four

The first if variable is zero makes the IN uneccessary. If it is equal to 0 of course it will be equal to 0, 1, 2, or 4

Maybe you want it to be OR ? If so, that makes the first if uneccessary.

Perhaps it would be helpful to break the $field_where_in into its components? eg.

$field_operator = "AND"; 
$field_identifier = "field_2"; 
$field_conditional = "IN(0,1,2,4)"; 
$field_where = $field_operator . $field_identifier . $field_conditional
1 Like
10

would you mind echoing the value of $query so that we can see the actual SQL generated

if the problem is fixing a query that isn’t working the way you want, it’s a ~lot~ easier looking at the SQL than debugging the php logic code that generates it

11

No, it doesn’t work either. Only works for “0”. And yes, the operator should be “Or”

12

Then, what to do with this line?

->where($db->quoteName(‘user_id’) . ’ = '. $db->quote($user->id). ’ AND '.$db->quoteName(‘field_2’) . ’ = '. $db->quote(‘0’) .$field_where_in );

13

@andresb. When following up on @Mittineague’s example :

where($db->quoteName(‘user_id’).'='.$db->quote($user->id).$field_where);

As @Mittineague allready explained, using both those statements doesn’t make sense. Since you allready constructed $field_where:

$field_operator = "AND"; 
$field_identifier = "field_2"; 
$field_conditional = "IN(0,1,2,4)"; 
$field_where = $field_operator . $field_identifier . $field_conditional

Just add the constructed AND CLAUSE right after the WHERE CLAUSE

14

PS, don’t do as I did in the example and forget to have spaces in the concatenation!

EDIT
To repeat what r937 posted.

being able to see the query apart from any PHP code would be a big help.

1 Like
15

This finally works:

<?php
$field_where_in =  ' AND field_2  IN (0,1,2,4,7)';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('user_id','field_22')))
      ->from($db->quoteName('#_users'))
      ->where($db->quoteName('user_id') . ' = '. $db->quote($user->id). $field_where_in );  
$db->setQuery($query);
$result = $db->loadObjectList();

...
}
?>

Solved. Many thanks. You have helped me to understand the model, which is more important than the problem itself.

2 Likes
16

Maybe try this which I find a lot easier to understand especially if it ever needs modifications :


	$field_where_in =  ' AND field_2  IN (0,1,2,4,7)';
	
	$db = JFactory::getDbo();
	
	$query = $db->getQuery(true);

	$query ->select
			( 
	  		$db->quoteName(array('user_id','field_22') )
			)

	      ->from
	      	(
	      	   $db->quoteName('#_users')
	      	)

	      ->where
	      	(
	   		$db->quoteName('user_id') 
	   		. ' = '
	   		. $db->quote($user->id)
	   		. $field_where_in 
	      	);  

	$db->setQuery($query);

	$result = $db->loadObjectList();

...
}
1 Like
17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.