expiring products (PHP & MySQL)

[vendettabass]

I'd say I'm a newbie to PHP and MySQL, and am looking for a solution to this problem!

I have to create an auction site for my coursework, and I'm trying to make auctions that expire.. well.. expire. I have 'Expires' and 'Starts' stored in my SQL table, obviously.

I want to make sure on the home page, only auctions that have not expired are displayed - as you can see, this, at the moment is not the case.

To add the 'Expires' value to a product, I used this MySQL code:

// add start date
$insertstart = "UPDATE smith_auctions SET Starts = NOW() WHERE AuctionTitle = '".$test."'";
$result2 = mysql_query($insertstart) or die(mysql_error());

// add expiry date
$insertexpiry = "UPDATE smith_auctions SET Expires = NOW() +INTERVAL 7 DAY WHERE AuctionTitle = '".$test."'";
$result3 = mysql_query($insertexpiry) or die(mysql_error());

and here is my homepage script:

while($row = mysql_fetch_array($result2,MYSQL_ASSOC))
{
$op1 = (string) $row['Expires'];
$op2 = (string) date("Y M J H:i:s");

echo "<tr><td><a href='product.php?id=$row[AuctionID]'>$row[AuctionTitle]</a></td><td>£".$row[CurrentPrice]."</td><td>$row[NumberBids] bids</td><td>Ending: ".$row[Expires]."</td>";

echo "<td><a href='product.php?id=$row[AuctionID]'>Bid</a> or </td>";
echo "<td>";
$bin = $row['BIN_Price'];
if ($row['BIN_Price'] > 0 )
{ echo "<a href='product.php?id=$row[AuctionID]'>Buy Now!</a></td></tr>"; }
}

any help to how to tackle this would be appreciated, I'm having a real hard time!

 

[chrish]

What's the select statement you are using for the actions on the page?

I think you need to add something like ...

select *
from smith_auctions
where Expires > now()

 

[SrWebDeveloper]

The method I like is to add two MySQL datetime fields such as notLiveBefore and notLiveAfter both of which determine a time window the content is active, otherwise its expired. Plus this allows users to delay the start of auctions if they wish as well as determine when they stop.

Exmple: The query would include as part of the select statement using the if command, returned as an alias named 'Status'...

if(unix_timestamp(notLiveBefore) < unix_timestamp(now()) AND unix_timestamp(notLiveAfter) > unix_timestamp(now()),'Active','Expired') as Status

Or you could use a variation of it in your where clause if you wish to exclude active or expired content in your result set. Remember the if command in MySQL determines if(condition,true,false) in case you're not familiar when used in the select part.

Get the idea?

-jim

 

[vendettabass]

lovely thanks!

 

[SrWebDeveloper]

Cheers

-jim