UPDATE users table using If

[Bradley99]

Alright, So I'm tryna code this page so when I update some results of fights, users who picked the correct fighter to win etc will have their points updated, but at the moment, As soon as I open the Add results page, It's just giving the Admin account 35 points, ANY help atall along the right path would be brilliant.

 

<?

$page="betting";

if ($_POST['Submit']){
$viewresult = $_POST['event_id'];
$fight_id = $_POST['fight_id'];
$winner = $_POST['winner'];
$method = $_POST['method'];
$round = $_POST['round'];

{

mysql_query("INSERT INTO results (`event_id` , `fight_id` , `winner` , `method` , `round`)
VALUES ('$fetch->id', '$fight_id', '$winner', '$method', '$round')") or die (mysql_error());
echo "Results Updated";

}}

$mywinner=mysql_fetch_object(mysql_query("SELECT * FROM betting WHERE user_id='$user->id' AND fight_id='$fight_id'"));

{
if ($mywinner->fighter_id == $winner && $mywinner->round == $round && $mywinner->method == $method){
$win="35";
}elseif ($mywinner->fighter_id == $winner && $mywinner->round == $round){
$win="25";
}elseif ($mywinner->fighter_id == $winner && $mywinner->method == $method){
$win="25";
}elseif ($mywinner->round == $round && $mywinner->method == $method){
$win="20";
}elseif ($mywinner->round == $round){
$win="10";
}elseif ($mywinner->fighter_id == $winner){
$win="15";
}elseif ($mywinner->method == $method){
$win="10";
}else{
exit();
}
$newpoints=$user->points+$win;

mysql_query("UPDATE users SET points='$newpoints' WHERE id='$user->id'");
echo "Results Updated";
?>

[Jessica]

You have way too many random { and }.

 

Turn on error reporting.

[Bradley99]

There's my error reports.  .

Notice: Undefined index: Submit in /home/mmamount/public_html/fightclub/eng/results.php on line 26

Notice: Undefined variable: fight_id in /home/mmamount/public_html/fightclub/eng/results.php on line 41

Notice: Trying to get property of non-object in /home/mmamount/public_html/fightclub/eng/results.php on line 44

Notice: Undefined variable: winner in /results.php on line 44

Notice: Trying to get property of non-object in /results.php on line 44

Notice: Undefined variable: round in /results.php on line 44

Notice: Trying to get property of non-object in /results.php on line 44

Notice: Undefined variable: method in /results.php on line 44
Results Updated

 

Line 44 is

if ($mywinner->fighter_id == $winner && $mywinner->round == $round && $mywinner->method == $method){

 

I mean, It seems to work, just not the way I wan't. I THINK

[Psycho]

You are making this too difficult. I wouldn't even have a "results" table and instead would simply add fields to the "fights" table for winner id, round, etc.

 

I was going to suggest that you only determine a user's amount dynamically, but I see that you are awarding different amounts based upon different criteria. If you were to only calculate the total dynamically and were to change the awarded amounts at a later time it would throw everything off. But, you still have two choices. You can update the user's "points" directly as you are trying to do, but then you lose the history of which bet provided what amounts. I would suggest the following:

 

You need one table to track add/removes of user points that take place outside of bets. I would assume there must be some manner to do this otherwise how do new users start and what do they do with their points? Second, use the current bets table to track all transactions with regard to bets. You could have one column for the bet amount and another column for the win amount (The default is 0). Then, when a match is completed update the win amount as needed. Lastly, you just need an appropriate query to get a user's total points. This format allows you to easily pull a history of every addition/subtraction to a users points as needed.

 

OK, to update the "betting" table at the end of a match you could run ONE query to update all the appropriate records. You can put an IF statement in the UPDATE clause for all the different amounts. but, that gets difficult to read. I would create a few different queries for each award amount.

 

Here is an example of one of the queries to update the betting table with win amounts after a fight. You would need an appropriate query for each award amount.

UPDATE betting
SET win_amount = bet_amount * 35
WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method = '$method'

 

Then to get the total points for a user you need to pull data from the betting table as well as the "inventory" table where points are added/subtracted outside of betting activities. How this is done would be based upon how much data is needed. You could do this through a UNION to merge data from the two tables into a combined results set.

[Bradley99]

Thanks for taking the time. Point's are NOT subtracted, only awarded. Point's also CANNOT be used. Points are simply awarded to show who gains the most points from picking the correct results. So user A wins 700 points he's at the top of the Standings table, User B wins 500 He's 2nd and so on.

 

The table name 'Betting' is misleading, as users don't actually bet anything. They simply pick the winners, round & method, 15 points if you get the winner, 10 for correct round and 10 for correct method. I do agree that simply adding onto the betting table is a great idea, then just with that table I can produce the standings via points per user per event/fight, as well as cross referencing user_id & points gained per fight.

 

The only thing I'm struggling with is actually ADDING points onto the users table if a user wins as I add the results. I actually don't know how I would code that.

 

Thanks

[Bradley99]

Alright, so my betting table now looks like this. .

 

bet_id - event_id - fight_id - user_id - fighter_id - method - round - bonus - winner - win_method - win_round - win_points

 

So as you suggested, I can use something along the lines of:

UPDATE betting
SET win_amount = bet_amount * 35
WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method = '$method'

 

To update the betting table, then grab total points won in each fight/event or whatever. What I need to know, is how to adjust my previous code to actually add the win points for users.

[Psycho]

OK, if you are simply awarding a flat amount of 23, 25, 10, etc. then the query to update the betting table would not have a multiplier. Here are the queries to update the betting table with the win amounts.

UPDATE betting SET win_amount = 35 WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method = '$method'

UPDATE betting SET win_amount = 25 WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method <> '$method'

UPDATE betting SET win_amount = 25 WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round <> '$round'
  AND method = '$method'

UPDATE betting SET win_amount = 20 WHERE fight_id='$fight_id'
  AND fighter_id <> '$winner'
  AND round = '$round'
  AND method = '$method'

UPDATE betting SET win_amount = 15 WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round <>'$round'
  AND method <> '$method'

UPDATE betting SET win_amount = 10 WHERE fight_id='$fight_id'
  AND fighter_id <> '$winner'
  AND round = '$round'
  AND method <> '$method'

UPDATE betting SET win_amount = 10 WHERE fight_id='$fight_id'
  AND fighter_id <> '$winner'
  AND round <> '$round'
  AND method = '$method'

 

That may not be the most efficient, but 7 queries as opposed to an unlimited amount of looping queries is definitely better. The to get the user point totals you would just run something like this

SELECT u.name, SUM(b.win_amount) as total_points
FROM users AS u
LEFT JOIN betting AS b
  ON b.user_id = u.id
ORDER BY total_points

[Bradley99]

Oh you are the man! Updating betting table works great now!

 

For total point's the query didn't seem to work, I tried this:

      <?
   $standing = mysql_query("SELECT a.user_id, SUM(b.win_points) as total_points ORDER BY total_points");
   while($the=mysql_fetch_object($standing)){ 
   

   
  echo "  <tr><td class='profilerow'>1</td><td class='profilerow'>$the->username</td><td class='profilerow'>$the->total_points</td>
  
   </tr>"; } ?>

But that didn't work either.

[Bradley99]

Scrap the above code, this code works:

<?php

$query = "SELECT user_id, SUM(win_points) FROM betting"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['user_id']. " = ". $row['SUM(win_points)'];
echo "<br />";
}
?>

 

but I'm having trouble on how to place/call the username via the user_id?

[Bradley99]

Also, is there a way of doing...

UPDATE betting SET win_amount = 35 WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method = '$method'
  IF bonus='Yes' win_amount *2

So IF bonus is Yes, then the win amount is doubled?

[Psycho]

The query I posted to get the username AND the total points is where you need to go back to. If something isn't working right then let's figure that out. Don't go and create a "flat" query to get the data from the betting table then ask how you get the username. I already provided that. I don't have your database and am not going to take the time to ensure all the field names in the query are correct.

 

So IF bonus is Yes, then the win amount is doubled?

 

Ok, first off if you have a variable for "bonus" the value should NOT be 'yes'. When you use text as values for those sorts of things you have to add logic to interpret then. Normally you should use the logical TRUE/FALSE values, but in this case I would suggest using 1 or 0 - which can also be interpreted as true/false. But, that way you can also use the "bonus" value directly in your UPDATE query with no elaborate coding to handle the logic:

 

UPDATE betting
SET win_amount = 35 * ($bonus +1)
WHERE fight_id='$fight_id'
  AND fighter_id = '$winner'
  AND round = '$round'
  AND method = '$method'

 

So, if $bonus = 0 (i.e. bonus NOT enabled) then the set value will be

SET win_amount = 35 * (0 +1)

35 * (0 +1)  =  35 * (1)  =  35

 

If $bonus = 1 (i.e. bonus IS enabled) then the set value will be

SET win_amount = 35 * (1 +1)

35 * (1 +1)  =  35 * (2)  =  70

[Bradley99]

Bonus works perfect, I also went back to your original code given on the grabbing username & total point's, fiddled with it & now thats working.

 

Can't thank you enough!!

 

Thanks!