How to chose random (userID)

Apologize for my poor English.

I have not been programmed at about 4 years, so I need some help. There is a need to do photo analysis system for educational purposes. To encourage more voting, script chosen random winner, who are voting for at least 15 times a week.

**

I need a script that, chosen random (userID), who are voting for at least 15 times a week.

If there is more than one, script randomly selects a winner (preference to those who have never won).

In this example, according to the winner should show every Monday.

The week starts on Monday at 00:00:00 and ends on Sunday at 23:59:59.

Server: Linux, Apache 2, PHP 5, MySQL 5

table ‘LotMachine’

$StartTime = ‘2010-11-01’; // Voting begins

$EndTime = ‘2010-12-05’; // Voting ends

$MinRecord = ‘15’; // minimal records in the last seven days (week) of the table “example”

$MaxWin = ‘1’; // max winner of the week.

table ‘Votings’

VoteID (INT)
GalleryID (INT)
PictureID (INT)
UserID (INT)
Voting (TINYINT)
VoteTime (DATETIME)

table ‘winners’

winID
galleryID
userID
winnerDay (DATETIME)

i gave you this query yesterday –

SELECT userID
  FROM example
 WHERE cID = 2
   AND rTime >= CURRENT_DATE - INTERVAL 7 DAY
GROUP
    BY userID
HAVING COUNT(*) >= 15

why have the column names changed since then?

yes, your query is correct.

If the result is one of the userID, then can put the values (galleryID, userID, NOW()) in the table “Winners” but if there is more…

if there (ExampleTable) is two userID, check if any of them are in the table “Winners” field ‘GalleryID=2’

if there is one userID, then the second userID is the winner.

if where there are both userID, then randomly choose a winner.

if the table"ExampleTable" is more than two userID …

Where the ideal is one of the winners, who have never won a ‘GalleryID = 2’.

before was wrong table name in comment

$MinRecord = ‘15’; // minimal records in the last seven days (week) of the table “Votings”

sorry, this is very confusing

please start by doing a SHOW CREATE TABLE for your tables

Rudy’s query gives you as many users that qualify (meeting the minimum number of votes) in that time period. So now you want ONLY one of those answers. You need the following (untested):


SELECT * FROM
  (SELECT 
     userID
   FROM 
     example
  WHERE 
     cID = 2
   AND 
     rTime >= CURRENT_DATE - INTERVAL 7 DAY
   GROUP BY 
      userID
   HAVING 
     COUNT(*) >= 15) as dt
ORDER BY RAND()
LIMIT 1

you can modify that example to match your tables

CREATE TABLE test.LotMachine (
lotID INT NOT NULL ,
GalleryID INT NOT NULL ,
StartTime DATETIME NOT NULL ,
EndTime DATETIME NOT NULL ,
MinRecord TINYINT( 2 ) NOT NULL ,
MaxWin TINYINT( 2 ) NOT NULL ,
PRIMARY KEY ( GalleryID ) ,
UNIQUE (
lotID
)
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE test.Votings (
VoteID MEDIUMINT UNSIGNED ZEROFILL NOT NULL ,
GalleryID INT UNSIGNED ZEROFILL NOT NULL ,
PictureID INT UNSIGNED ZEROFILL NOT NULL ,
UserID INT UNSIGNED ZEROFILL NOT NULL ,
Voting VARCHAR( 2 ) NOT NULL ,
VoteTime TIMESTAMP NOT NULL ,
PRIMARY KEY ( UserID )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE test.winners (
winID SMALLINT NOT NULL ,
GalleryID INT NOT NULL ,
userID INT NOT NULL ,
winnerDay DATE NOT NULL ,
PRIMARY KEY ( userID )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;