Adding field from a website to a database

[Icewolf]

Hi

I am trying to figure out why this code is doubling the counts. What I am trying to do is a person would input a number on a website form that would add to the number that is already stored in the database. I have it working but the problem is that it is doubling the number of what is from the website and I am not sure why that its. The filter piece pulls back what is in the database then I have a update query to update the information being enterd on the screen.

// Formulate Query
$_POST["filter"];
$memid = mysql_real_escape_string($_POST["Member_ID"]);
$query = sprintf("SELECT Member_ID, Bank, Reward_1, Reward_2, Reward_3 FROM Points_Rewards WHERE Member_ID = '$memid'") or die("Could Not Formulate the Query");

//execute the SQL query and return records
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
   $message .= 'Whole query: ' . $query;
    die($message);
}

//fetch tha data from the database
while ($row = mysql_fetch_array($result))   
echo "<table width=750 cellspacing=2 cellpadding=2 border=2>
		<tr>
			<td bgcolor=#000000 width=150><font face=tahoma color=white>ID: {$row['Member_ID']}</font></td>". 
			"<td width=150><font face=tahoma>Bank: {$row['Bank']}</td>". 
			"<td width=150><font face=tahoma>Reward 1: {$row['Reward_1']}</td>". 
			"<td width=150><font face=tahoma>Reward 2: {$row['Reward_2']}</td> ". 
			"<td width=150><font face=tahoma>Reward 3: {$row['Reward_3']}</td>
		</tr>
  </table><br></font>";//display the results
 
// Formulate Update Query
$_POST["submit"];
$memid = mysql_real_escape_string($_POST["Member_ID"]);
$bankpd = $_POST['bank'];
$reward1 = $_POST['reward1'];
$reward2 = $_POST['reward2'];
$reward3 = $_POST['reward3'];



$query = "UPDATE Points_Rewards Set Bank = (Bank + '$bankpd'), Reward_1 = (Reward_1 + '$reward1'), Reward_2 = (Reward_2 + '$reward2'), Reward_3 = (Reward_3 + '$reward3') WHERE Member_ID = '$memid'";
$result = mysql_query($query) or die(mysql_error());

[ginerjm]

Sorry - but I can't see your arithmetic problem in this code.  I do however see some very questionable tactics throughout your code.  Can you explain what the first line ($_POST["filter"]  is doing?  That and the later line ($_POST['submit'])?  Don't they give you some kind of error?

 

Also - what is the purpose of the sprintf function use when building your query statement?

Also - do you really mean to create a complete table for each and every row of your results?

And - why the closing </font> tag at the end of the table, but individual opening font tags for each td element?

 

As for the math issue, perhaps changing your update query to drop the quotes on numeric values - AFAIK you only need to quote string values, not numeric ones.

[mac_gyver]

duplicate execution of your code is usually caused by -

 

1) you are running your code twice by including it or looping over it two times. what's all the code on your page (less any database credentials)? basically your page isn't doing what you expect, you need to provide all the code on your page that duplicates the problem if you need someone else to determine if this is what is causing the problem.

 

2) the browser is requesting your page twice or your web server setup is causing your code to be executed two times. after you eliminate #1 as the cause, tackle this possibility.

 

for both of these possibilities, you may want to consider logging some information in your code (see the error_log() statement) with at least the the microtime() value and the update query that is in $query so that you can see when and how many times the code is running and what the values are. this would pin the problem down to the php side (i/we have seen cases where people have used triggers in the database to perform actions and the problem is actually occurring in the database and not in the php code.)

Edited June 8, 2013 by mac_gyver

[mac_gyver]

as a continuation of the above reply: i just thought of another possibility for a second execution of that query that would be exposed by having all the code on your page. you may be running another mysql_query($query) statement after the code you posted.

[Icewolf]

Hi

Here is all of the code. I might be doing this wrong but what I have is when the user goes to this page I want to display the data from the database (Filter). Once they do this they can add points (Submit).

<?php 
$username = "username";
$password = "password";
$hostname = "localhost";
 
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
 or die("Unable to connect to MySQL");
echo "<font face=tahoma color=#ff000><b>Connected to MySQL</b></font><br><br>";

//select a database to work with
$selected = mysql_select_db("pdogclan_points",$dbhandle)
  or die("Did this change");
  
// Formulate Query
$_POST["filter"];
$memid = mysql_real_escape_string($_POST["Member_ID"]);
$query = sprintf("SELECT Member_ID, Bank, Reward_1, Reward_2, Reward_3 FROM Points_Rewards WHERE Member_ID = '$memid'") or die("Could Not Formulate the Query");

//execute the SQL query and return records
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
   $message .= 'Whole query: ' . $query;
    die($message);
}

//fetch tha data from the database
while ($row = mysql_fetch_array($result))   
echo "<table width=750 cellspacing=2 cellpadding=2 border=2>
		<tr>
			<td bgcolor=#000000 width=150><font face=tahoma color=white>ID: {$row['Member_ID']}</font></td>". 
			"<td width=150><font face=tahoma>Bank: {$row['Bank']}</td>". 
			"<td width=150><font face=tahoma>Reward 1: {$row['Reward_1']}</td>". 
			"<td width=150><font face=tahoma>Reward 2: {$row['Reward_2']}</td> ". 
			"<td width=150><font face=tahoma>Reward 3: {$row['Reward_3']}</td>
		</tr>
  </table><br></font>";//display the results
 
// Formulate Update Query
$_POST["submit"];
$memid = mysql_real_escape_string($_POST["Member_ID"]);
$bankpd = $_POST['bank'];
$reward1 = $_POST['reward1'];
$reward2 = $_POST['reward2'];
$reward3 = $_POST['reward3'];



$query = "UPDATE Points_Rewards Set Bank = Bank + '$bankpd', Reward_1 = Reward_1 + '$reward1', Reward_2 = Reward_2 + '$reward2', Reward_3 = Reward_3 + '$reward3' WHERE Member_ID = '$memid'";
$result = mysql_query($query) or die(mysql_error());
		


if(mysql_query($query)){
echo "updated";}
else{
echo "fail";}

//close the connection
mysql_close($dbhandle);

?>

[mac_gyver]

what do you see that is wrong with the following lines from your code that might cause the update query to be executed two times?

 

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

[Icewolf]

Thank you so much I knew it had to be something stupid. That fixed the double points but now when I change the amounts on the forum it looks like it is keeping the counts and adding those points after they are gone. Do I need to clear the cookies or something after every time this is ran?

Edited June 13, 2013 by Icewolf