SOLVED Calc Element - select, from, where with and syntax question

[gsellers]

First, I'd like to thank the developers behind Fabrik; it really is a great product.

As a general rule, when I hit a hurdle, I read the Fabrik Wiki and Forum, and of course Google is my friend, until I can find out what I've done incorrectly (although as a novice, responses on StackOverflow or reading the PHP manual is more than a little difficult to then apply to what I need to do within Fabrik).

I believe I have a syntax issue when writing the Calculation field in a Calc Element. Unfortunately, this is something I am currently developing on a WAMP server and have no way to provide access.

This is Fabrik 3.0.X and Joomla 2.5

This is what I have

$v = '{dqw3l_sis_tsh_equip_detail___id}';
$s = '{dqw3l_sis_tsh_equip_detail___alias_number_raw}';
$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query
->select('r_m_category')
->from('dqw3l_sis_tsh_equip')
->where('id = ' . $db->quote($v));

$db->setQuery($query);
$category = $db->loadResult();

$query = $db->getQuery(true);
$query
->select('category_vendor')
->from('dqw3l_sis_tsh_vc')
->where('alias_number = ' . $db->quote($s))
and ('category = ' .$db->quote($category));

$db->setQuery($query);
$vendor = $db->loadResult();

return $vendor;

I can't seem to get the "and" evaluation to work. The table 'dqw3l_sis_tsh_vc' is a one to many, where one "alias_number" can relate to many "category". An "alias_number":"category" relationship will produce a single "category_vendor" value. As written, $vendor returns the same value for all "category" for a particular "alias_number". If I delete the portion "and ('category = ' .$db->quote($category))" there is no change (same value for all "category"). I have also reversed the two where statements. In that instance, it returns different "category" values, but the values are the same regardless of the "alias_number" It's demo data and the same categories repeat as the alias_number changes creating effectively the opposite effect - seemingly not using the evaluation after the "and".

I'm hoping that I'm making some obvious structural or syntax mistake; the answer may also be a simple "you can't do that, that way". Any help in correcting the structure, syntax, or pointing me to a "do it this way" code example would be greatly appreciated.

 

[gsellers]

This is an update for anyone searching this post, because they have a similar problem. The issue was a syntax mistake (mainly based on a lack of knowledge). Although I didn't originally find this post http://www.fabrikar.com/forums/index.php?threads/help-on-joomla-query.24406/ and actually only hit upon it after realizing my mistake, it is on point (although my working code isn't exactly like any of the suggestions). The correction with the last "where" is;

->where('alias_number = ' . $db->quote($s))->where('category = ' .$db->quote($category));

If you are a novice (like me) or non-programmer (like me) my path of understanding took me through a PHP101 set of tutorials and also recognition that (and I'll probably not have the terminology correct) I'm not doing a SQL query but executing JDatabaseQuery a PHP class. In this instance "where" will accept multiple conditions and automatically defaults to 'and' as the operator. If you've hit this hurdle; I can't recommend highly enough; go to the JDatabaseQuery page and do a little reading, you'll be glad you did.

 

[troester]

->where('alias_number = ' . $db->quote($s).' and category = ' .$db->quote($category));
should do also.