Displaying information based on date

[delux247]

Hello all,

 

I Have information that is coming from a database, I would only like to display the records whose date is only 6 months old from the current date. I have tried a few things but had no luck.  ??? I am really stuck on this. if you need more information please let me know.

 

Thank you in advance.

[dMilesFox]

The table from your database has a special field with its timestamp or date added/created.?

[delux247]

Yes, I have a field called "news_date" and it has the date in this format "2006-08-14"

[premiso]

Show some code and we will be more apt to help you.

[delux247]

This is the code that i tired to configure the date with. I don't know if i did this correctly.

<?php
$sixmonth = mktime(0,0,0,date("m"),date("d")-180,date("Y")); 
$month = date("m/d/Y", $sixmonth);
$date = $row_news['news_date']; //date in database

 

this code is what will display

if($totalRows_news>0 && $month >= $date){ ?>
        <?php do { ?>
          <div class="newsitem">
            <h3><?php echo date('F j, Y', strtotime($row_news['news_date'])); ?></h3>
            <a href="index.php?news_id=<?php echo $row_news['news_id']; ?>"><?php echo $row_news['news_title']; ?></a>
          </div>
          <?php } while ($row_news = mysql_fetch_assoc($news)); ?>
        <?php } ?>

 

again im not sure if this is correct, but it is what i came up with...

[premiso]

<?php
$month = date("m/d/Y", time()-(60*60*24*180));
$date = $row_news['news_date']; //date in database
?>

 

Should produce 6 months ago from today. Note the 60 times 60 gets us minutes the 60*60*24 gets us 1 day. The 60*60*24*180 will give us 180 days ago.

 

 

[ober]

You should really let the database handle all of this:

 

$sql = "SELECT news_data, news_date, etc FROM news WHERE news_date >= DATE_ADD(NOW() - 6 MONTHS)

 

Handling this through PHP is just inefficient.  This should work (I didn't test it) but there are other options as well.

[delux247]

@ober, I want to do it with the database and had tried what you suggested before. that doesn't work, I double check and put your code in and it still doesn't work. I get this mysql error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'news_date >= DATE_ADD(NOW() - 6 MONTHS) ORDER BY news_date DESC'

 

this is my sql stament

 

SELECT news_id, news_title, news_date, publications.publications_title, publications.publications_link FROM news LEFT JOIN publications ON news.news_pdf=publications.publications_id WHERE `news_date` >= DATE_ADD(NOW() - 6 MONTHS) ORDER BY news_date DESC";