Forums

sorttable table ChronoConnectivity

tamarteleco 02 Jun, 2010
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!
GreyHead 02 Jun, 2010
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
tamarteleco 02 Jun, 2010
Thanks Bob,

It Will be great if you have a working solution!!
GreyHead 02 Jun, 2010
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
tamarteleco 02 Jun, 2010
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.
GreyHead 02 Jun, 2010
Hi tamartelco,

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

Bob
tamarteleco 02 Jun, 2010
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?
GreyHead 02 Jun, 2010
Hi Tamrateleco,

recortime should be recordtime

Bob
tamarteleco 02 Jun, 2010
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?
tamarteleco 02 Jun, 2010
In line 276:

eval("?>".${$part});
GreyHead 02 Jun, 2010
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
tamarteleco 02 Jun, 2010
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!
tamarteleco 02 Jun, 2010
With one column no error.
But it doesn't work 😟
tamarteleco 02 Jun, 2010
with one column, the java error console says:

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

tamarteleco 02 Jun, 2010
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
tamarteleco 02 Jun, 2010
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.
tamarteleco 02 Jun, 2010
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.
GreyHead 02 Jun, 2010
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
tamarteleco 03 Jun, 2010
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

tamarteleco 03 Jun, 2010
Its working now.

I replace

job_listing

to my corresponding connectionname.

I'm very grateful for all your help.
GreyHead 03 Jun, 2010
Hi tamarteleco,

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

Bob
tamarteleco 03 Jun, 2010
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!
GreyHead 03 Jun, 2010
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
GreyHead 03 Jun, 2010
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
tamarteleco 03 Jun, 2010
Thats perfect,
I'm going to try if I could implement it ini my ChronoConnectivity front-end table.

Thanks!
tamarteleco 03 Jun, 2010
Hi Bob,

Where do you close the <table> and <tbody> in the footer before </form>??
tamarteleco 03 Jun, 2010
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?
GreyHead 03 Jun, 2010
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
tamarteleco 03 Jun, 2010
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...
GreyHead 03 Jun, 2010
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
GreyHead 03 Jun, 2010
Hi Tamarteleco,

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

Bob
tamarteleco 03 Jun, 2010
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!
GreyHead 03 Jun, 2010
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
tamarteleco 04 Jun, 2010
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.
tamarteleco 07 Jun, 2010
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.
GreyHead 08 Jun, 2010
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
tamarteleco 08 Jun, 2010
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.
tamarteleco 08 Jun, 2010
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.
GreyHead 08 Jun, 2010
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
tamarteleco 08 Jun, 2010
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!
tamarteleco 08 Jun, 2010
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?
GreyHead 08 Jun, 2010
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
tamarteleco 09 Jun, 2010
Hi Bob,

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

In the footer there is almost this:

{pagination}

Thanks
GreyHead 09 Jun, 2010
Hi tamarteleco,

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

Bob
tamarteleco 16 Jun, 2010
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!.
tamarteleco 16 Jun, 2010
Sorry,

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

I'm going to try it.

Tamarteleco.
tamarteleco 16 Jun, 2010
Hi Bob,

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

Thanks!
This topic is locked and no more replies can be posted.