[SOLVED] Bet profit calculation

[Deyaan]

Hello people!

 

I'm new to coding and really need some help.

My current project is fantasy sports betting site and I'm stuck here:

 

I've got those 6 mysql columns query from two tables: BET_ID, TIP, TIP_ODDS, STAKE, RESULT and PROFIT.

TIP contains 1, X, 2, 1X or X2 sign

TIP_ODDS contains a number with 2 decimals, in example: 2,23

STAKE contains a number from 1 to 10,

RESULT is classic soccer result 1:0, 2,0..., 0:0, 1:1..., 0:1, 0:2 and so on.

PROFIT needs to be calculated like:

PROFIT = TIP ODDS * STAKE - STAKE if TIP equals to RESULT

PROFIT = - STAKE if TIP does not equal RESULT

 

I don't know how to define result... lets say result is A:B

A>B --> TIP 1 and TIP 1X  are correct

A=B --> TIP X, TIP 1X and TIP X2  are correct

A<B --> TIP 2 and TIP X2  are correct

 

If somebody can help me with PROFIT calculation script, please do so, because I don't think I can do this by myself 

[akitchin]

are you trying to do this with MySQL queries, or is this a calculation you need to perform prior to inserting the bet into the database?

[Deyaan]

MySQL queries. Bet is placed and inserted into database table and waits for the result. When the match ends I enter the result in an other table.

I actually don't know how that should work... with profit calculation i mean. But I think I should query placed bet columns from one table and result from other table (match_ID is the same in both) and then somehow perform php calculation to get "profit".

[akitchin]

depending on your proficiency with PHP, the solution is similar in SQL. a section that might do wonders to help you through this is the functions and operators portion of the MySQL manual.

 

in order to help you better, can you offer more details with regards to the table columns? i was under the impression results and bet parameters were in the same table, but you've just mentioned result will be in a separate table.

[Deyaan]

I have got 2 MySQL tables: 1. Betting_Offer, 2. Placed_Betts

Betting_Offer contains these columns: BettingOfferID(auto_increment), SportType, League, Date_and_Time_Match_Begins, Who_Plays, Tip1, Tip2, Tip3, Tip1X, TipX2, Result

Placed_Bets contains these columns: BetID(auto_increment), userID, BettingOfferID, Selected_Tip, Odds_of_Selected_Tip, Stake

 

Here is how it works: I fill in MySQL Betting_Offer table. I made a script that queries this information and echoes it in a HTML table as forms. User then chooses his Tip with radio buttons, enters his Stake and Submits form to Placed_Betts MySQL table.

 

When the match ends and result is known I enter it in Result column of Betting_Offer MySQL table.

 

Now I want users to see their and others results, PROFIT, I want to make HTML tables with success ranking and those sort of things.

 

Since I'm new to programing I don't even know if planned this as it should be. Also should profit be somehow be calculated into MySQL field or can it be calculated when generating HTML page?

[akitchin]

my first suggestion would be instead of storing the result in the format you mentioned (ie. 2:0), store it in two separate fields as "result_left" and "result_right" as integers (name them whatever you want). this will make calculations much easier, because otherwise you need to split the string up before performing the calculation.

 

it's actually not a bad idea to calculate and store the profit in a column in the placed_bets table, since this will allow you to run more straightforward queries on the table to generate those ranking tables and avoid selecting unnecessary data. the best time to calculate is probably when you input the result. you can either store "profit" in the placed_bets table, or you can make a separate table with just the userID, the betID, and the profit. it's probably easiest to put it in the placed_bets table.

 

the calculation should be relatively straightforward. you can simply run an UPDATE query that will update all placed_bets with a matching BettingOfferID to the result you've just input:

 

UPDATE Placed_Bets
  SET profit = 
    CASE WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') > 0
      AND (Selected_Tip = '1' OR Selected_Tip = '1X')
        THEN Odds_of_Selected_Tip * Stake - Stake
    CASE WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') = 0
      AND (Selected_Tip = 'X' OR Selected_Tip = '1X' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
    CASE WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') < 0
      AND (Selected_Tip = '2' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
    ELSE -1 * Stake
    END
  WHERE BettingOfferID = '$variable-here'

 

this is a generic UPDATE query that just implements your calculation. if you want a less cumbersome-looking query, you can always do result_left - result_right in PHP after INSERTing it, and run the appropriate individual query:

 

if ($_POST['result_left'] - $_POST['result_right'] > 0)
{
  $query = "UPDATE Placed_Bets SET profit = IF(Selected_Tip = '1' OR Selected_Tip = '1X', Odds_of_Selected_Tip * Stake - Stake, -1 * Stake) WHERE BettingOfferID = '$variable-here'";
}
elseif ($_POST['result_left'] - $_POST['result_right'] == 0)
{
}

 

(you get the idea)

 

hopefully this helps you get a picture of what's involved here. you can then use functions like SUM() and AVG() on the profit column GROUPed by userID to play around with stats when SELECTing the data for ranking tables.

[Deyaan]

Oh man, thanx a lot!!! You are a huge help! I'd newer come up with this. Now it looks so simple... 

I'll try to implement this code next week because my weekend already started and I have other things to do.

I'll let you know how it went. Thanx again!

[akitchin]

be sure to check my code again when you come back to this. iève edited it to include END after the CASE block since itès required, and i forgot it when i first wrote the example.

[Deyaan]

Hello I'm back to this as one of the final parts of my project.

 

I tried to UPDATE Placed_Bets through phpMyAdmin, but it returns some error:

Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN (SELECT result_left - result_right FROM Betting_Offer WH

 

Where could be the problem?

[akitchin]

to be honest, i haven't used CASE statements in an UPDATE query before. it could be that you need to enclose the entire statement in parentheses?

[Deyaan]

Unfortunately, that didn't work.

I'll try to figure out something 

Thanx for all the help!

[Deyaan]

I figured it out!

CASE goes only once!

 

UPDATE Placed_Bets
  SET profit =
    CASE
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') > 0
      AND (Selected_Tip = '1' OR Selected_Tip = '1X')
        THEN Odds_of_Selected_Tip * Stake - Stake
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') = 0
      AND (Selected_Tip = 'X' OR Selected_Tip = '1X' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') < 0
      AND (Selected_Tip = '2' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
    ELSE -1 * Stake
    END
  WHERE BettingOfferID = '$variable-here'

 

Thanx for everything again!

 

Cheers! 

[Deyaan]

Now I noticed that result_left and result_right miscalculate profit if one of the result is 0

I guess it sees it as an empty field. How can I fix that?

And one more thing: How to UPDATE with multiple variables for BettingOfferID at once?

[Deyaan]

Ignore upper post about profit, forgot to put integers.

[akitchin]

Now I noticed that result_left and result_right miscalculate profit if one of the result is 0

I guess it sees it as an empty field. How can I fix that?

And one more thing: How to UPDATE with multiple variables for BettingOfferID at once?

 

you can add as many variables as you want to that UPDATE query by adding the other fields separated by commas. is there something in particular you're trying to add to the query?

[Deyaan]

Sorry, my terminology is weak, I ment in WHERE BettingOfferID = '$variable-here' if I could enter multiple '$variable-here' at once. I tried with OR but it returned error as multiple rows were found or something like that.

[akitchin]

you can use "AND" and "OR" to separate them, but you must include the field in every clause:

 

WHERE this_variable = '$this_variable' OR that_variable = '$that_variable'

[Deyaan]

I'm looking for a way to update the table with the query beneath with multiple values at once:

BettingOfferID = 'bla1', 'bla2', 'bla3'.

I tried with 'OR' , 'AND' and I tried like that:

WHERE this_variable = '$this_variable' OR that_variable = '$that_variable'

also, but the PROFIT calculations are wrong. It works only with one value at a time.

 

UPDATE Placed_Bets
  SET profit =
CASE
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') > 0
      AND (Selected_Tip = '1' OR Selected_Tip = '1X')
        THEN Odds_of_Selected_Tip * Stake - Stake
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') = 0
      AND (Selected_Tip = 'X' OR Selected_Tip = '1X' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
WHEN (SELECT result_left - result_right FROM Betting_Offer WHERE BettingOfferID = '$variable-here') < 0
      AND (Selected_Tip = '2' OR Selected_Tip = 'X2')
        THEN Odds_of_Selected_Tip * Stake - Stake
    ELSE -1 * Stake
    END
  WHERE BettingOfferID = '$variable-here'

 

 

[akitchin]

if you're wanting to run a batch update on all of the current bets, you would be able to do this by selecting each BettingOfferID from the betting offers table, and run an UPDATE within each loop. if you're going to do this, you might as well grab the result_left and result_right values at the same time and avoid the subqueries within that massive UPDATE, and perform the calculation in PHP:

 

$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.';
}

 

this will probably take a bit of time, but since it's a batch run, it will take care of all bets currently in your system. if you intend to run this query every single time you input the results for a certain match, you're better off limiting yourself to the UPDATE query previously offered using only the BettingOfferID for that match. otherwise if you just plan to batch UPDATE every now and again, i would add a flag field into the Betting_Offers table that tells you whether the calculation has taken place for that bet already; this will avoid re-calculating the profit for bets that have already undergone the update and will avoid wasting resources.

[Deyaan]

The web site is up and running, but I'm spending way too much time calculating profit one by one. I also don't want to batch update the whole table so I'm searching for something like this:

$get_all_bets = 'SELECT BettingOfferID, result_left, result_right FROM Betting_Offer WHERE BettingOfferID = '34' OR BettingOfferID = '35' OR BettingOfferID = '36' OR BettingOfferID = '37'';

$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.';

}

but when I run it like that, it doesn't work.

 

Also the flagging you recommended, I don't understand it. How to put flag column and configure it?

[Deyaan]

It's working well with

$get_all_bets = "SELECT BettingOfferID, result_left, result_right FROM Betting_Offer WHERE BettingOfferID IN ('xx', 'xxx', 'xxxx')";

 

Finally we can close this topic. It was very educational and useful for me. Thanx a lot!