Welcome to Array Web Development's Dedicated Blog Site at MarketingPortland.com.
FROM THE ARCHIVES: Highlights from Techmanity 2014 (part 2 of 2)     MARKETING SNIPPETS: Challenges are what make life interesting and overcoming them is what makes life meaningful. -Joshua J. Marine     GRANNYISM #77: Be good to yourself and others.~via SweetLeaf

Quick tip, based on a little trouble I was having earlier... Say you want JDatabase to do a query that returns a recordset based on two rules:

  1. records with an id of 123 in the DB
  2. another where clause that has an OR operator in it... say that a column `val2` must either be 0 or between 234 and 345

Ok, if you're like me, you probably code this using two normal WHERE clauses, like so...

  1.  
  2.      $query->where(' `id` = 123 ');
  3.      $query->where(' `val2` = 0 or ( `val2` > 234 and `val2` < 345 )' );
  4.  

Seems legit, right? However, Joomla would render the query as:

  1.  
  2.      SELECT * FROM #__table WHERE `id` = 123 AND `val2` = 0 or ( `val2` > 234 and `val2` < 345 )
  3.  

See the problem? Where Joomla sews these two where clauses together, it loses the logic of your distinct WHERE statements. As written, that query would not return the results you want. It would instead only return:

  1. items with 123 as the id and also a val2 value of 0, and
  2. items with any id but val2 values between the values specified.

The fix is to put the 2nd entire WHERE in parens of its own, as in:

  1.  
  2.      $query->where(' `id` = 123 ');
  3.      $query->where('( `val2` = 0 or ( `val2` > 234 and `val2` < 345 ))' );
  4.  

.... which renders:

  1.  
  2.      SELECT * FROM #__table WHERE `id` = 123 AND (`val2` = 0 or ( `val2` > 234 and `val2` < 345))
  3.  

And that would be the correct result list. :-)

Get Help with Your Company's Web Site

Thanks for reading Array Web Development's "Marketing Portland" blog. Need help with a web project? Click here to contact us today and tell us about it.

Link to this article from your web site:

Option 1: Grab the link

Option 2: Embed a link (copy the HTML code below)

On Facebook?

Please visit / like our Facebook page.

On Medium.com?

Follow Jim on Medium to read more web, business, and marketing articles.

Professional web development, Joomla! development, traditional marketing, and business development services.
Call (503) 902-HTML now to discuss your project!