Updating a mysql database based on case statements

I really need help on this one. I'm trying to do a mysql update based uisng case but i don't think i'm getting it right. Here is what i'm trying to achieve. I have a table with the following fields

user_id, rank, weekly, monthly, justwinners
1         9       0       0       0
2         29      0       0       0
3          8      0       0       0
4         10      0       0       0
5         12      0       0       0

What i want to achieve is to update the weekly, monthly and justwinners fields based on specific dates Lets say i have a start date of 5/07/2011. $startdate = 5-07-2011; At the end of one week which is 12/07/2011 i want to update the weekly field of the user with highest rank to 1. Also at the same time update justwinners field of the next 3 users with the highest rank to 1. At the end of the second week which is now 19/07/2011, i want to update the weekly field of the user with highest rank whose value is still '0' to 2 and also update the justwinners field whose value is still '0' to 2. This will continue until i get to one months time when i will update the monthly field. This is what i have been able to come up with so far which is not working.

//Initiate the database connection 开发者_运维技巧here
function get_db_conn() {
  $conn = mysql_connect(HOST, DB_USER, DB_PASSWORD) or die('Could not Connect!');
  mysql_select_db(DATABASE, $conn) or die ('could not connect to database');
  return $conn;
}

function updateWinners( $limit, $field ) {
$conn = get_db_conn();
    switch($field) {
    case "weekly" :
    $limit = 1;

    case "monthly" :
    $limit = 1 ;

    case "giftpack" : 
    $limit = 10 ;

    default:
    $limit = 1 ;
    }

    $sqlquery = "SELECT * FROM application rank DESC " ;
    $sqlquery .= " where $field < 1 ";
    $sqlquery .= " LIMIT $limit ";

    $result = mysql_query($sqlquery);
    $user_data = mysql_fetch_row($result);
    if(isset($user_data)) { 
        $user_data = 0 ;    
        while($user_data){
        $uid = $user_data(user_id);
        $rank = $user_data(rank);

    $query = "INSERT INTO application_winners (user_id, rank, date) VALUES ('$uid', '$rank' 'now()')";
    mysql_query($query) or die('Error, insert winners query failed');

           switch ($action) {
        case "weekly":
        $startdate = "5-07-2011";
        $sqlquery = "UPDATE application SET weekly = CASE 
            WHEN (CURDATE() = (startdate * 7)) 
            THEN weekly = '1' 
            WHEN (CURDATE() = (startdate * 14)) 
            THEN weekly = '2' 
            WHEN (CURDATE() = (startdate * 30)) 
            THEN weekly = '3' 
            ELSE weekly 
            END";



              }

        $user_data = $user_data + 1;
        } //endwhile
    }
    else {
    echo "Error in updating the winners";
    }

}

In case you know of any better way to implement this. kindly share.

---

Just as a remark - you have an insert followed by an update. You could do this in the insert part. Something like:

$query = "INSERT INTO application_winners (user_id, full_name, rank, date, $field) VALUES ('$uid', '$user_fullname', '$rank' 'now()', '$updatevalue')";
mysql_query($query) or die('Error, insert winners query failed');

---

i did not look at everything closely but you are using the variable "$action" for the second switch but it does not exist anywhere else in your code..

---

Try changing:

    $sqlquery = "UPDATE application SET weekly = CASE 
        WHEN (CURDATE() = (startdate * 7)) 
        THEN weekly = '1' 
        WHEN (CURDATE() = (startdate * 14)) 
        THEN weekly = '2' 
        WHEN (CURDATE() = (startdate * 30)) 
        THEN weekly = '3' 
        ELSE weekly 
        END";

to:

    $sqlquery = "UPDATE application SET weekly = CASE 
        WHEN (CURDATE() = (startdate * 7)) 
        THEN '1' 
        WHEN (CURDATE() = (startdate * 14)) 
        THEN '2' 
        WHEN (CURDATE() = (startdate * 30)) 
        THEN '3' 
        ELSE weekly 
        END";