Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    19

    Unanswered: hiding record after expiry date

    Hello, this one is driving me a little crazy

    I have inherited a site with the following code which is 'supposed' hide records from the from end, but it simply doesn't

    PHP Code:
    //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 
    $k;
                }
                
    ?>
                </tbody>
                </table>
    #

    and the mysql/function is

    PHP Code:
    //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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE expire_date > CURRENT_DATE

    if you want to confirm that this is in fact the right sql, test your query outside of php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    19
    Thanks for the reply, i can confirm that the sql has been tested outside of php with no problems

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excellent

    let's ask that this thread be moved to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    1
    r937: how about we not just suggest moving this message, and actually see if we can give some help.

    tsp003: your sql $where statement should be changed to something like this

    PHP Code:
    $where .= "AND a.is_active='y' AND a.publish_date <= '".$now."' AND expire_date >= '".$now."' AND expire_date <> '0000-00-00 00:00:00'"
    It is necessary to add the publish date to the sql statement, in order to compare dates in order to get only the active items to display

    My guess is that you tried adding an expiry WHERE clause when making your select statement and without changing the $where statement above meant that it conflicted and produced nothing in the results.

    The 'WHERE expiry date' would of course work on its own when tested outside of PHP, any sql statement tested in isolation would.

    You should now find that the only records displaying are the ones you require.

    (As an aside I'd like you to know that I think this is actually a relevant question posted in the relevant place, some people are far too quick, to dismiss without looking deeply enough at what's being asked! I'd rather these people didn't bother replying - Just my two cents worth!)

    I hope my explaination makes sense and I hope this now works for you?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have tested the SQL outside PHP and it worked, then have you checked it inside PHP, and by that actaully examined the SQL being sent to the server to make certain its requesting what you think it is.

    ie prove that the SQL, especially the where clause is valid
    also I don't see any error handling or trapping on the query, so at present Im not entirely certain that you have valid SQL. I always prefer to see code that checks the error codes, and or uses to 'or die' construct to warn of possible errors
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •