A couple things...

I think I'm getting overly confused on my code here...

I'm attempting to create a search for my chronoconnectivity page that allows for search of specific database info. For example, the form I created was for business emergency contact registration, so in my chronoconnectivity page, I'd like to be able to search my database by Business Name, Street Name and Primary Contact Last name.

I was able to get the search the way I wanted it to look (with a dropdown specifying which column to search in and a text box to input the specific search term), but I can not figure out how to get the functionality to work... I've tried finding similar situations to look at the code that may be posted here, but unfortunately, the more example code I look at, the less it makes sense to me.

Here is a pic of what my search looks like now:




Additionally, I'm wondering how I can get that "View" button that I created to show the full database details for a particular entry? At this time, I simply want to show the limited info above on this page, but have the "view" button direct to the full details of that entry. I already tried index.php?option=chronocontact&chronoformname=bus_reg1&cf_id={cf_id} and that's just giving me a 404 error...

Thanks in advance for any assistance you can provide!

Christy

Hi christye1224 ,

Please check the tutorials here; in particular the dynamic filters and view details docs.

Bob

Thanks Bob.

I got the second thing (the view button) working perfectly!

I'm still struggling with the search even after reading the dynamic filter tutorial though. :s I'm either overthinking it or my situation differs a bit from what has been presented in that guide or on this forum.

You can see from the picture above that I'm filtering with a dropdown. The dropdown options are:

Business Name
Owner Last Name
Street Name

I'm assuming my biggest issue is that I don't know how to call the filter properly, though I am sure there are plenty of other errors in my code as well. At one point, I was trying to name my drop down "drop1" so I could use that as a reference, but that didn't seem to work either.

The function of the search is to allow someone to select how they want to search (Business Name, Owner Last Name or Street Name) from the drop down and then enter the term they are searching for in the text field so only that info is pulled up.

For example, say I want to run a search by street name for the property on that street. I'd select Street name from the drop down and then enter the name of the street in the text field "Curtis Lane". The table could then show all the listings on Curtis Lane.

In the Where SQL (if I follow your example in the tutorial and the supplementary thread here: http://chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=12&t=19788), it would be something like:

<?php
// create the empty array
$where = array();

// check the text filter
$filter_text = JRequest::getString('filter_text', '', 'post');
if ( $filter_text ) {
$where[] = "( `Business_Name` LIKE '%$filter_text%'
   OR `Street_Name` LIKE '%$filter_text%')";
   OR `Contact_1_Last_Name` LIKE '%$filter_text%')";
}

// check the response filter
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');
if ( count($filter_response) ) {
  // !! add the following three lines !!
  foreach ( $filter_response as $k => $v ) {
    $filter_response[$k] = "'$v'";
  }
  $filter = implode(', ', $filter_response);
  $where[] = " `NO IDEA WHAT TO PUT HERE` IN ($filter) ";
}?>

The Header code I have is:

<?php
JHTML::_('behavior.modal');
?>
<?php
$db =& JFactory::getDBO();
global $count;
$count = $db->loadResult();
$style = "";
$style .= "
table.cf_listing {
margin-bottom: 12px;
}
";
if ( $style) {
   $doc =& JFactory::getDocument();
   $doc->addStyleDeclaration($style);
}
$script = "";
$script .= "
$('clear').addEvent('click', function() {
   $('filter_text').value = '';
   $('filter_catid').selectedIndex = -1;
});
";
if ( $script ) {
   $doc =& JFactory::getDocument();
   $script = "window.addEvent('domready', function() { $script });";
   $doc->addScriptDeclaration($script);
}
$filter_text = JRequest::getString('filter_text', '', 'post');
$query = "
   SELECT `$filter_response'
      FROM `#__chronoforms_bus_reg` 
      ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<select name='filter_response[]' id='filter_response' multiple='multiple' size='5' > <?php
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array'); foreach ( $data as $d ) {
   $selected = '';
   if ( in_array($d->$filter_response) ) {
     $selected = "selected='selected'";
   }
   echo "<option value='".$d->$filter_response."' $selected >".$d->$filter_response."</option>";  
}
?>
</select>
<br />
<input type='text' name='filter_text' id='filter_text' value='<?php echo $filter_text; ?>'
/> <input type='submit' name='filter' id='filter' value='Filter' /> <input
type='button' name='clear' id='clear' value='Clear' /> <table class='cf_listing'>

<?php
if ( !$count ) {
echo "<div>Sorry, no results were found.</div>";
} else {
?> <br />
{new_record}<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 12%;">Business Name</td>
      <td style="font-weight: bold; width: 5%;">Street No.</td>
      <td style="font-weight: bold; width: 15%;">Street Name</td>
      <td style="font-weight: bold; width: 10%;">Unit No.</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Title</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact First Name</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Last Name</td>
      <td style="font-weight: bold; width: 15%;">Actions</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

Body Code:

<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>
      <td style="width: 12%;">{Business_Name}</td>
      <td style="width: 5%;">{Street_Number}</td>
      <td style="width: 15%;">{Street_Name}</td>
      <td style="width: 10%;">{Unit_No}</td>
      <td style="width: 15%;">{Contact_1_Title}</td>
      <td style="width: 15%;">{Contact_1_First_Name}</td>
      <td style="width: 15%;">{Contact_1_Last_Name}</td>
      <td style="width: 15%;"><a class='modal' href='index.php?option=com_chronocontact&chronoformname=busregdetails&cf_id={cf_id}&tmpl=component' rel="{handler: 'iframe', size: {x:800, y: 600}}"><u>View</u></a> {edit_record} {delete_record}</td>
    </tr>
  </tbody>
</table>
<?php $i++ ?>

Footer Code:

<?php
// get the row count and show the pagination if needed
global $count;
if ( $count ) {
?>
{pagination}
<?php
}
?>     
<br />

I am trying, but I'm really bad at this...

Hi christye1224,

I'll take a look at this in the morning. I haven't built a filter where one input is dependent on another. It should work OK - just needs thinking through with a clear head.

I usually take another approach and check all the columns: so filtering on 'Curtis' would find 'Curtis Lane' and 'Curtis Brothers Inc' and 'John Curtis Jr'.

Bob

Hi christye1224,

I can't see what this query does?

$query = "
   SELECT `$filter_response'
      FROM `#__chronoforms_bus_reg`
      ;
";

$filter_response isn't defined here as far as I can see (and the quotes are mis-matched which will break the query).

I'll assume that you have a multi-drop-down here (or a check-box array) with option values of business_name, owner_last_name and street_name

$filter_text = JRequest::getString('filter_text', '', 'post');
if ( !$filter_text ) {
  return;
}
// check the response filter
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');
// so filter_response is like array('business_name', 'street_name')
if ( !count($filter_response) ) {
  return;
}
$temp = array();
foreach ( $filter_response as $v ) {
  $temp[] = "`$k` LIKE '%$filter_text%'";
}
$where[] = "( ".implode(' OR ', $temp)." )";
?>

The resulting Where clause should look like:

WHERE ( `business_name LIKE '%xxx%' OR street_name LIKE '%xxx%' )

Bob

I can't see what this query does?

Code: Select all
$query = "
SELECT `$filter_response'
FROM `#__chronoforms_bus_reg`
;
";

$filter-response isn't defined here as far as I can see (and the quotes are mis-matched which will break the query).

Bob-

That was where I really got hung up... I took "$filtered response" to mean the selection from my drop down menu (ie- Business Name, Street Name or Owner Last Name)which are all database table values and I wanted to instruct to find the result for the search within that selected column of my jos_chronoforms_bus_reg table but to take the text input into consideration when offering up results.

Does that make sense?

It all comes back to one filter being dependent on another and the fact that the drop filter isn't specifically named and defined within the database table, I think.

I've made all of the edits you instructed me to above, except this one since I am not sure what I should be defining for a query. Still no luck getting it to work...

Hi christye1224 ,

And what does the query look like now?


Bob

Here's all the code as it is right now:

Where SQL:

<?php
// create the empty array
$where = array();

// check the text filter
$filter_text = JRequest::getString('filter_text', '', 'post');
if ( $filter_text ) {
WHERE ( `Business_Name` LIKE `%xxx%` OR `Street_Name` LIKE `%xxx%` OR `Contact_1_Last_Name` LIKE `%xxx%` )
$filter_text = JRequest::getString('filter_text', '', 'post');
if ( !$filter_text ) {
  return;
}
// check the response filter
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');
// so filter_response is like array('Business_Name', 'Street_Name', 'Contact_1_Last_Name')
if ( !count($filter_response) ) {
  return;
}
$temp = array();
foreach ( $filter_response as $v ) {
  $temp[] = "`$k` LIKE '%$filter_text%'";
}
$where[] = "( ".implode(' OR ', $temp)." )";
?>

Header:

<?php
JHTML::_('behavior.modal');
?>
<?php
$db =& JFactory::getDBO();
global $count;
$count = $db->loadResult();
$style = "";
$style .= "
table.cf_listing {
margin-bottom: 12px;
}
";
if ( $style) {
   $doc =& JFactory::getDocument();
   $doc->addStyleDeclaration($style);
}
$script = "";
$script .= "
$('clear').addEvent('click', function() {
   $('filter_text').value = '';
   $('filter_catid').selectedIndex = -1;
});
";
if ( $script ) {
   $doc =& JFactory::getDocument();
   $script = "window.addEvent('domready', function() { $script });";
   $doc->addScriptDeclaration($script);
}
$filter_text = JRequest::getString('filter_text', '', 'post');
$query = "
   SELECT `$filter_response`
      FROM `#__chronoforms_bus_reg`
      ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<select name='filter_response[]' id='filter_response' multiple='multiple' size='5' > <?php
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array'); foreach ( $data as $d ) {
   $selected = '';
   if ( in_array($d->$filter_response) ) {
     $selected = "selected='selected'";
   }
   echo "<option value='".$d->$filter_response."' $selected >".$d->$filter_response."</option>";
}
?>
</select>
<br />
<input type='text' name='filter_text' id='filter_text' value='<?php echo $filter_text; ?>'
/> <input type='submit' name='filter' id='filter' value='Filter' /> <input
type='button' name='clear' id='clear' value='Clear' /> <table class='cf_listing'>

<?php
if ( !$count ) {
echo "<div>Sorry, no results were found.</div>";
} else {
?> <br />
{new_record}<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 12%;">Business Name</td>
      <td style="font-weight: bold; width: 5%;">Street No.</td>
      <td style="font-weight: bold; width: 15%;">Street Name</td>
      <td style="font-weight: bold; width: 10%;">Unit No.</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Title</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact First Name</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Last Name</td>
      <td style="font-weight: bold; width: 15%;">Actions</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

Body:

<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>
      <td style="width: 12%;">{Business_Name}</td>
      <td style="width: 5%;">{Street_Number}</td>
      <td style="width: 15%;">{Street_Name}</td>
      <td style="width: 10%;">{Unit_No}</td>
      <td style="width: 15%;">{Contact_1_Title}</td>
      <td style="width: 15%;">{Contact_1_First_Name}</td>
      <td style="width: 15%;">{Contact_1_Last_Name}</td>
      <td style="width: 15%;"><a class='modal' href='index.php?option=com_chronocontact&chronoformname=busregdetails&cf_id={cf_id}&tmpl=component' rel="{handler: 'iframe', size: {x:800, y: 600}}"><u>View</u></a> {edit_record} {delete_record}</td>
    </tr>
  </tbody>
</table>
<?php $i++ ?>

Footer:

<?php
// get the row count and show the pagination if needed
global $count;
if ( $count ) {
?>
{pagination}
<?php
}
?>     
<br />

In the Header block, could I/should I define the query statement with all 3 calls- much like the array?

For example:

$query = "
   SELECT `business_name`, `street_name`, `contact_1_last_name`
      FROM `#__chronoforms_bus_reg`
      ;

???

Hi Christye1224,

In the Header block, could I/should I define the query statement with all 3 calls- much like the array?

No, ChronoConnectivity builds the query for you.

I have had a go in the following boxes but I should stress that buiding a CC listing without access the the database isn't the easiest of projects so my code may well need debugging.

I think that the WHERE box still has a stray query in it

<?php
// create the empty array
$where = array();
$filter_text = JRequest::getString('filter_text', '', 'post');
if ( !$filter_text ) {
  return;
}
// check the response filter
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');
if ( !count($filter_response) ) {
  return;
}
$temp = array();
foreach ( $filter_response as $v ) {
  $temp[] = "`$k` LIKE '%$filter_text%'";
}
$where[] = "( ".implode(' OR ', $temp)." )";
echo " WHERE ".implode(' AND ', $where)." ";
?>

The Header box:

<?php
$db =& JFactory::getDBO();
global $count;
$count = $db->loadResult();
JHTML::_('behavior.modal');
$style = "";
$style .= "
table.cf_listing {
margin-bottom: 12px;
}
";
if ( $style) {
   $doc =& JFactory::getDocument();
   $doc->addStyleDeclaration($style);
}
$script = "";
$script .= "
$('clear').addEvent('click', function() {
   $('filter_text').value = '';
   $('filter_response').selectedIndex = -1;
});
";
if ( $script ) {
   $doc =& JFactory::getDocument();
   $script = "window.addEvent('domready', function() { $script });";
   $doc->addScriptDeclaration($script);
}
?>
<select name='filter_response[]' id='filter_response' multiple='multiple' size='5' >
<?php
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array'); 
$options = array(
  'Business_Name' => 'Business name', 
  'Street_name' => 'Street name', 
  'Contact_1_Last_Name' => 'Last name'
);
foreach ( $options as $k => $v ) {
   $selected = '';
   if ( in_array($k, $filter_response ) ) {
     $selected = "selected='selected'";
   }
   echo "<option value='$k' $selected >$v</option>";
}
?>
</select>
<br />
<input type='text' name='filter_text' id='filter_text' value='<?php echo $filter_text; ?>'
/> <input type='submit' name='filter' id='filter' value='Filter' /> <input
type='button' name='clear' id='clear' value='Clear' /> <table class='cf_listing'>

<?php
if ( !$count ) {
  echo "<div>Sorry, no results were found.</div>";
} else {
?>
<br />{new_record}<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 12%;">Business Name</td>
      <td style="font-weight: bold; width: 5%;">Street No.</td>
      <td style="font-weight: bold; width: 15%;">Street Name</td>
      <td style="font-weight: bold; width: 10%;">Unit No.</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Title</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact First Name</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Last Name</td>
      <td style="font-weight: bold; width: 15%;">Actions</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

I hope this helps

Bob


Bob

Well, made the edits and it seems to be closer than it has been- at least the entries table is showing up on my page again, but the search field table is not... I'm getting a parse error:

Parse error: syntax error, unexpected $end in /home/trivita/public_html/unfairadvantagemarketing.net/components/com_chronoconnectivity/libraries/connection.php(246) : eval()'d code on line 72

Also, I see the php call for the modal which is suppose to pop up when the "view" button is clicked to show the full entry information, but the modal itself isn't working any longer.

Bob- if you wouldn't mind looking, I could send you the login for the site. I'm sure this sort of admin side filter and search of a database table could be beneficial to others here, so it would be great to figure out the code and be able to post some working code for others to build off of.🙂 I know I've learned a lot from what others have shared here...

Just noting that this was fixed off-line. Bob

Just noting that this was fixed off-line. Bob

Yes it was, and it's working absolutely amazing!😀 Thanks again, Bob, for your help.🙂

Here is the working code- for those that may be looking for a similar functionality:

WHERE SQL:

<?php
// create the empty array
$where = array();
$filter_text = JRequest::getString('filter_text', '', 'post');
//$mainframe->enqueuemessage('$filter_text: '.print_r($filter_text, true));
if ( !$filter_text ) {
  return;
}
// check the response filter
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');
//$mainframe->enqueuemessage('$filter_response: '.print_r($filter_response, true));
if ( !count($filter_response) ) {
  return;
}
$temp = array();
foreach ( $filter_response as $v ) {
  $temp[] = "`$v` LIKE '%$filter_text%'";
}
$where[] = "( ".implode(' OR ', $temp)." )";
echo " WHERE ".implode(' AND ', $where)." ";
?>

HEADER:

<?php
$db =& JFactory::getDBO();
global $count;
$count = $db->loadResult();
$filter_text = JRequest::getString('filter_text', '', 'post');
$filter_response =& JRequest::getVar('filter_response', array(), 'post', 'array');

JHTML::_('behavior.modal');
$style = "";
$style .= "
table.cf_listing {
margin-bottom: 12px;
}
";
if ( $style) {
   $doc =& JFactory::getDocument();
   $doc->addStyleDeclaration($style);
}
$script = "";
$script .= "
$('clear').addEvent('click', function() {
   $('filter_text').value = '';
   $('filter_response').selectedIndex = -1;
});
";
if ( $script ) {
   $doc =& JFactory::getDocument();
   $script = "window.addEvent('domready', function() { $script });";
   $doc->addScriptDeclaration($script);
}
?>
<select name='filter_response[]' id='filter_response' multiple='multiple' size='5' >
<?php

$options = array(
  'business' => 'Business name',
  'busstreet' => 'Street name',
  'buscon1last' => 'Last name'
);
foreach ( $options as $k => $v ) {
   $selected = '';
   if ( in_array($k, $filter_response ) ) {
     $selected = "selected='selected'";
   }
   echo "<option value='$k' $selected >$v</option>";
}
?>
</select>
<br />
<input type='text' name='filter_text' id='filter_text' value='<?php echo $filter_text; ?>'
/> <input type='submit' name='filter' id='filter' value='Filter' /> <input
type='button' name='clear' id='clear' value='Clear' /> <table class='cf_listing'>

<?php
if ( !$count ) {
  echo "<div>Sorry, no results were found.</div>";
} else {
?>
<br />{new_record}<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 12%;">Business Name</td>
      <td style="font-weight: bold; width: 5%;">Street No.</td>
      <td style="font-weight: bold; width: 15%;">Street Name</td>
      <td style="font-weight: bold; width: 10%;">Unit No.</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Title</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact First Name</td>
      <td style="font-weight: bold; width: 15%;">Primary Contact Last Name</td>
      <td style="font-weight: bold; width: 15%;">Actions</td>
      <td style="font-weight: bold; width: 15%;">Entry Time/Date</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>
<?php
}
?>

BODY:

<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>
      <td style="width: 12%;">{business}</td>
      <td style="width: 5%;">{busstreetno}</td>
      <td style="width: 15%;">{busstreet}</td>
      <td style="width: 10%;">{busunit}</td>
      <td style="width: 15%;">{buscontact1title}</td>
      <td style="width: 15%;">{buscon1first}</td>
      <td style="width: 15%;">{buscon1last}</td>
      <td style="width: 15%;"><a class='modal' href='index.php?option=com_chronocontact&chronoformname=busregdetails&cf_id={cf_id}&tmpl=component' rel="{handler: 'iframe', size: {x:800, y: 600}}"><u>View</u></a> {edit_record} {delete_record}</td>
      <td style="width: 15%;">{recordtime}</td>
    </tr>
  </tbody>
</table>
<?php $i++ ?>

FOOTER:

<?php
// get the row count and show the pagination if needed
global $count;
if ( $count ) {
?>
{pagination}
<?php
}
?>     
<br />