hiding an expired record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

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

hiding an expired record

Post by tsp003 »

Hello, this one is driving me a little crazy

I have inherited a site with the following code which is 'supposed' hide records that have passed the date entered in the 'expire_date_ field, but it simply doesn't

Code: Select all

//searchByCateg
function searchBySpec(&$rows,$spec,&$pageNav,$option)
{	
		$user = & JFactory::getUser();	
		$id   = (int)JRequest::getVar('id', 0, 'get', 'string');	
		$post   = JRequest::get('post');
		if($id==0){
			$id= (!empty($post['id'])) ? $post['id'] : null;
		}
		$action	= JRoute::_('index.php?option=com_tpjobs&task=searchbycateg&id='.$id);
		$is_employer  = isEmployer($user->id);
		$is_jobseeker = isJobSeeker($user->id);
		global $mainframe;
	$config = & JComponentHelper::getParams( 'com_tpjobs' );
	$now = date('Y-m-d H:i:s', time() + ( $mainframe->getCfg('offset') * 60 * 60 ) );
	$where = "b.is_active='y' and b.expire_date > '".$now."' and b.expire_date <> '0000-00-00 00:00:00'";
		
		?>
	
		<h3 class="tpj_h3title"><b><?php echo JText::_('LIST OF JOB'); ?></b></h3>
		
		
		<h3><strong><?php echo JText::_('CATEGORY'); ?> </strong> :
<?php echo $spec->category; ?> - 
	<strong><?php echo JText::_('SPECIALIZATION'); ?> </strong> :
<?php echo $spec->specialization; ?></h3>

		
		<form action="<?php echo $action; ?>" method="post" name="userFormJob" enctype="multipart/form-data">	
			<table width="100%" cellpadding="0" cellspacing="0">
			<thead>
				<tr class="tpj_rowhead">
					<th width="10">
						<?php echo JText::_('NO'); ?>
					</th>
					
					<th width="10%" align="left">
						<?php echo JText::_('DATE'); ?>
					</th>
					
					<th width="30%" align="left">
						<?php echo JText::_('JOB TITLE'); ?>
					</th>
					
					<th width="20%" align="left">
						<?php echo JText::_('LOCATION'); ?>
					</th>
					
					<th  align="left">
						<?php echo JText::_('COMPANY'); ?>
					</th>
					
									
					
				</tr>
			</thead>
			<tfoot>
				<tr>
					<td colspan="6" class="tpj_row3">
						<?php echo $pageNav->getListFooter(); ?>
					</td>
				</tr>
				<tr>
					<td colspan="6" class="tpj_row3">
						<a href="<?php echo JRoute::_('index.php?option=com_tpjobs&task=rss&type=specialization&id='.$id); ?>"><img src="<?php echo JURI::root(); ?>components/com_tpjobs/images/rss.png" alt="RSS"></a>
					</td>
				</tr>
			</tfoot>
			<tbody>
			<?php
			$k = 0;
			for ($i=0, $n=count($rows); $i < $n; $i++) {
				$row = $rows[$i];

				
				$link_detail	= JRoute::_('index.php?option=com_tpjobs&task=detailjob&id='. $row->id );
				$link_publish	= JRoute::_('index.php?option=com_tpjobs&task=publishjob&id='. $row->id );
				?>
				<tr class="tpj_<?php echo "row$k"; ?>">
					<td>
						<?php echo $pageNav->getRowOffset( $i ); ?>
					</td>
					<td>
					<?php echo JHTML::_('date', $row->publish_date, '%d-%m-%Y'); ?>
					</td>
					
					<td>
					<a href="<?php echo $link_detail; ?>"><?php echo $row->job_title; ?></a><?php
						$now = date('Y-m-d H:i:s', time() + ( $mainframe->getCfg('offset') * 60 * 60 ) );
						if($row->expire_date != "0000-00-00 00:00:00" && $row->expire_date < $now)
						{
							echo '(expired)';
						}
					?>	
                                        				
					</td>
					<td>
					<?php echo $row->state; ?>, <?php echo $row->country; ?>
					</td>
					<td>
					<?php echo $row->comp_name; ?>
					</td>				
				</tr>
				<?php
				$k = 1 - $k;
			}
			?>
			</tbody>
			</table>
and the mysql/function is

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.
			 " 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".
			 " 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.
			 " ORDER BY a.publish_date DESC";
	
	
	$db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
	
	$rows = $db->loadObjectList();	
	
	HTML_front_tpjobs::searchBySpec($rows,$spec,$pageNav,$option);
}
I have tried all kinds of ways to only show the live records ,the latest being "WHERE expire_date >= CURDATE()" Everything I try in the sql to hide the records results in no records being displayed at all. So in the meantime i have resorted to displaying "(expired)" on the front end if the expiry date has passed. I just can't get it to not display if 'expire_date' field has passed.

Could someone use a fresh pair of eyes on this for me and point me in the right direction.

Many thanks:D
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: hiding an expired record

Post by Christopher »

What kind of field is expire_date? Is it a DATETIME or TIMESTAMP? Are there values in that field?
(#10850)
Post Reply