Page 1 of 1

Help Update Income

Posted: Wed Apr 28, 2010 2:27 pm
by kybaker
Hello again,

I have created a form that will update the winner of a sport event between two teams. This update of the database works fine.
However I now need it to also update my income of the users that have selected the correct winner.

Basically I will use 2 tables:
1)sportEvents - which stores: the 2 teams, team ids, game data/time and so one
2)makeBets - which stores the sport event id (references sportEvents), the bet amount, and the member_id that bet on the game

I am unable to get my php and mysql to actually update my database properly and I'm pretty sure my MySQL commands work fine.

My code is as follows:

Code: Select all

<?php
	//Start session
	session_start();
	
	//Include database connection details
	require_once('config.php');
	
	//Array to store validation errors
	$errmsg_arr = array();
	
	//Validation error flag
	$errflag = false;
	
	//Connect to mysql server
	$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
	if(!$link) {
		die('Failed to connect to server: ' . mysql_error());
	}
	
	//Select database
	$db = mysql_select_db(DB_DATABASE);
	if(!$db) {
		die("Unable to select database");
	}
	
	//Function to sanitize values received from the form. Prevents SQL injection
	function clean($str) {
		$str = @trim($str);
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
	}
	
	//Sanitize the POST values
	$winner_id = clean($_POST['school_name']);
	$game_id = clean($_POST['game_id']);
	$member_id = $_SESSION['SESS_MEMBER_ID'];

	$data = mysql_query("SELECT school_name FROM teams WHERE team_id='$winner_id'")
		or die(mysql_error());
	$gameupdate = mysql_fetch_array( $data );
	
        //determines school name from team_id
	$winner = $gameupdate['school_name'];

	//Create update winner query
	$qry = "UPDATE sportEvents SET game_winner='$winner' WHERE sport_event_id ='$game_id'";
	$result = mysql_query($qry);
	//if query is successful
	/////////////////////////////This Section Seems To Be the Problem////////
       if($result) {
		//pull information from bet where bet is on right game
		$income_qry = "SELECT * from makeBets where sport_event='$game_id'";
		$income_results = mysql_query($income_qry)
			or die("Query failed");
		while($incomeupate = mysql_fetch_array($income_results)){
			$bet_amt = $incomeupdate['bet_amt'];
			$selection = $incomeupdate['selection'];
			$member = $incomeupdate['member_id'];
			$inc = $bet_amt + $bet_amt;
			if($selection==$winner){
				$qry_update_income = "UPDATE users SET income='$inc' WHERE member_id ='$member'";
				$update_results = mysql_query($qry_update_income);
				header("location: bet-success.php");
				exit();
			}
			else {
				continue;
			}
		}
	}
	
	//Check whether the query was successful or not
	if($result) {
		header("location: update-games-success.php");
		exit();
	}else {
		die("Query failed");
	}
?>

Re: Help Update Income

Posted: Wed Apr 28, 2010 5:30 pm
by mecha_godzilla
When you're running the main script, is your browser redirected to the 'bet-success.php' script (indicating that the $selection==$winner test worked)? You haven't got any error handling on the $qry_update_income query that you make, so this part could be failing without you knowing. If you have access to it, have you tried running a sample query in phpMyAdmin to see what's happening - you just need to put some sample values in the query where your $inc and $member values are at the moment.

If you're not getting an error it usually means that MySQL ran the command but the values didn't mean anything to it when it processed the query (so it returns 0 rows). To find out if the right values were sent to a MySQL query which then redirects after it's completed, I usually add the values to the end of the address I'm redirecting to (only when testing of course). If you want to do this, find the line that looks like:

Code: Select all

header("location: bet-success.php");
exit();
and replace it with:

Code: Select all

$redirected_address = 'Location: bet-success.php?inc=' . $inc . '&member=' . $member;
header ($redirected_address);
exit();
HTH,

Mecha Godzilla

Re: Help Update Income

Posted: Thu Apr 29, 2010 7:53 am
by kybaker
I put some error handling in there but I haven't tried it out yet. Instead of a while loop don't I need a for loop? As in:

1)store query results into array
2)for each part of array store information (bet_amt, selection, member), then update income

or can I do that using just my while loop?

Lastly, I have used this sample query with sample values ("UPDATE users SET income='2' WHERE member_id='23'") which works fine in the command line but remember this will need to work for more than just one member. I'll keep on chugging along but if someone would let me know if I can do everything I need inside my 'while loop' or if I need to do something else as in a 'for loop'.

Code: Select all

<?php
        //Start session
        session_start();
       
        //Include database connection details
        require_once('config.php');
       
        //Array to store validation errors
        $errmsg_arr = array();
       
        //Validation error flag
        $errflag = false;
       
        //Connect to mysql server
        $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        if(!$link) {
                die('Failed to connect to server: ' . mysql_error());
        }
       
        //Select database
        $db = mysql_select_db(DB_DATABASE);
        if(!$db) {
                die("Unable to select database");
        }
       
        //Function to sanitize values received from the form. Prevents SQL injection
        function clean($str) {
                $str = @trim($str);
                if(get_magic_quotes_gpc()) {
                        $str = stripslashes($str);
                }
                return mysql_real_escape_string($str);
        }
       
        //Sanitize the POST values
        $winner_id = clean($_POST['school_name']);
        $game_id = clean($_POST['game_id']);
        $member_id = $_SESSION['SESS_MEMBER_ID'];

        $data = mysql_query("SELECT school_name FROM teams WHERE team_id='$winner_id'")
                or die(mysql_error());
        $gameupdate = mysql_fetch_array( $data );
       
        //determines school name from team_id
        $winner = $gameupdate['school_name'];

        //Create update winner query
        $qry = "UPDATE sportEvents SET game_winner='$winner' WHERE sport_event_id ='$game_id'";
        $result = mysql_query($qry);

       if($result) {
                //pull information from bet where bet is on right game
                $income_qry = "SELECT * from makeBets where sport_event='$game_id'";
                $income_results = mysql_query($income_qry)
                        or die("Query failed");
                while($incomeupate = mysql_fetch_array($income_results)){
                        $bet_amt = $incomeupdate['bet_amt'];
                        $selection = $incomeupdate['selection'];
                        $member = $incomeupdate['member_id'];
                        $inc = $bet_amt + $bet_amt;
                        if($selection==$winner){
                                $qry_update_income = "UPDATE users SET income='$inc' WHERE member_id ='$member'";
                                $update_results = mysql_query($qry_update_income)
                                         or die("Update Query Failed");
                        }
                        else {
                                continue;
                        }
                }
        }
        header("location: update-games-success.php");
        exit();
        }else {
                die("Query failed");
        }
?>
Thanks for the help!

Re: Help Update Income

Posted: Thu Apr 29, 2010 9:33 am
by kybaker
Hello again,

So I've updated my code. I get it to update the income BUT only for one user. It does not update it for every user that made a correct bet.

Anyone see anything that I am missing?

New Code:

Code: Select all

        if($result) {
                //pull information from bet where bet is on right game
                $income_qry = "SELECT * from makeBets where sport_event='$game_id'";
                $income_results = mysql_query($income_qry)
                        or die("Query failed");
                while($incomeupdate = mysql_fetch_array($income_results)){
                        $bet_amt = $incomeupdate['bet_amt'];
                        $selection = $incomeupdate['selection'];
                        $member = $incomeupdate['member_id'];
						
						//Query to get original income
						$iqry = "SELECT income FROM users WHERE member_id ='$member'";
						$income_results = mysql_query($iqry);
						$income_array = mysql_fetch_array($income_results);
						$income = $income_array['income'];
						
						
						//if selection is a winner; update income
                                                if($selection==$winner){
								//add original income + (bet_amt *2)
								$new_income  = $income + $bet_amt + $bet_amt;
                                                                $qry_update_income = "UPDATE users SET income='$new_income' WHERE member_id ='$member'";
                                                                $update_results = mysql_query($qry_update_income)
                                                                       or die("Update Income Query Failed");
                                                }
                                                else {
                                                       continue;
                                                }
                                }
				header("location: update-games-success.php");
        		        exit();
               }
		else{
			echo "Update Game Winner Query Failed";
		}

Re: Help Update Income

Posted: Thu Apr 29, 2010 3:56 pm
by mecha_godzilla
I've looked at your script but I can't quite see where the problem is, so I suggest you put the following after each query

Code: Select all

$number_of_rows = mysql_num_rows($result);
then echo() out the $number_of_rows values to make sure the expected number of rows is being returned, as this determines how many times the different parts of your script will loop - obviously change $result to the relevant value in your scripts. You're using the $income_results value twice (to hold the $income_qry and $iqry queries) so this *might* be something to look at - I'm not sure what happens at runtime if you're looping through a script 'x' number of times and you then change the value of 'x' while you're still inside that loop, though it might not be relevant.

Am I right in thinking that there should also be some code in this script to decrease the user's income if they didn't win?

Just another point (not related to your question) - it might be possible to streamline this script to avoid the query where you get the member's income and store it into $income_array, because you don't need to know what the user's income is, just how much to increase/decrease it by. Code might look something like this and seems to work correctly for both integer and floating point numbers:

Code: Select all

$qry_update_income = "UPDATE users SET income=income+('$bet_amt' * 2) WHERE member_id ='$member'";
Regards,

Mecha Godzilla