[SOLVED] Please help with quite a complicated query!

[gavrichards]

Hi there! First post, be nice please

Hope you help, i've really got stuck with this one...

 

I have a database called 'entries', here's the layout...

  `id` INTEGER AUTO_INCREMENT

  `con_id` VARCHAR(

  `mem_id` VARCHAR(

  `status` INTEGER

  `winner` TINYINT(1)

  `dt_win` DATETIME

 

Here's the setup...

It's a database which stores entries into contests.

Each contest has a 'con_id'.

Each entry into the contest has an 'id'

Each member who can enter a contest has a 'mem_id'.

If they answer the contest question correctly, status is 1, else it's 0

If they are selected as a winner, 'winner' is 1 else it's 0

The date they are confirmed as a winner is stored in 'dt_win', else it's '0000-00-00 00:00:00'

 

I'm trying to create a macro script to help the administrator, which will automatically select 'x' number of random entries as winners.

However, it will also need to perform a check, that the winners have not won a previous contest in the last 'y' days.

 

So lets say they're running the macro for contest 'z'. The query will begin...

 

UPDATE `entries` SET winner = `1` WHERE con_id = `z` .......

 

Unfortunately that's where I've come stuck.

I don't even know if this is even possible to do in one query to be honest.

 

MySQL Server Version: 5.0.22

 

I really hope someone has the answer! I hope i've provided enough information. Many thanks.

[teng84]

maybe

 

select * from table where winner =0 order by rand() limit 1 // will return one record randomly

[gavrichards]

Hi teng84

I don't think you've understood my question. Your answer doesn't take into account the check that member hasn't won in the last ? number of days.

Thanks

[teng84]

select * from table where winner =0  and datediff(now(),givendate) <= 3 order by rand() limit 1

 

will return the records who dont won on the last 3 days 

sorry mate

i sometimes find it hard to understand native english speakers

[gavrichards]

No problem, I appreciate your help, thanks!

 

I don't think that will work though.

[gavrichards]

Hi guys

 

I've managed to solve the issue, and I thought i'd post the solution for anyone else who is interested.

 

It is split into 2 separate queries. I would've used a sub query, only you're not permitted to perform a sub query on the same table as the outer query.

 

// SET ALL $selMacNum NUMBER OF ENTRANTS TO WINNERS
$strQuery = "UPDATE `entries` SET winner = \"1\" WHERE con_id = \"".$conId."\"";
if ($chkMacDays) {
  // DO NOT SET WINNERS TO ENTRANTS WHO HAVE WON ANOTHER CONTEST IN THE LAST $selMacDays DAYS
  $arrResSub = mysql_query("SELECT mem_id FROM `entries` WHERE winner = \"1\" AND dt_win > (now()-INTERVAL ".$selMacDays." DAY) AND dt_win <> \"0000-00-00 00:00:00\"") or die("Query failed: " . mysql_error());
  while (list($aMemId) = mysql_fetch_row($arrResSub)) {
    $arrSubId[] = "'".$aMemId."'";
  }
  $strSubIds = implode(",", $arrSubId);
  if ($strSubIds <> "") {
    $strQuery .= " AND mem_id NOT IN (".$strSubIds.")";
  }
}
$strQuery .= " ORDER BY RAND() LIMIT ".$selMacNum;
$arrRes = mysql_query($strQuery);