0

I'm creating a daily drawing system. The users have a chance to win every day. Once a user wins, they will not be able to win again for 30 days. I need to select all rows inside my mysql database that have dates over 30 days ago, or are still set as default (0000-00-00). How can I do something like this?

I've tried a few different ways but none seem to work. Once these rows are selected, it randomly chooses 3 winners, and sets their new dates to the current date.

Current code:

<?php

include_once('../inc/connect.php');

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

$drawquery = mysql_query("SELECT id FROM users ORDER BY rand() LIMIT 1");
$row = mysql_fetch_assoc($drawquery);

$wondaily = $row['wondaily'];

$userid = $row['id'];
echo $userid;

?>
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by aquilax
0

The SQL should be

SELECT id 
FROM users
WHERE date = '0000-00-00'
OR date <= CURRENT_DATE() - INTERVAL 30 days  
ORDER BY rand() 
LIMIT 3;

If you also want to update the dates with the same query use:

UPDATE users 
SET date = CURRENT_DATE()
WHERE id IN (
SELECT id 
FROM users
WHERE date = '0000-00-00'
OR date <= CURRENT_DATE() - INTERVAL 30 days  
ORDER BY rand() 
LIMIT 3);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.