Can anyone see the error in this bit of code?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tsp003
Forum Newbie
Posts: 16
Joined: Thu Apr 21, 2011 1:39 am

Can anyone see the error in this bit of code?

Post 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);
}
Last edited by califdon on Fri May 20, 2011 11:46 pm, edited 1 time in total.
Reason: Edited by moderator to enclose php script in <syntax=php> tags, to make script more readable. Please do this when you post code.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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";
tsp003
Forum Newbie
Posts: 16
Joined: Thu Apr 21, 2011 1:39 am

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

Post 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 :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
tsp003
Forum Newbie
Posts: 16
Joined: Thu Apr 21, 2011 1:39 am

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

Post 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.
Post Reply