Updating a table based on the date in another table

[perky416]

Hi guys,

 

On my auction site I have a table in my database that contains any offers made. If an offer is sent to auction an entry is made in the auctions table containing the same data as well as a status and auction end date. Im using the code below to update the auction status with "ended" when the end date has passed. Im struggling with the second query that updates the offers table.

Im trying to get it to update the status of each offer, where the auction from the auctions table has passed its end date.

 

I cant figure out how to do it. Does anybody have any ideas?

 

Thanks

 

$auction_query = mysql_query("SELECT * FROM auctions");
while ($auction_row = mysql_fetch_assoc($auction_query))
{
$date = date("Y-m-d H:i:s");
$end_date = $auction_row['end_date'];
if ($date > $end_date)
{
	$item = $auction_row['item'];
	$seller = $auction_row['seller'];
	$buyer = $auction_row['high_bidder'];
	echo $domain;
	mysql_query("UPDATE auctions SET status='ended' WHERE NOW() > end_date");
	mysql_query("UPDATE offers SET buyer_status='auction ended, you won' WHERE seller='$seller' AND buyer='$buyer'");
}

[TomFromKWD]

do you have any form of auction ID in the database tables?

 

You could use a NOT IN

 

ie

 

mysql_query("UPDATE offers SET buyer_status='Sorry, you did not win this auction' WHERE auctionID='$auctionID' AND buyer NOT IN ('$buyer')");

 

That would update everyone related with that auctionID who is not the "$buyer" with 'Sorrry, you did not win this auction' instead of 'auction ended, you won'.... in theory.

That exact code its self wont work unless you have some form of ID you can call on to identify, and it may not be 100% perfect as its a while since I've used "NOT IN" and thats off the top of my head lol

Im sure someone can touch it up, or confirm that I have added the NOT IN in the correct fasion

 

Hope it helps at least, or points you in the right direction.

 

Tom