Page 1 of 1

Can anyone see the error in this bit of code?

Posted: Fri May 20, 2011 1:33 pm
by tsp003
This code is not displaying as i'm expecting, namely the state field which is displaying the state id and not the actual text of that state - the table references are correct and i have tried all sorts of ways of tinkering with the text, but always the id is dispalyed, i.e "65, United States" is whats showing, when I want "Arizona, United States"

Can anyone see where the problem is here. The country is coming out just fine, so am very confused.

I'd be forever grateful and slightly less grey-er , thank you :D

Code: Select all

//searchBySpec
function searchBySpec($option){
	global $mainframe;
	$user	=& JFactory::getUser();
	$id 	= (int)JRequest::getVar('id', 0, 'get', 'string');
	if(empty($id))
	{
		$return = JRoute::_('index.php?option=com_tpjobs');
		$mainframe->redirect($return);
		return;
	}
	// Initialize variables
	$db 	= & JFactory::getDBO();		
	
	//for header title
	$query = "select a.id,specialization,category ".
		 		" from #__tpjobs_job_spec a ".
				" left join #__tpjobs_job_categ b".
				" ON a.id_category = b.id".
				" where a.id = ".$db->quote( $id );
				
	$db->setQuery($query);	
	$categ = $db->loadObjectList();
	$spec = (!empty($categ[0])) ? $categ[0] : null;

	$keyword="";
	$where ="where a.job_title like '%".$keyword."%'";
	$where .=" and id_job_spec =".$db->quote( $id )." ";
	$now = date('d-m-Y H:i:s', time() + ( $mainframe->getCfg('offset') * 60 * 60 ) );
	$where .= "and a.is_active='y' and expire_date > '".$now."' and expire_date <> '0000-00-00 00:00:00'";

	$limit		= $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');

	$limitstart			= JRequest::getVar('limitstart',0,'','int');
	
	
	$query = "SELECT COUNT(*)".	          
			 " from #__tpjobs_job a".
	      	 " left join #__tpjobs_country b".
			 " ON a.id_country = b.country".
		 	 " left join #__tpjobs_employer c".
			 " ON a.employer_id = c.user_id".
			 " left join #__tpjobs_comp_type d".
			 " ON c.id_comp_type = d.id ".$where.
			 " LEFT JOIN  #__tpjobs_state e".
			 " ON a.state = e.id".
			 " ORDER BY a.publish_date DESC";
			 	
	
	$db->setQuery( $query );
	$total = $db->loadResult();
	
	jimport('joomla.html.pagination');
	$pageNav = new JPagination( $total, $limitstart, $limit );


	$query ="select a.*,country,comp_name,comp_type,state ".
			 " from #__tpjobs_job a".
	      	 " left join #__tpjobs_country b".
			 " ON a.id_country = b.id".
		 	 " left join #__tpjobs_employer c".
			 " ON a.employer_id = c.user_id".
			 " left join #__tpjobs_comp_type d".
			 " ON c.id_comp_type = d.id ".$where.
			 " LEFT JOIN  #__tpjobs_state e".
			 " ON a.state = e.state".
			 " ORDER BY a.publish_date DESC";
	
	
	$db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
	
	$rows = $db->loadObjectList();	
	
	
	HTML_front_tpjobs::searchBySpec($rows,$spec,$pageNav,$option);
}

Re: Can anyone see the error in this bit of code?

Posted: Fri May 20, 2011 1:44 pm
by mikosiko
you meant this query?
$query ="select a.*,country,comp_name,comp_type,state ".
" from #__tpjobs_job a".
" left join #__tpjobs_country b".
" ON a.id_country = b.id".
" left join #__tpjobs_employer c".
" ON a.employer_id = c.user_id".
" left join #__tpjobs_comp_type d".
" ON c.id_comp_type = d.id ".$where.
" LEFT JOIN #__tpjobs_state e".
" ON a.state = e.state".
" ORDER BY a.publish_date DESC"
;

and same for this?
$query = "SELECT COUNT(*)".
" from #__tpjobs_job a".
" left join #__tpjobs_country b".
" ON a.id_country = b.country".
" left join #__tpjobs_employer c".
" ON a.employer_id = c.user_id".
" left join #__tpjobs_comp_type d".
" ON c.id_comp_type = d.id ".$where.
" LEFT JOIN #__tpjobs_state e".
" ON a.state = e.id".
" ORDER BY a.publish_date DESC";

Re: Can anyone see the error in this bit of code?

Posted: Fri May 20, 2011 2:12 pm
by tsp003
Its not my code, and am quite inexperienced with php, but removing that 'where' made no difference whatsoever to the output.

Am still very confused

thanks for the reply :)

Re: Can anyone see the error in this bit of code?

Posted: Fri May 20, 2011 8:27 pm
by mikosiko
maybe you need to post your tables.... my first guess is that your table "state" has a state_name field or similar and seems that you are displaying the "state" code instead.

probably also you don't need to eliminate the "where"... just to place it correctly in the sql

Re: Can anyone see the error in this bit of code?

Posted: Fri May 20, 2011 11:58 pm
by califdon
tsp003 wrote:This code is not displaying as i'm expecting, namely the state field which is displaying the state id and not the actual text of that state - the table references are correct and i have tried all sorts of ways of tinkering with the text, but always the id is dispalyed, i.e "65, United States" is whats showing, when I want "Arizona, United States"
...
Can anyone see where the problem is here. The country is coming out just fine, so am very confused.

Code: Select all

	$query ="select a.*,country,comp_name,comp_type,state ".
			 " from #__tpjobs_job a".
	      	 " left join #__tpjobs_country b".
			 " ON a.id_country = b.id".
		 	 " left join #__tpjobs_employer c".
			 " ON a.employer_id = c.user_id".
			 " left join #__tpjobs_comp_type d".
			 " ON c.id_comp_type = d.id ".$where.
			 " LEFT JOIN  #__tpjobs_state e".
			 " ON a.state = e.state".
			 " ORDER BY a.publish_date DESC";
	
Here's how to look at it. You are selecting a field named "state" and that is what MySQL is delivering. So the field you are asking for contains the state code, not the state name. Looking at your query, above, note that 2 different tables contain a field named "state" that is used to join the 2 tables. This means that it is surely a code, not a name. Therefore, I would expect that there's another field in the state table that contains the name that corresponds with each code. Look at the structure of the state table and you should be able to see the field that you need. I'm surprised that query didn't generate an error, because in the first line, where you select the fields, you did not specify which "state" field you want (even though they will both contain the same value, since it's being used to join the tables). So where you now have:
$query ="select a.*,country,comp_name,comp_type,state "
change state to the name of the correct field of the state table.

Re: Can anyone see the error in this bit of code?

Posted: Sat May 21, 2011 4:49 am
by tsp003
Thanks so musch for replying.

I have checked and actually re-done the table so includes the only the fields 'id' and 'state' for state name.

The problem is still there - the number shown represents the 'id' field and it still isn't displaying the text.

Still very stumped.