Deyaan Posted July 15, 2009 Share Posted July 15, 2009 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 Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 15, 2009 Share Posted July 15, 2009 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? Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 15, 2009 Author Share Posted July 15, 2009 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". Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 16, 2009 Share Posted July 16, 2009 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. Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 16, 2009 Author Share Posted July 16, 2009 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? Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 16, 2009 Share Posted July 16, 2009 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. Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 17, 2009 Author Share Posted July 17, 2009 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! Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 17, 2009 Share Posted July 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 20, 2009 Author Share Posted July 20, 2009 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? Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 20, 2009 Share Posted July 20, 2009 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? Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 20, 2009 Author Share Posted July 20, 2009 Unfortunately, that didn't work. I'll try to figure out something Thanx for all the help! Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 20, 2009 Author Share Posted July 20, 2009 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! Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 20, 2009 Author Share Posted July 20, 2009 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? Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 20, 2009 Author Share Posted July 20, 2009 Ignore upper post about profit, forgot to put integers. Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 21, 2009 Share Posted July 21, 2009 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? Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 21, 2009 Author Share Posted July 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 21, 2009 Share Posted July 21, 2009 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' Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 21, 2009 Author Share Posted July 21, 2009 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' Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 21, 2009 Share Posted July 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 28, 2009 Author Share Posted July 28, 2009 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? Quote Link to comment Share on other sites More sharing options...
Deyaan Posted July 28, 2009 Author Share Posted July 28, 2009 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.