Need to know how to set ad listings to expire 30 days in php mysql?

PHP
1

I need to know how to set ad listings to expire 30 days in php mysql?
i know i have to ad some kind of feids in the myql table of listings

here is my php code to the page where it displays the for sale listings and catagories

  <div class="orageBGfg" style="padding:2px 0px 2px 20px;">
<b>Category</b> : <? echo "$category";?> 
<?
$query = "SELECT COUNT(*) FROM forsale_content WHERE category='$category' AND status='online'"; 
$numentries = mysql_query($query) or die("Select Failed!");
$numentry = mysql_fetch_array($numentries); 
?>
</div>

  <b class="orageBG">
  <b class="orageBG5"></b>
  <b class="orageBG4"></b>
  <b class="orageBG3"></b>
  <b class="orageBG2"><b></b></b>
  <b class="orageBG1"><b></b></b></b>
</div>

<div id="title1">
Click on title to view description
</div>


<? 
$sql = "SELECT * FROM forsale_content WHERE category='$category' AND status='online' ORDER BY id DESC"; 
$result = mysql_query($sql); 
while ($record = mysql_fetch_object($result)) { 

$question = nl2br($record->description);
$answer = nl2br($record->answer);
?>

<div id="list">
<a href="?q=detail&amp;id=<? echo"$record->id";?>"><? echo "$record->title"; ?><?php if($record->price != "") { ?>
               - $<? echo number_format($record->price);?>
<?php } ?></a> 
 <span>(<? echo "$record->city"; ?>) (<? echo "$record->postdate";?>)</span>

</div>

<?
}
?>

any help would be great thanks

2

Welcome to the forums, @sales132. It’s easier for people to read your code and make suggestions if you format your code properly. I’ve done it for you this time, but next time just type three backticks on the line before the code and three backticks on the line after the code. Or if you prefer, you can highlight the code after you paste it in and then select the </> icon that is just above the edit area.

1 Like
3

Assuming you have a field in your table that records the date/time that the ad is submitted (easily done with DATETIME format and default value of CURRENT_TIMESTAMP) you can add a WHERE clause to your SELECT query which utilises DATE_SUB() to compare the date against the current time. Eg:-

WHERE date_added > DATE_SUB(NOW(), INTERVAL 30 DAY

On a side note, you must stop using the mysql functions, they are obsolete, no longer a part of PHP and will fail to work when your server is upgraded to a currently supported version.
Use mysqli or PDO and take advantage of prepared statements rather than putting variables like $category directly into your queries, as this is a security vulnerability.

3 Likes
4

I would add an expired boolean flag and expired_at date time to the table deferring the expiration responsibility to a background job that runs once a day. The job would find all the ads that need to expire and set their expired field value to 1/true and expired_at field value to the current start time. Moving that logic to a background process results in a more flexible and efficient architecture.

1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.