sorttable table ChronoConnectivity

Hi,

I had review all the topics for ChronoConnectivity and I think this doesn't appear.
I was trying to do the table in the fron-end sorttable,

I tried with : http://www.kryogenix.org/code/browser/sorttable/

I added the javascript, edit my tamplate to load it, no javascript erros. And the table has the 'clickable' header.

But it doesnt work, it doesn't sort the data.

¿Is there any way to make the table sorttable with ChronoConnectivity?

Thanks!

Hi tamartelco,

I do it using the code from the Joomla admin sortable columns. It's a bit tricky to work out but works fine. When I have a moment I'll look at the last site I did and pull out the code.

Bob

Thanks Bob,

It Will be great if you have a working solution!!

Hi Tamartelco,

Here's an edited extract from the Header box:

<?php
$filter_order =& JRequest::getString('filter_order', '', 'post');
$filter_order_Dir =& JRequest::getString('filter_order_Dir', '', 'post');
?>
<form name="adminForm" id="adminForm" method="post" action="index.php?option=com_chronoconnectivity&connectionname=job_listing">
<input type='hidden' name='filter_order' id='filter_order' value='<?=$filter_order?>' />
<input type='hidden' name='filter_order_Dir' id'filter_order_Dir' value='<?=$filter_order_Dir?>' />

<table style='width:100%; border-collapse:collapse;'>
<thead>
<tr>
<th><?=JHTML::_('grid.sort', 'Job title ', 'job_title', @$filter_order_Dir, @$filter_order )?></th>
<th style='max-width:25%;'><?=JHTML::_('grid.sort', 'Location ', 'location', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Salary range ', 'salary_to', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Closing date ', 'closing_date', @$filter_order_Dir, @$filter_order )?></th>
</tr>
</thead>
<tbody>

You can see that we are picking up values of 'filter_order' and 'filter_order_Dir' which identify the currently filtered column and the ASC/DESC setting. These are stored in hidden inputs to make sure that they are preserved (I think that I could use the Joomla 'GetUserState' code to do this better).

The <form> tag is explicitly included because we want the form to be called AdminForm to let us use the Joomla scripts. (You need to add </form> in the Footer box.)

The columns themselves use JHTML grid.sort to create the sortable header links.

Unfortunately to get this to work you also have to hack the ChronoConnectivity code :-( In libraries/connection.php add the hacked code in the buildQuery() function found around line 190. See the lined // hack . . . // end hack

	function buildQuery($connectionname)
	{
		global $mainframe;
		$session =& JFactory::getSession();
		$MyConnection =& CFChronoConnection::getInstance($connectionname);
		$WHERE = $MyConnection->buildWhere($connectionname);
		//$mainframe->enqueuemessage('$WHERE: '.print_r($WHERE, true));
		//todo: get appropriate connection from connections table to get its data!
		$query = "SELECT * FROM ".$MyConnection->connectionrow->tablenames;
		if($MyConnection->connectionrow->SQL){
			$query = $query." ".trim($WHERE);
		}
		//$mainframe->enqueuemessage($query);
		// hack
		if(trim($MyConnection->connectionparams('ordersql'))){
			//$query = $query." ORDER BY ".trim($MyConnection->connectionparams('ordersql'));
		}
		$filter_order  = JRequest::getString('filter_order', $MyConnection->connectionparams('ordersql'), 'post');
		$filter_order_Dir  = JRequest::getString('filter_order_Dir', 'ASC', 'post');
		if ( $filter_order ) {
			$query = $query." ORDER BY $filter_order $filter_order_Dir ";
		}
		// end hack
		$pageNav = $MyConnection->buildPageNav($MyConnection->connectionrow->name);
		
		$query .= " LIMIT $pageNav->limitstart,$pageNav->limit";	
		//$mainframe->enqueuemessage('Final query: '.$query);
		
		return $query;
	}

Bob

Thanks Bob,

I was trying. I change the header accordingly to your code, I added

</form>

in the footer, and I changed the

buildQuery()

function. But I have some erros.

I don't these fields are:

'job_title'
'location'
'salary_to'
'closing_date'

May be is the cause of my errors.
They are the field of the form?

{field_name}

Thanks.

Hi tamartelco,

Well, you'll need to replace those with the corresponding column Titles and database column names from your Connection table.

Bob

Thanks Bob,

That was what I was doing.

With my header like that:

<?php
$filter_order =& JRequest::getString('filter_order', '', 'post');
$filter_order_Dir =& JRequest::getString('filter_order_Dir', '', 'post');
?>
<form name="adminForm" id="adminForm" method="post" action="index.php?option=com_chronoconnectivity&connectionname=job_listing">
<input type='hidden' name='filter_order' id='filter_order' value='<?=$filter_order?>' />
<input type='hidden' name='filter_order_Dir' id'filter_order_Dir' value='<?=$filter_order_Dir?>' />

<table style="text-align: left; color:white;background-color:#6389B4;padding:5px;" border="0" cellpadding="2" cellspacing="0">
  <thead>
    <tr>
      <th style="font-weight: bold;font-size:12px; width: 8%;"><?=JHTML::_('grid.sort','Date','recortime',@$filter_order_Dir,@$filter_order )?></th>
      <th style="font-weight: bold;font-size:12px; width: 20%;"><?=JHTML::_('grid.sort','field1','form_text_improvement',@$filter_order_Dir,@$filter_order )?></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;"><?=JHTML::_('grid.sort','field2','form_combo_importance',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><?=JHTML::_('grid.sort','field3','form_requestor',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><?=JHTML::_('grid.sort','field4','form_combo_department',@$filter_order_Dir,@$filter_order=?></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;">dummy_field</th>
    </tr>
	</thead>
  </tbody>

And my table:

CREATE TABLE IF NOT EXISTS `bcc_chronoforms_improvementidea_form` (
  `cf_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `recordtime` varchar(255) NOT NULL,
  `ipaddress` varchar(255) NOT NULL,
  `cf_user_id` varchar(255) NOT NULL,
  `form_requestor` text NOT NULL,
  `form_text_improvement` text NOT NULL,
  `form_text_future_situation` text NOT NULL,
  `form_text_affected` text NOT NULL,
  `form_text_process_affected` text NOT NULL,
  `form_text_tools_affected` text NOT NULL,
  `form_combo_department` varchar(255) NOT NULL,
  `form_combo_importance` varchar(255) NOT NULL,
  PRIMARY KEY (`cf_id`)
)

Maybe the recortime column?

Hi Tamrateleco,

recortime should be recordtime

Bob

Hi Bob,

It appears the same error:

Parse error: parse error in C:\0000\www\bcc\components\com_chronoconnectivity\libraries\connection.php(276) : eval()'d code on line 16
Call Stack
#	Time	Memory	Function	Location
1	0.0013	70976	{main}( )	..\index.php:0
2	0.2027	6933944	JSite->dispatch( )	..\index.php:68
3	0.2040	6936568	JComponentHelper->renderComponent( )	..\application.php:124
4	0.2090	7109936	require_once( 'C:\qmd\www\bcc\components\com_chronoconnectivity\chronoconnectivity.php' )	..\helper.php:162
5	0.2220	7470856	showmainlist( )	..\chronoconnectivity.php:61
6	0.2235	7491232	HTML_ChronoConnectivity::showmainlist( )	..\chronoconnectivity.php:87
7	0.2249	7492624	CFChronoConnection->evalPart( )

That this is related to the file connectivity.php?

In line 276:

eval("?>".${$part});

Hi tamarteleco,

Line 16 is

      <th style="font-weight: bold;font-size:12px; width: 15%;"><?=JHTML::_('grid.sort','field4','form_combo_department',@$filter_order_Dir,@$filter_order=?></th>

I think that should be

@$filter_order?></th>

at the end, there's an extra = in there.

Bob

Ups! Sorry, I corrected, but still appears the same:

( ! ) Parse error: parse error in C:\qmd\www\bcc\components\com_chronoconnectivity\libraries\connection.php(276) : eval()'d code on line 16
Call Stack
#	Time	Memory	Function	Location
1	0.0012	70976	{main}( )	..\index.php:0
2	0.1654	6933944	JSite->dispatch( )	..\index.php:68
3	0.1667	6936568	JComponentHelper->renderComponent( )	..\application.php:124
4	0.1717	7109936	require_once( 'C:\qmd\www\bcc\components\com_chronoconnectivity\chronoconnectivity.php' )	..\helper.php:162
5	0.1834	7470856	showmainlist( )	..\chronoconnectivity.php:61
6	0.1849	7491232	HTML_ChronoConnectivity::showmainlist( )	..\chronoconnectivity.php:87
7	0.1866	7492624	CFChronoConnection->evalPart( )

With the header:

<?php
$filter_order =& JRequest::getString('filter_order', '', 'post');
$filter_order_Dir =& JRequest::getString('filter_order_Dir', '', 'post');
?>
<form name="adminForm" id="adminForm" method="post" action="index.php?option=com_chronoconnectivity&connectionname=job_listing">
<input type='hidden' name='filter_order' id='filter_order' value='<?=$filter_order?>' />
<input type='hidden' name='filter_order_Dir' id='filter_order_Dir' value='<?=$filter_order_Dir?>' />

<table style="text-align: left; color:white;background-color:#6389B4;padding:5px;" border="0" cellpadding="2" cellspacing="0">
  <thead>
    <tr>
      <th style="font-weight: bold;font-size:12px; width: 8%;"><?=JHTML::_('grid.sort','Date','recordtime',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 20%;"><?=JHTML::_('grid.sort','field1','form_text_improvement',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;"><?=JHTML::_('grid.sort','field2','form_combo_importance',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><?=JHTML::_('grid.sort','field3','form_requestor',@$filter_order_Dir,@$filter_order)?></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><?=JHTML::_('grid.sort','field4','form_combo_department',@$filter_order_Dir,@$filter_order?></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;">dummy_field</th>
    </tr>
	</thead>
  </tbody>

May be I cant test with one column?

Thanks again!

With one column no error.
But it doesn't work 😟

with one column, the java error console says:

tableOrdering is not defined:
javascript:tableOrdering('redortime','desc','');

Now I have all the columns working, there were a ')' missing.

But now I have the javaScript error:

Error: tableOrdering is not defined
Source File: javascript:tableOrdering('form_requestor','desc','');
Line: 1
Error: tableOrdering is not defined
Source File: javascript:tableOrdering('form_combo_importance','desc','');
Line: 1
Error: tableOrdering is not defined
Source File: javascript:tableOrdering('form_combo_department','desc','');
Line: 1
Error: tableOrdering is not defined
Source File: javascript:tableOrdering('form_requestor','desc','');
Line: 1

And the html code generated for the header where the js 'javascript:tableOrdering' is

<form action="index.php?option=com_chronoconnectivity&connectionname=status_overview" method="post" name="connectivity"><form name="adminForm" id="adminForm" method="post" action="index.php?option=com_chronoconnectivity&connectionname=job_listing">
<input type='hidden' name='filter_order' id='filter_order' value='' />
<input type='hidden' name='filter_order_Dir' id='filter_order_Dir' value='' />

<table style="text-align: left; color:white;background-color:#6389B4;padding:5px;" border="0" cellpadding="2" cellspacing="0">
  <thead>
    <tr>
      <th style="font-weight: bold;font-size:12px; width: 8%;"><a href="javascript:tableOrdering('recortime','desc','');" title="Click to sort by this column">Date</a></th>

      <th style="font-weight: bold;font-size:12px; width: 20%;"><a href="javascript:tableOrdering('form_text_improvement','desc','');" title="Click to sort by this column">Idea description</a></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;"><a href="javascript:tableOrdering('form_combo_importance','desc','');" title="Click to sort by this column">Status</a></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><a href="javascript:tableOrdering('form_requestor','desc','');" title="Click to sort by this column">Requester</a></th>
      <th style="font-weight: bold;font-size:12px; width: 15%;"><a href="javascript:tableOrdering('form_combo_department','desc','');" title="Click to sort by this column">Quality Officer</a></th>
      <th style="font-weight: bold;font-size:12px; width: 5%;">Decision maker</th>
    </tr>

	</thead>
  </tbody>
</table>

Do I have to include any javaScript in the template?

Thanks.

I added

<script language="javascript" type="text/javascript">
function tableOrdering( order, dir, task )
{
        var form = document.adminForm;
 
        form.filter_order.value = order;
        form.filter_order_Dir.value = dir;
        document.adminForm.submit( task );
}
</script>

in the head template section,

But now appears:

Error: form is undefined
Source File: http://devbcc/index.php?option=com_chronoconnectivity&Itemid=4
Line: 219

From this point I can't follow.

Did you had the javaScript function somewhere else?

Thanks.

Hi tamarteleco,

That's part of the joomla.javascript.js which is usually loaded automatically if I recall correctly.

Perhaps you still have the ChronoConnectivity <form> tags turned on, those need to be turned off as you are adding them manually.

Bob

Hi Bob,

I turn it off, and now no javascript errors, but when I click the header title to short it appears:

There is no connection with this name

Its working now.

I replace

job_listing

to my corresponding connectionname.

I'm very grateful for all your help.

Hi tamarteleco,

Great, sorry I didn't think of that change :-(

Bob

Is working perfect, thanks!

Now, next step I have to do is a filter (combobox) to filter the front-end table by the contect of one column. This column has

High, Medium and Low

in it. This is entered by the user with the Chronoform form, when the user use the option in a combobox field with (High, Medium, Low) and send the form, this values are stored in the database in the column 'form_combo_importance'.

Is it possible to implement this with ChronoConnectivity? I saw various form topics, but I dont know wich is the most usuful/recent...

Thanks!

Hi tamarteleco,

Yes, you add a little form input in the header and add the output to the WHERE code box.

I have to go out now, will post an example later.

Bob

Hi tamarteleco,

This is quite a long post - apologies. It's taken from the same ChronoConnection as the sort part, so I've left those lines of code in as well.

The finshed version looks like this.
[attachment=0]03-06-2010 15-53-33.png[/attachment]
You can see that there are three multi-select boxes and a text filter box. You may not need all of these but it's helpful to see how they work together.

The full header box code is here. (Note that at the end there is a function that I wrote to create the select boxes, you could hand code these instead - or copy the code over from a ChronoForm.

<?php
/* ensure that this file is called by another file */
defined('_JEXEC') or die('Restricted access');

include(JPATH_SITE.DS.'components'.DS.'com_chronocontact'.DS.'includes'.DS.'job_list'.DS.'arrays.php');
$filter =& JRequest::getString('filter', '', 'post');
$filter_order =& JRequest::getString('filter_order', '', 'post');
$filter_order_Dir =& JRequest::getString('filter_order_Dir', '', 'post');
$location =& JRequest::getVar('location', array(), 'post');
$salary =& JRequest::getVar('salary', array(), 'post');
$job_type =& JRequest::getVar('job_type', array(), 'post');

$count_rows = 0;
global $count_rows;
//echo '<div>$lists: '.print_r($lists, true).'</div>';
//jimport('joomla.filesystem.file');
JHTML::_('behavior.mootools');
$scripts = $styles = array();
$scripts[] = JURI::base().'components/com_chronoconnectivity/assets/smoothbox.js';
$styles[] = JURI::base().'components/com_chronoconnectivity/assets/smoothbox.css';
$script = "
function clearFilter() {
	$('filter').value = '';
	$('salary').selectedIndex = -1;
	$('location').selectedIndex = -1;
	$('job_type').selectedIndex = -1;
	$('adminForm').submit();
}
window.addEvent('domready', function() {
	$('filter_reset').addEvent('click', clearFilter );
});
";
?>
<form name="adminForm" id="adminForm" method="post" action="index.php?option=com_chronoconnectivity&connectionname=job_listing">
<div style='padding:6px 0;'>
<table style='width:100%;'>
<tr>
<td><?=createSelect('Location', 'location', $location_array, $location )?></td>
<td> </td>
<td><?=createSelect('Salary (max)', 'salary', $salary_array, $salary )?></td>
<td> </td>
<td><?=createSelect('Job type', 'job_type', $type_array, $job_type )?></td>
</tr>
</table>
    <input type='text' name='filter' id='filter' value='<?=$filter?>' /> 
    <input type='submit' name='filter_submit' id='filter_submit' value='Filter' /> 
    <input type='button' name='filter_reset' id='filter_reset' value='Clear all' />
    <input type='hidden' name='filter_order' id='filter_order' value='<?=$filter_order?>' />
    <input type='hidden' name='filter_order_Dir' id'filter_order_Dir' value='<?=$filter_order_Dir?>' />
</div>

<table style='width:100%; border-collapse:collapse;'>
<thead>
<tr>
<th><?=JHTML::_('grid.sort', 'Job title ', 'job_title', @$filter_order_Dir, @$filter_order )?></th>
<th style='max-width:25%;'><?=JHTML::_('grid.sort', 'Location ', 'location', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Salary range ', 'salary_to', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Closing date ', 'closing_date', @$filter_order_Dir, @$filter_order )?></th>
</tr>
</thead>
<tbody>

<?php
$doc =& JFactory::getDocument();
if ( $script ) {
	$doc->addScriptDeclaration($script);
}
if ( count($scripts) ) {
    foreach ($scripts as $script ) {
        $doc->addScript($script);
    }
}
if ( count($styles) ) {
    foreach ($styles as $style ) {
        $doc->addStylesheet($style);
    }
}

function createSelect($title='', $name='', $option_array=array(), $values=array() )
{
	if ( !is_array($option_array) ) {
    	return;
    }
?>
    <div class="form_item">
      <div class="form_element cf_slect">
        <label class="cf_label" style="width: 150px; font-weight:bold;"><?=$title?></label>
        <div class="float_left">
        	<select name='<?=$name?>[]' id='<?=$name?>' multiple='multiple' size='9'  style="width: 150px;">
<?php
	$i = 0;
	$select_array = array();
	foreach ( $option_array as $k => $v ) {
		$selected = '';
		if ( in_array($k, $values ) ) {
			$selected = "selected='selected'";
		}
		$select_array[] = "<option value='$k' $selected >$v</option>";
	}
	echo implode('<br />', $select_array); 
?>
			</select>
        </div>
      </div>
      <div class="cfclear"> </div>
    </div>
<?php
}
?>

The code starts by using JRequest to get the variables from the last submission so that the last search values are re-displayed.

Then some scripts are loaded, the smoothbox scripts are used to show the details of a single record. The script snippet is linked to the 'Clear filter' button.
At the start of the form is a table containing the select inputs followed by inputs for the text filter and the Search & Clear buttons.

There is some PHP to load the css & scripts and finally the createSelect() function.

The remaining code is in the WHERE box

<?php
/* ensure that this file is called by another file */
defined('_JEXEC') or die('Restricted access');

$path = JPATH_SITE.DS.'components'.DS.'com_chronocontact'.DS.'includes'.DS.'job_list'.DS;
include($path.'arrays.php');
global $mainframe, $task;
$limit   = $mainframe->getUserStateFromRequest("$option.limit", 'limit', 14, 'int');
$limitstart  = JRequest::getInt('limitstart');

$filter 	=& JRequest::getString('filter', '', 'post');
$location 	=& JRequest::getVar('location', array(), 'post');
$salary 	=& JRequest::getVar('salary', array(), 'post');
$job_type 	=& JRequest::getVar('job_type', array(), 'post');
$task 	=& JRequest::getString('task', '', 'get');
//$mainframe->enqueuemessage('$task: '.print_r($task, true));

$where = $where_filter = $where_salary = array();

if ( $task == 'show_deleted' ) {
	$where[] = " ( `published` = 0 OR CAST(`closing_date` AS DATE) <= CURDATE() ) ";
} else {
	$where[] = " ( `published` = 1 AND CAST(`closing_date` AS DATE) > CURDATE() ) ";
}
if ( $filter ) {
	$where_filter[] = "`job_title` LIKE '%$filter%' ";
	$where_filter[] = "`company` LIKE '%$filter%' ";
	$where_filter[] = "`job_description` LIKE '%$filter%' ";
	$where_filter[] = "`job_ref` LIKE '%$filter%' ";

    if ( !empty($where_filter) ) {
        $where[] = "( ".implode(' OR ', $where_filter)." ) ";
    }
    //echo " LIMIT $limitstart, $limit ";
}
if ( count($location) ) {
	foreach ( $location as $k => $v ) {
		$location[$k] = "'$v'";
	}
	$where[] = " `location` IN ( ".implode(', ', $location)." ) ";
}

if ( count($salary) ) {
	$min = 0;
	$where_salary[] = " ( `salary_to` + `salary_from` = 0 ) ";
	foreach ( $salary_array as $k => $v ) {
		if ( in_array($k, $salary) ) {
			$where_salary[] = " ( `salary_to` >= $min AND `salary_to` < $k ) ";
			$where_salary[] = " ( `day_rate_to` >= $min/200 AND `day_rate_to` < $k/200 ) ";
		}
		$min = $k;
	}
	if ( !empty($where_salary) ) {
        $where[] = "( ".implode(' OR ', $where_salary)." ) ";
    }
	reset($salary_array);
}

if ( count($job_type) ) {
	foreach ( $job_type as $k => $v ) {
		$job_type[$k] = "'$v'";
	}
	$where[] = " `job_type` IN ( ".implode(', ', $job_type)." ) ";
}

if ( !empty($where) ) {
	//$mainframe->enqueuemessage(" WHERE ".implode(' AND ', $where));
	echo " WHERE ".implode(' AND ', $where);
}
?>

This loads in a set of arrays used for the select box values then uses JRequest to get the data from the form.

Most of the rest of the code is a series of tests to check if there is a value set in the search boxes and then to build a corresponding code snippet to go into the WHERE part of the MySQL query.

The one for the text filter is

if ( $filter ) {
	$where_filter[] = "`job_title` LIKE '%$filter%' ";
	$where_filter[] = "`company` LIKE '%$filter%' ";
	$where_filter[] = "`job_description` LIKE '%$filter%' ";
	$where_filter[] = "`job_ref` LIKE '%$filter%' ";

    if ( !empty($where_filter) ) {
        $where[] = "( ".implode(' OR ', $where_filter)." ) ";
    }
}

This checks if there is a filter and if there is it build ups a mySQL query in 4 columns from the database table. The final result might look like

( `job_title` LIKE '%xxx%' OR `company` LIKE '%xxx%' OR `job_description` LIKE '%xxx%' OR `job_ref` LIKE '%xxx%' )

Then right at the end the final WHERE statement is built with this snippet:

if ( !empty($where) ) {
	//$mainframe->enqueuemessage(" WHERE ".implode(' AND ', $where));
	echo " WHERE ".implode(' AND ', $where);
}

Apologies for the long post, didn't have time for the shorter one.

Bob

Thats perfect,
I'm going to try if I could implement it ini my ChronoConnectivity front-end table.

Thanks!

Hi Bob,

Where do you close the <table> and <tbody> in the footer before </form>??

In your code header, in the line

<input type='hidden' name='filter_order_Dir' id'filter_order_Dir' value='<?=$filter_order_Dir?>' />

an = is missing in

id'filter_order_Dir'

, is it?

Hi tamarteleco,

Yes there's an '=' missing there :-(

The tags are closed in the Footer box - here's the code.

</tbody>
</table>
<br />
</form>
<?php
global $count_rows;
if ( !$count_rows ) {
  echo '<div>No records found.</div>';
}
?>

Bob

Thanks for the footer.

I have one doubt,
In your table you display : "location", "salary_to","closing_date",

<th><?=JHTML::_('grid.sort', 'Job title ', 'job_title', @$filter_order_Dir, @$filter_order )?></th>
<th style='max-width:25%;'><?=JHTML::_('grid.sort', 'Location ', 'location', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Salary range ', 'salary_to', @$filter_order_Dir, @$filter_order )?></th>
<th><?=JHTML::_('grid.sort', 'Closing date ', 'closing_date', @$filter_order_Dir, @$filter_order )?></th>

But in the WHERE clause it search by "job_title, "company", "job_description", "job_ref"

( `job_title` LIKE '%xxx%' OR `company` LIKE '%xxx%' OR `job_description` LIKE '%xxx%' OR `job_ref` LIKE '%xxx%' )

I don't understand why in the where the are 4 items, but in the displaying table, and they are different columns...

Hi tamarteleco,

Not all the data columns are shown in the table. There's a 'View Details' link that show more. So the job-description for example is searched if you enter say 'charity' in the filter box. But the detail isn't shown in the ChronoConnectivity listing.

Bob

I was trying the code, but many erros, and can't solve all of them,

But I think I need something much simpler.Meybe you could understend with this picture:

See the picture link:
https://docs.google.com/leaf?id=0B9xbQ1Y_SVoJY2Y1OGE3NjAtZjNiYy00YTJmLTk5NTAtM2JlMTZhYmVlZTQ5&hl=en
I want to put a combobox filter like in the picture.

You think is possible?

Thanks.

Hi Tamarteleco,

That's exactly the same as just *one* of the select boxes I used. The 'job tyoe' one is almost identical.

Bob

Ok,

I will try to solve the important errors.

But for example if I use only one column to filter, what I have to do with this code:

if ( !empty($where_filter) ) {
            $where[] = "( ".implode(' OR ', $where_filter)." ) ";
        }

It adds the string OR, but I will have only one array... I need to clean the code. I will try again and come back.

Thanks!

Hi tamarteleco,

You can just leave it. It will work OK with 1 or 2 or 99 or 0 strings.

It adds the OR between the string so if there is only one then no OR is added.

Bob

Thanks Bob,

I'm still trying, the main errors comes from the WHERE box.

I will ask you when I find a more expecific error.

Hi Bob,

I'm keeping trying to make working the filter...
Now, I have a warning that says:

.......\components\com_chronocontact\includes\job_list\arrays.php) [function.include]: failed to open stream: No such file or directory in

Should I need arrays.php?

Thanks.

Hi tamarteleco,

You can comment out that line - but you may then get some other errors.

It's used to import the arrays that are used to create the options for the select boxes.

Bob

Hi Bob,

Thanks for your response,

I've commented it. No new error. I have one that was before that says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=SELECT count(*) FROM bcc_chronoforms_improvementidea_form WHERE
Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\bcc\components\com_chronoconnectivity\libraries\connection.php  on line 330
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=SELECT count(*) FROM bcc_chronoforms_improvementidea_form WHERE

Do you know if I need to change anything in connection.php? or in the WHERE BOX?

Thanks.

I found that the query comes from the WHERE BOX where it says:

$where = $where_filter = $where_salary = array();

if ( $task == 'show_deleted' ) {
   $where[] = "  ";
} else {
//test
   $where[] = " form_combo_importance = High";
}

What I should put in this where array?

-----

I commented and is working now!!!!

I'm going to put the $where like it was to control the publishing.

Thanks.

Hi Bob,

Definetly it doesn't work with the clause

if ( $task == 'show_deleted' ) 

I think it uses columns that I dont have in my table like:
published,closing_date

Isn't it?

Another question I have is what the clear all button does?

Thanks.

Hi tamarteleco,

There's quite a bit of code in there that is specific to my application. It would take too long to pull it all out before posting here. If in doubt, comment the lines out and test without them.

The entries 'show_deleted', 'published', 'closing_date' are all inputs from my form and table that may well not exist in yours.

Bob

Hi Bob,

Thanks for your response.

Commenting the part:

$where = $where_filter = $where_salary = array();

if ( $task == 'show_deleted' ) {
   $where[] = "  ";
} else {
//test
   $where[] = " form_combo_importance = High";
}

and the one that call 'arrays.php' and adapting to my table columns
Works perfect!!!. Thanks.

I think I'm going to remove the button "Clear all" next to 'filter' button because I don't see the point to use it in my form...

Next step, I have to do is a Search next to the filter. To search in the ChronoConnectivity table. I think I saw in the forum some post related to it. I go to have a look.

Thanks for your invaluable help!

Hi Bob,

Now I have the filter working. But "Enable forms tags" are disabled. So, then pagination is not working.

How can I get both? I mean, pagination + the filter?

I also made a deep search to all the forum topics about the solution to add a Search to ChronoConnectivity and all seems to need "Enable forms tags" enable.

Do you think that it will be possible to implement the search with the filter and pagination?

Hi tamartelkeco,

If I remember correctly there were form tags included in the Header & footer box code. You need these added manually because the form name needs to be AdminForm for the column sorting to work correctly. The CC form tags must be left off.

Bob

Hi Bob,

Do you know wich is the tag for the pagination then?

In the footer there is almost this:

{pagination}

Thanks

Hi tamarteleco,

That's right and it must come before </form> in the same box.

Bob

Hi Bob,

My pagination doesn't work yet.
Two post ago you wrote me:

Hi tamartelkeco,

If I remember correctly there were form tags included in the Header & footer box code. You need these added manually because the form name needs to be AdminForm for the column sorting to work correctly. The CC form tags must be left off.

Bob

I have in my footer box :

{pagination}

What do you mean when you say:

You need these added manually

What I'm missing?
Thanks, the filter and the ordering works very well!.

Sorry,

I didn't try to put it before </form>

I'm going to try it.

Tamarteleco.

Hi Bob,

Pagination works now. I put it before </form> as you said and work fine.

Thanks!