Dynamic Filter on two fields

Hi - I have built a form and view using ChronoForms and ChronoConnectivity and included the ability to dynamically filter the view among other things with your previous assistance. However, my client would like to be able to filter the views based on having two fields match. Please let me know what needs to be modified in my code to allow for this. I tried some code from one of the tutorials but it was for having a drop down field filter plus a text box search filter and I couldn't get it to work the way I wanted. The firld names my client would like to filter on in combination are "gpao" and "metal". My CC form info is below and the database table name is "jos_chronoforms_bovetstock1":

WHERE SQL:

<?php
$model = JRequest::getString('model', '', 'post');
if ( $model ) {
echo " WHERE `model` LIKE '%$model%'
OR `gpao` LIKE '%$model%'
OR `metal` LIKE '%$model%'
OR `case` LIKE '%$model%'
OR `worldprice` LIKE '%$model%'
OR `cost` LIKE '%$model%'
OR `dial` LIKE '%$model%'
OR `location` LIKE '%$model%'  ";
}
?>

FRONTEND FILTERING:
worldprice,cost,metal,case,gpao,dal,location

HEADER:

<br>

<?php
$script = "";
$script .= "
$('clear').addEvent('click', function() {
$('title').value = '';
});
";
if ( $script ) {
$doc =& JFactory::getDocument();
$script = "window.addEvent('domready', function() { $script });";
$doc->addScriptDeclaration($script);
}
?>

<input type='text' name='model' id='model' value='' /> <input type='submit'
name='filter' id='filter' value='Filter' /> <input type='submit' name='clear' id='clear' value='Clear' />

<?php
global $total_cost;
$total_cost = 0;
?>

<?php
global $total_invoice;
$total_cost = 0;
?>

<?php
global $total_worldprice;
$total_worldprice = 0;
?>

<form><input type="button" value="Print"
onclick="JavaScript:popitup('PRINTAREA');" rel="nofollow"/></form>


<br>
<div id="PRINTAREA"> 
<table class="printtext" width="100%" " border="1" cellpadding="0">
  <tr>
    <th scope="col" bgcolor="#e7e7e7">GPAO</th>
    <th scope="col" bgcolor="#e7e7e7">Model </th>
    <th scope="col" bgcolor="#e7e7e7">Metal </th>
    <th scope="col" bgcolor="#e7e7e7">Dial </th>
    <th scope="col" bgcolor="#e7e7e7">Location</th>
    <th scope="col" bgcolor="#e7e7e7">Case No.</th>
    <th scope="col" bgcolor="#e7e7e7">World Price</th>
    <th scope="col" bgcolor="#e7e7e7">Cost</th>
    <th scope="col" bgcolor="#e7e7e7">Edit</th>
    <th scope="col" bgcolor="#e7e7e7">Delete</th>

  </tr>

BODY:

<?php
global $total_cost;
$total_cost += $MyRow->cost;
?>

<?php
global $total_invoice;
$total_invoice += $MyRow->invoice;
?>

<?php
global $total_worldprice;
$total_worldprice += $MyRow->worldprice;
?>
<tr>
    <th scope="col">{gpao}</th>
    <th scope="col">{model}</th>
    <th scope="col">{metal}</th>
    <th scope="col">{dial}</th>
    <th scope="col">{location}</th>
    <th scope="col">{case}</th>
    <th scope="col">${worldprice}</th>
    <th scope="col">${cost}</th>
    <th>{edit_record}</th>
    <th>{delete_record}</th>
  </tr>

FOOTER:

</table>
<br>    
<?php
global $total_worldprice;
echo "<div>Total World Price: $".number_format($total_worldprice)."</div>";
?>
<?php
global $total_cost;
echo "<div>Total Cost: $".number_format($total_cost)."</div>";
?>
</div>

Hi mkusens ,

I can't see from this how you want the new filter to work? I can see that `gpao` and `metal` are columns in the table and currently you are searching these and others for the value in the 'model' input.

Bob

PS I would expect the embedded <form> tags in the header to cause problems with the Listing <form> tags - nested <form> tags are not allowed in HTML.

Yes, currently I am searching all of those fields for the typed in value. I want to change it so that only the gpao and/or metal fields are being searched. So for example if I have the values in the table below:

GPAO METAL
AM43 WG
AM43 RG
AB23 WG
AH65 RG
AH65 RG

I want to search for where gpao is "AM43" and Metal is "WG" so only that one item returns.

Hi mkusens,

Please try this:

<?php
$gpao = JRequest::getString('gpao', '', 'post');
$metal = JRequest::getString('metal', '', 'post');
$where = array();
if ( $gpao ) {
  $where[] = " `gpao` LIKE '%$gpao%' ";
}
if ( $metal ) {
  $where[] = " `metal` LIKE '%$metal%' ";
}
if ( count($where) ) {
  echo "WHERE ".implode(' AND ', $where);
}
?>

Bob

I couldn't get that code to work. I assume I replace my existing WHERE SQL code with this code but just want to confirm. If I provided admin access to my site, could you please look at the code and get it to work? I would be glad to buy you a beer or two to help. Thanks.

Hi Mkusens,

Sure. By all means email or PM me the site URL and a SuperAdmin login and I'll take a quick look.

Bob

Hi mkusens,

Done. While I was there I added a location drop-down too as that demonstrates another kind of filter. Here's the WHERE code I used:

<?php
$gpao = JRequest::getString('gpao', '', 'post');
$metal = JRequest::getString('metal', '', 'post');
$location = JRequest::getString('location', '', 'post');
$where = array();
if ( $gpao ) {
  $where[] = " `gpao` LIKE '%$gpao%' ";
}
if ( $metal ) {
  $where[] = " `metal` LIKE '%$metal%' ";
}
if ( $location ) {
  $where[] = " `location` = '$location' ";
}
if ( count($where) ) {
  echo "WHERE ".implode(' AND ', $where);
}
?>

and the corresponding part of the Header code:

<?php
$script = "";
$script .= "
$('clear').addEvent('click', function() {
  $('gpao').value = '';
  $('metal').value = '';
  $('location').options.length = 1;
});
";
if ( $script ) {
  $doc =& JFactory::getDocument();
  $script = "window.addEvent('domready', function() { $script });";
  $doc->addScriptDeclaration($script);
}
$gpao = JRequest::getString('gpao', '', 'post');
$metal = JRequest::getString('metal', '', 'post');
$location = JRequest::getString('location', '', 'post');
$db =& JFactory::getDBO();
$query = "
    SELECT DISTINCT `location`
        FROM `#__chronoforms_bovetstock1`
        ORDER BY `location`;
";
$db->setQuery($query);
$data = $db->loadResultArray();
$locations = array();
$locations[] = "<option value=''>==?==</option>";
foreach ( $data as $d ) {
  $s = '';
  if ( $location == $d ) {
    $s = "selected='selected'";
  }
  $locations[] = "<option value='$d' $s >$d</option>";
}
?>

GPAO: <input type='text' name='gpao' id='gpao' value='<?php echo $gpao; ?>' /> 
Metal: <input type='text' name='metal' id='metal' value='<?php echo $metal; ?>' /> 
Location: <select name='location' id='location'><?php echo implode("", $locations); ?></select>
 <input type='submit'
name='filter' id='filter' value='Filter' /> <input type='submit' name='clear' id='clear' value='Clear' />

Bob

Bob - this worked like a charm. Thanks for all of your help. I just bought you a beer so please enjoy.

Can use the code above to help me with this issue I am having:

http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=12&t=21885

Thanks

Hi about2flip,

Yes.

Bob