Here is how to extract the latest additions from a MySQL table using PHP. Click here to see an example of what it could look like!

Assumptions:

- the date of the record is stored in datefield as yyyy-mm-dd hh:mm:ss (timestamp).

Code:

<?

// first we get the current date as a Unix timestamp

$curr=strtotime(date("Y-m-d"));

// then we subtract the new period, f.e. 2 weeks. In our case we take 86400 (seconds per day) times 14 days, but there are many ways to do this in PHP

$checkfordate=$curr-(14*86400);

// now we write the query that checks how many records will be returned to match the date criteria, using the UNIX_TIMESTAMP tag to convert the stored date

$getnew = "SELECT * FROM your_table WHERE UNIX_TIMESTAMP(datefield) > ".$checkfordate." ";

// run the query and count the number of rows returned that match the query

$rowcnt=mysql_num_rows(mysql_query($getnew));

// depending on the number of records you want to show we have to ensure there are sufficient result records else we need to remove the LIMIT tag from the query

if ($rowcnt > 5){

// generate a random number between 0 and the number of rows found (always -1)

$randvar = rand(0,$rowcnt-1);

// get the records (in this case 5) by using the LIMIT statement which uses the record number generated in $randvar, sort by newest descending

$latest1= mysql_query("SELECT * FROM your_table WHERE UNIX_TIMESTAMP(datefield) > ".$checkfordate." ORDER BY datefield desc LIMIT ".$randvar.", 5");
}  //endif

else {  // there are less records than the limit

$latest1= mysql_query("SELECT * FROM your_table WHERE UNIX_TIMESTAMP(datefield) > ".$checkfordate." ORDER BY datefield desc");
}  //endelse
?>

// now we have to insert the results into the page

<div align="left">
<table border="0" cellpadding="0" cellspacing="0" width="85%">
<tr>
<td><font>WHAT'S NEW</font></td>
</tr>
<tr>
<td valign="top">
<table border="1" cellpadding="4" cellspacing="0" width="100%" class="b" bordercolordark="#c0c0c0" bgcolor="#E6F2FF">
<tr>
<td>

<? // start the loop to get all the resulting records

while ($latest=mysql_fetch_array($latest1)) {
$getthem1=mysql_query("SELECT * FROM your_table WHERE uid=".$latest["id"]);  // adjust this query as is required for your situation
$getthem=mysql_fetch_array($getthem1);

// if you want to include a category you can include something like the following query

$getcat=mysql_fetch_array(mysql_query("SELECT id,categoryname FROM categories WHERE id=".$getthem["cid"]));  // adjust this query as is required for your situation
?>

// showing the results, adjust as is required for your situation

<a href="http://yourwebsite.com/goto.php?cid=<? echo $getthem["id"];?>"><? echo $latest["title"];?></a>&nbsp;<span>
(<a href="http://yourwebsite.com/goto.php?cid=<? echo $getcat["id"];?>"><? echo $getcat["categoryname"];?></a>)<br />

// here we clip the description to max. 300 characters

<? echo substr(str_replace("\n","<br />",$latest["description"]),0,300);
if (strlen($latest["description"])>300)
{
echo "...";  // to indicate the description has more
}
?>
<br />
<?
} //end whilelatest
?>
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>