Deyaan
07-28-2009, 01:54 PM
Could somebody help me with this table update script:
$get_all_bets = 'SELECT BettingOfferID, result_left, result_right FROM Betting_Offer';
$resource = mysql_query($get_all_bets) or trigger_error('MySQL is complaining: '.mysql_error(), E_WARNING);
while ($row = mysql_fetch_assoc($resource))
{
$discriminant = $row['result_left'] - $row['result_right'];
$update_these_bets = "UPDATE Placed_Bets
SET profit =
CASE
WHEN $discriminant > 0
AND (Selected_Tip = '1' OR Selected_Tip = '1X')
THEN Odds_of_Selected_Tip * Stake - Stake
WHEN $discriminant = 0
AND (Selected_Tip = 'X' OR Selected_Tip = '1X' OR Selected_Tip = 'X2')
THEN Odds_of_Selected_Tip * Stake - Stake
WHEN $discriminant < 0
AND (Selected_Tip = '2' OR Selected_Tip = 'X2')
THEN Odds_of_Selected_Tip * Stake - Stake
ELSE -1 * Stake
END
WHERE BettingOfferID = '{$row['BettingOfferID']}'";
$result = mysql_query($update_these_bets);
echo ($result == FALSE) ? 'Bet number '.$row['BettingOfferID'].' not updated properly.' : 'Bet number '.$row['BettingOfferID'].' updated successfully.';
}
It's a batch update and I wonder if there is a way to isolate just a few "BettingOfferID" to update Placed_Bets table with? Because database gets larger and batch is not the right solution. Also single "BettingOfferID" by single "BettingOfferID" update is also not acceptable.
If somebody has got any idea, please help!
$get_all_bets = 'SELECT BettingOfferID, result_left, result_right FROM Betting_Offer';
$resource = mysql_query($get_all_bets) or trigger_error('MySQL is complaining: '.mysql_error(), E_WARNING);
while ($row = mysql_fetch_assoc($resource))
{
$discriminant = $row['result_left'] - $row['result_right'];
$update_these_bets = "UPDATE Placed_Bets
SET profit =
CASE
WHEN $discriminant > 0
AND (Selected_Tip = '1' OR Selected_Tip = '1X')
THEN Odds_of_Selected_Tip * Stake - Stake
WHEN $discriminant = 0
AND (Selected_Tip = 'X' OR Selected_Tip = '1X' OR Selected_Tip = 'X2')
THEN Odds_of_Selected_Tip * Stake - Stake
WHEN $discriminant < 0
AND (Selected_Tip = '2' OR Selected_Tip = 'X2')
THEN Odds_of_Selected_Tip * Stake - Stake
ELSE -1 * Stake
END
WHERE BettingOfferID = '{$row['BettingOfferID']}'";
$result = mysql_query($update_these_bets);
echo ($result == FALSE) ? 'Bet number '.$row['BettingOfferID'].' not updated properly.' : 'Bet number '.$row['BettingOfferID'].' updated successfully.';
}
It's a batch update and I wonder if there is a way to isolate just a few "BettingOfferID" to update Placed_Bets table with? Because database gets larger and batch is not the right solution. Also single "BettingOfferID" by single "BettingOfferID" update is also not acceptable.
If somebody has got any idea, please help!