How do I build a WHERE statement in CCv5?

Written Mar 31, 2014 15:36

ChronoConnectivity v5 and ChronoForms v5 use a different syntax for building WHERE statements. This FAQ gives some examples of the forms that can be used to build more complex statements.

Note: these examples will work for ChronoForms v5 or ChronoConnectivity v5

In each case the code to create a WHERE clause goes into the 'Conditions' box on the listing Models tab.

To use '='

<?php
return array('model_id.column' => 'value');
?>

To use != (or similar expressions)

<?php
return array( 'model_id.column !=' => 'value' );
?>

To use AND

Just add more than one entry to the array and they will all be required

<?php
return array( 'model_id.column' => 'value', 'model_id.column_2' => 'value_2' );
?>

To use OR

Add an array of values

<?php
return array( 'model_id.column' => array('some value', 'another value', . . . ) );
?>

To use LIKE

<?php
return array( 'model_id.column LIKE' => '%value%' );
?>

Or, with a PHP variable

<?php
return array( 'model_id.column LIKE' => '%'.$variable.'%' );
?>

Getting values in CC/CFv5

ChronoForms v5 stores any values it has in the $form->data array and you can access the values using $form->data['variable_name']. Similarly ChronoConnectivity v5 stores any values it has from the page URL in the $this->data array and you can access the values using $this->data['variable_name'].

So for example if you want to use the gcb variable passed back by CC to CFv5 you might have a WHERE clause like this

<?php
return array('model_id.column' => $form->data['gcb']);
?>

Or, if you are using a Model Id that saves data in a sub-array the WHERE code might look like this:

<?php
return array('model_id.column' => $form->data['model_id']['var_name'];
?>

You can adapt any of the other examples here similarly.

Getting the current user's records

If you have a column in the database table with the user's id in it then you can get just their records

<?php
$user = \JFactory::getUser();
return array('model_id.user_id' => $user->id);
?>

This can be combined with other conditions as shown above.

A complex query

You can add more complex WHERE string by prefixing it with a colon :

<?php
return array (":model.column = 'value' OR model.column_2 = 'value_2'");
?>

<?php
// where $var is an integer
$var = JRequest::getVar('param','', 'get');
return array (":test.id > {$var}");
?>

But note that you must quote any string values before using them:

<?php
// where $var is an string
$var = JRequest::getVar('param','', 'get');
$dbo = \GCore\Models\MODEL_ID::getInstance()->dbo;
$var = $dbo->quote($var);
return array (":test.id > {$var}");
?>

A conditional query . . .

You can use any PHP in the Conditions box to check form values and set different conditions as you need them

<?php
if ( empty($form->data['input_name']) ) { 
  return array('model_id.column' => 'value');
} else { 
  return array (":test.id > { $form->data['input_name']}");}
?>

FAQs