Advertisement

SQL WHERE clause with two OR conditions Topic is solved

For Joomla! 4.x Coding related discussions, you could also use: http://groups.google.com/group/joomla-dev-general

Moderators: ooffick, General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Tue May 07, 2024 5:12 pm

SQL WHERE clause with two OR conditions

Post by JanoD » Tue Jul 09, 2024 9:05 am

Hello,

I am trying to create a query that is satisfied when either column1 or column2 contains a desired string (stored in variable $search). The following code works but seems to be doing AND instead of OR logic. How to I do OR with where?

Code: Select all

$query->where($db->quoteName('column1') . ' LIKE :search1')
	->where($db->quoteName('column2') . ' LIKE :search2')
	->bind([':search1', ':search2'], $search);
Thanks!

Advertisement
SharkyKZ
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 3006
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: SQL WHERE clause with two OR conditions

Post by SharkyKZ » Tue Jul 09, 2024 11:08 am

You can pass the "glue" as a second argument.

Code: Select all

$query->where($db->quoteName('column1') . ' LIKE :search1', 'OR')
	->where($db->quoteName('column2') . ' LIKE :search2')
Note it can only bet set in the first call. To change it later you have to use extendWhere() with first argument as the new outer "glue":

Code: Select all

$query->extendWhere( 'AND', $db->quoteName('column3') . ' LIKE :search3');
This will result in a query like this:

Code: Select all

SELECT * FROM #__table WHERE (`column1` LIKE :search1 OR `column2` LIKE :search2) AND (`column3` LIKE :search3) 
Also, instead of multiple where/extendWhere() calls you can pass an array:

Code: Select all

$query->where(
    [
        $db->quoteName('column1') . ' LIKE :search1',
        $db->quoteName('column2') . ' LIKE :search2',
    ],
    'OR'
);

JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Tue May 07, 2024 5:12 pm

Re: SQL WHERE clause with two OR conditions

Post by JanoD » Tue Jul 09, 2024 11:48 am

@SharkyKZ, thanks this helps and solves the problem! Is there anything on this also somewhere in documentation? I could not find it e.g. here: https://manual.joomla.org/docs/general- ... /database/ but I could have missed something.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 31159
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: SQL WHERE clause with two OR conditions

Post by Per Yngve Berg » Tue Jul 09, 2024 12:57 pm

Mod. Note: Relocated topic to the Coding Forum.

SharkyKZ
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 3006
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: SQL WHERE clause with two OR conditions

Post by SharkyKZ » Tue Jul 09, 2024 2:42 pm

You should use an IDE when developing, then you'd see hints. Otherwise you can find API docs here https://api.joomla.org/framework-3/clas ... thod_where.

JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Tue May 07, 2024 5:12 pm

Re: SQL WHERE clause with two OR conditions

Post by JanoD » Tue Jul 09, 2024 3:59 pm

Thanks for the link.

I am using VS Code and I get some hints on hover over very few functions. When hovering on e.g. select->(...) or where->(...) I get no hints. Do I need to install an extension?

SharkyKZ
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 3006
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: SQL WHERE clause with two OR conditions

Post by SharkyKZ » Tue Jul 09, 2024 4:25 pm

First, it depends on how your environment is set up, whether your code repository includes Joomla code. If not, you need to add Joomla directory to your workspace. Secondly, it depends on your code. The hints will not be shown if you're using class aliases like JFactory. For instance, if you're using JFactory::getDbo() to retrieve the database instance. In that case you can run stub generator script (build/stubGenerator.php) but it's better to just upgrade to new namespaced classes. Or even refactor code to use proper DI.

JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Tue May 07, 2024 5:12 pm

Re: SQL WHERE clause with two OR conditions

Post by JanoD » Thu Jul 11, 2024 4:33 pm

OK, thanks. Step by step :-).

Advertisement

Post Reply

Return to “Joomla! 4.x Coding”