Update Not Working

[carleihar]

This simple code to "buy" a horse from a database is returning, "the money could not be updated". I simply cannot figure out why! Any help?

<?php 


$page_title = 'Buy a Horse';
include ('../includes/header.html');
echo '<h1>Buy a Horse</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From for_sale.php
$id = $_GET['id'];
echo "Horse ID: ";
echo "$id";



} else { // No valid ID, kill the script.
echo '<p class="error">This page has been accessed in error.</p>';
echo 'Please go back to the for sale page. <br />';
echo '<a href="http://liveequian.com/htdocs/pages/for_sale.php">For Sale Page</a>';
include ('../includes/footer.html'); 
exit();
}



require_once ('../../mysqli_connect.php');

// Check if the form has been submitted:
if (isset($_GET['submitted'])) {

if ($_GET['sure'] == 'Yes') { // Buy the horse.



//fetch price
$q = "SELECT price AS price FROM horses WHERE horse_id='$id'";		
$r = @mysqli_query ($dbc, $q); // Run the query.

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$price=$row['price'];

}

	// Make the query:
	$q = "UPDATE users SET money=(money-$price) WHERE username='{$_COOKIE['username']}'";		
	$r = @mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));
	if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

		echo 'change money ran okay.<br />';

		$q = "UPDATE horses SET user_name='{$_COOKIE['username']}' WHERE horse_id='$id'";		
		$r = @mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));
		if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

			echo 'username change ran okay.<br />';

			$q = "UPDATE horses SET for_sale='0' WHERE horse_id='$id'";		
			$r = @mysqli_query ($dbc, $q);
			if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

				echo 'for sale to zero ran okay. <br />';

				$q = "UPDATE horses SET price='0' WHERE horse_id='$id';";		
				$r = @mysqli_query ($dbc, $q);
				if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
					echo 'everything ran okay!';

					// Print a message:
					echo '<p>The Horse is now yours.</p>';	
				} else {
					echo 'the price could not be updated.';
				}
			} else {
				echo 'for sale could not be changed.';
			}	 
		} else {
			echo 'could not change money.';
		}
	} else { // If the query did not run OK.
		echo 'the money could not be updated.'; // Public message.

		//echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
	}

} else { // No confirmation of deletion.
	echo '<p>The user has NOT bought the horse.</p>';	
}

} else { // Show the form.

// Retrieve the user's information:
$q = "SELECT horse_name, price FROM horses WHERE horse_id=$id";
$r = @mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

	// Get the user's information:
	$row = mysqli_fetch_array ($r, MYSQLI_NUM);

	// Create the form:
	echo '<form action="buy_horse.php" method="get">
<h3>Name: ' . $row[0] . '</h3>
<p>Are you sure you want to buy this horse?<br />
<input type="radio" name="sure" value="Yes" /> Yes 
<input type="radio" name="sure" value="No" checked="checked" /> No</p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="' . $id . '" />
</form>';

} else { // Not a valid user ID.
	echo '<p class="error">This page has been accessed in error.</p>';
}

} // End of the main submission conditional.



mysqli_close($dbc);

include ('../includes/footer.html');
?>

 

 

[Psycho]

// Make the query:
$q = "UPDATE users SET money=(money-$price) WHERE username='{$_COOKIE['username']}'";
$r = @mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));
if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

 

Your query doesn't seem to be too restrictive. If it is updating more than one row then the IF statemetn will return false. That's just a possibility. Try echoing mysqli_affected_rows($dbc) to the page after that query to see what it is returning.

[carleihar]

Hmm..it's returning 0, although I still don't know why.

 

Here is the column and row straight out of myphpadmin. Am I missing something? Even when I hand feed the username with "blah", it still doesn't work.

 

user_id email pass user_level active registration_date username barn_name awards money

17 xxx@xxxxxx c6ecf2b0691fdd614e35ba4d1e12a5b5a840eef3 0 NULL 2010-01-16 17:56:30 blah[/td][td] 1000000

[PFMaBiSmAd]

Echo the query $q to see what exactly is in it. You will probably find that the $_COOKIE is either empty or has some other value in it.

 

You need to validate and escape ALL external string data being put into a query and in this case, if you are expecting someone to be logged in, your page should be checking for that at the start of the page and only executing the relevant code on the page if the current visitor is logged in.