Codeigniter Days Left mysql Query

Question

I have a simple script that displays products. In my database I store both date_posted and end_date as fields in he products table. On my homepage I display the 20 most recent posted products.

I want to display products that have 1 or more days left. So far I've manage to do this but using the front end script and css and this isn't best practice because it won't display 20 products if some among them have ended.

Below is the script that i have:

my View =>

 <?php $i=0;?>
        <?php foreach($product as $p): ?> 
        <?php 
    $startDate = date('m/d/Y');
    $endDate = $p['endDate'];
    $seconds_left = (strtotime($endDate) - strtotime($startDate));
    $days_left = floor($seconds_left / 3600 / 24);


     ?>
       <?php  $i+= 1; ?>

            <td <?php  //if($days_left<=0){echo "class='ended'";} ?>>
            <a href="<?php echo base_url()."user/product/".$p['productID']; ?>" class="product_name"><?php echo $p['productName']; ?>
            </a>
             <ul class="nav">
        <li><i class="icon-home"> </i> Location : <?php echo substr($p['location'],0,25); ?></li>
        <li><i class="icon-tags"> </i> Budget : Tshs <?php  echo number_format($p['Price']); ?></li>
        <li><i class="icon-tasks"> </i> <?php if($p['CategoryID']==4){echo"Number of rooms: ";}else echo "Quantity :"?> <?php echo $p['quantity']; ?></li> 
        <li><i class="icon-eye-open"> </i> Bids : <?php echo $p['Bids']; ?></li>
        <li><i class="icon-time"> </i> Days Left: <?php echo $days_left; ?></li>
      </ul>

            </td>

my Model=>

 function get_product($productID){
                               $this->db->select()->from('products')->where('productID',$productID);


                $query = $this->db->get();
                return $query->first_row('array');
            }

bottleboot · Accepted Answer · 2013-06-27 10:08:11Z

1

You'll need to get a date in your where clause. Something like this should do the trick:

function get_product($productID){
          $this->db->select()
             ->from('products')
             ->where('productID',$productID)
             ->where('end_date >=', date('Y-m-d H:i:s', strtotime('- 1 day')))
             ->limit(20);

          $query = $this->db->get();
          return $query->first_row('array');
}

edited Jun 27, 2013 at 10:08

answered Jun 27, 2013 at 9:33

bottleboot

1,6992 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

robertrutenge Over a year ago

thnk you so much @bottleboot,t workd perfectly rily appreciate coz i was stuck on ths for hours,but i only have to change "end_date<=" to "end_date>=" so as t display days greater than 0 in my case so i changed -1 to 0.thnks again buddy :-)

bottleboot Over a year ago

Oops, my bad, not fully awake yet! I modified my answer. No problem, glad I could help. You can also accept the answer if you want!

Collectives™ on Stack Overflow

Codeigniter Days Left mysql Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related