Welcome to Array Web Development's Dedicated Blog Site at MarketingPortland.com.
FROM THE ARCHIVES: "Circling Back -- an interesting sales phrase."     MARKETING SNIPPETS: Whatever the mind of man can conceive and believe, it can achieve. -Napoleon Hill     GRANNYISM #33: Too many cooks spoil the brew.~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. :-)

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