MySql update not updating


#1

Hello,

I have recently created a table where members of a guild have points attributed to there characters.
On the backend of this scorekeeping system i wanted a way to select a large number of characters (via check boxes) and add a set value to all of their existing respective scores.

here is my code that is supposed to handle the input from the first page:

[code]<?
$username=“";
$password="
”;
$database="********";

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( “Unable to select database”); // connects to db
$query=“SELECT * FROM dkp”;
$query2=“UPDATE dkp SET dkp=’$ud_dkp’ WHERE id=’$id’”;

$result=mysql_query($query); //run first quesry

$num=mysql_numrows($result); //count rows

$ud_val=$_POST[‘value’]; // grab a # from page before which we will be adding
// to characters selected on page before

$i=0;
while ($i < $num) { // loop through all the rows

$character=mysql_result($result,$i,“character”); // grab chacter name from db

$tempchar=$_POST[$character]; // grab the post data of character name from the page before

if ($tempchar){ // this character value is either selected (ture) or not (false)

										// if it was selected it needs to be updated

$dkp=mysql_result($result,$i,“dkp”); // grab existing pt value
$ud_dkp=$ud_val+$dkp; // add set amt to existing
$id=mysql_result($result, $i,“id”); // grab character id
echo “$character updated to $ud_dkp
”;

mysql_query($query2); // run query 2

}

$i++;
}

mysql_close(); // close db connection

?>
[/code]

i run through this and it actually does echo only those i have selected and it does display their new ‘dkp’ as the correct updated amount, however when i goto a new page that simply outputs the data base, i see that nothing has changed.
the information isn’t being updated. i’ve tried using mysql_close() after the first query then opening a new connection for each addition but that also does not work.

if any of you could help me out i would be most appriciative.
thx~


#2

Try this:

// this turns on all errors so you can fix anything that may be "hiding"
error_reporting(E_ALL);

/*  For security reasons you should never have the database access information
 *  in the script itself.  It is better to include it from a file outside the
 *  web directory.  For more information see the PHP Security Consortum's
 *  "PHP Security Guide".
 */
$username="********";
$password="********";
$database="********";

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");  // connects to db
$query="SELECT * FROM dkp";
$query2="UPDATE dkp SET `dkp`='$ud_dkp' WHERE `id`='$id'";

$result=mysql_query($query); //run first quesry
// always make sure the query went through
if(!$result)
{
	echo 'Query1 Error - Reason given: '.mysql_error();
	exit();
}
$num=mysql_num_rows($result); //count rows <-- error in function name - fixed
$ud_val=$_POST['value'];    // grab a # from page before which we will be adding
                     		// to characters selected on page before
$i=0;
while ($i < $num) // <- you might want to look into "for" loops for this
{         // loop through all the rows
    $character=mysql_result($result,$i,"character");   // grab chacter name from db
	$tempchar=$_POST[$character];  // grab the post data of character name from the page before
	if ($tempchar)  // this character value is either selected (ture) or not (false)
	{                                       
                                 // if it was selected it needs to be updated
		$dkp=mysql_result($result,$i,"dkp");      // grab existing pt value
		$ud_dkp=$ud_val+$dkp;                  // add set amt to existing
		$id=mysql_result($result, $i,"id");         // grab character id
		echo "$character updated to $ud_dkp<br>";    
		$a = mysql_query($query2);                  // run query 2
		// always make sure the query went through
		if(!$a)
		{
			echo 'Query2 Error - Reason given: '.mysql_error();
			exit();
		}
	}
	$i++;
}
mysql_close();                        // close db connection

Please note the changes I made and the comments I added. Let us know if it still doesn’t work and any errors it may be giving.


#3

ok, i changed the code accordingly and ran a test run.
i got alot of output (one for each $character in the db)

my test selected the character ‘Demos’ whose dkp = 102.5
and i used an adding value of 10

line 16 is:

mysql_connect(localhost,$username,$password);

line 35 is:

$tempchar=$_POST[$character];

after this run the value is still 102.5. hopefully these errors can tell you something about my problem because i am still as perplexed as ever, however it is only giving those undefined index errors for names that are not selected on page 1. i don’t know if that effects the mysql table update though.

thx again gfor any help u can provide


#4

Hmm - localhost needs to be quoted.

the warnings it is giving you is because of the uninitialized values that the post is giving you. So we can fix that with a ternary operation (make sure to look this up in the manual.

$tempchar= (isset($_POST[$character]) ? $_POST[$character] : '' ;

Other then that - I will need to see the new code you have written with my hints.


#5

hmm, well rather then replace my current $tempchar initialization with that one, i removed it entirely and had the if statement simply do the isset($_post~~ etc.) thing. also localhost is quoted. so now no errors appear however the update issue wasnt resolved.

!!! it works now! during the process of typing this post it is fixed!

running a quick test i had made a line

echo "Run: $query2";

and found it to output that it was running the query based on initial values for $ud_dkp and $id which were zero.

removing these initial id’s brought back the error messages that they did not exist
so i just moved the entire text from $query2

UPDATE dkp SET `dkp`='$ud_dkp' WHERE `id`='$id'
down to the variable $a you created for me. and with that all is well, it works fine now and i can finally edit multiple accounts by the same value at once!!

thank you so much!! once again this forum not only saves me from a problem but teaches me more n more about coding! i love this place!

thanks again,
-nils brasz

(edit: if u wanna see what it actually is u r helping me out with, check it out @ : http://kos.theswede.net/view.php?page=dkp ) this is only the front end but it is made much easier to manage now ^_^.