hiding an expired record
Posted: Wed Aug 17, 2011 3:21 am
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
and the mysql/function is
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
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>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);
}Could someone use a fresh pair of eyes on this for me and point me in the right direction.
Many thanks:D