[SOLVED] Very, very confused....

[Tovin]

Alright, so I'm fairly new to PHP (and to this forum) so this code is very simple.  But for some reason I can't get it to work.

 

What I'm trying to do, basically, is take a variable from a table, use it in an equation and place the results of the equation back into the table.

 

Like so:

 

while($r=mysql_fetch_array($result))

{

$points=$r["points"];

$plus = '6';

 

$total = $points + $plus;

 

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";

}

 

It works...once.  I'll change $plus to be something and rerun the script and usually it will update but NEVER to what I expected or wanted.  Last night 16 + 2 suddenly equalled 15?  I'm really not quite sure what I did wrong.  lol.

 

It's probably something extremely simple, I'm just not seeing it due to my inexperience.  Any help will be appreciated.

 

Thanks!

[corillo181]

ok the r["points"] doesn't have to be double quoted only single quote.

 

the plus can't be double quoted because it becomes a string, so $plus = 6;

 

numbers don't need quotes of any kind.

 

is the query executed inside the loop? because i don't see it! i only see the the condition.

 

it should be mysql_query($sql); right after the $sql line.

[jeet_0077]

Well thats really confusing--

You can try this

 

while($r=mysql_fetch_array($result))

{

echo $points=$r["points"];

$plus = 6; // Just omitted the quotes

 

echo $total = $points + $plus;

 

echo $sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";

}

 

And don't execute the update command. Just print that for verification.

I guess you can figure out where went wrong

 

[gerkintrigg]

also add this to your code unless you already have it, but haven't posted it here:

$q="SELECT * FROM `xxxxxxxxx` WHERE `id`='xxxxxxxx'";
$sql=mysql_query($q); // fill in where you have x's

[Tovin]

Alright....it's still being frustrating.

 

The script appears to run just fine only it's not adding for some reason.  When I echo $total at the bottom of the page it gives me the total I already started with.

 

For example, I start with 22 points.  I'm trying to add 6 points to this so $total should equal 28, right?  Apparently not.  lol.  It's still showing 22 and it's still not updating the table.  So for some reason $total is reading just the $points and not $plus?

 

Thanks for the help so far!

[Ken2k7]

Try:

<?php
while($r=mysql_fetch_assoc($result))
{
$points=$r['points'];
$plus = '6';

$total = $points + $plus;

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";
mysql_query($sql) or die(mysql_error());
}
?>

[Tovin]

Alright, thank you, that worked a little bit.  It's still, however, not reading the $points variable.  When I change this line:

 

$points=$r['points'] ;

 

to

 

$points='6';

 

it works just fine, updating the table and everything.  But left with the $r['points'] in there, the $points variable isn't working correctly.

 

Suggestions?

[Ken2k7]

Alright, thank you, that worked a little bit.  It's still, however, not reading the $points variable.  When I change this line:

 

$points=$r['points'] ;

 

to

 

$points='6';

 

it works just fine, updating the table and everything.  But left with the $r['points'] in there, the $points variable isn't working correctly.

 

Suggestions?

Could you post the rest of your code? (Especially your query)

[Tovin]

mysql_connect("host","username","password");

 

mysql_select_db("db");

 

$result = mysql_query("select * from table");

 

 

while($r=mysql_fetch_assoc($result))

{

$points=$r['points'];

$plus = '6';

 

$total = $points + $plus;

 

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";

mysql_query($sql) or die(mysql_error());

}

[therealwesfoster]

Try this:

 

while($r=mysql_fetch_array($result))
{
$plus = 6;

$sql = "UPDATE table SET points = points+{$plus} WHERE username = '$myusername'";
}

 

First, don't put INTEGERS inside of single or double quotes.. it will take it as a string..

 

Plus i took out all the php adding stuff and just put it in the SQL query (points=points+{$plus})

[Ken2k7]

mysql_connect("host","username","password");

 

mysql_select_db("db");

 

$result = mysql_query("select * from table");

 

 

while($r=mysql_fetch_assoc($result))

{

$points=$r['points'];

$plus = '6';

 

$total = $points + $plus;

 

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";

mysql_query($sql) or die(mysql_error());

}

1. Please use code tags.

2. You didn't define the variable $myusername

3. Wesf90, it wouldn't matter if it the numbers are in quotes. It still adds them as should.

[Tovin]

Sorry, will use code tags in the future.

 

Tried Wesf90's method and it's doing absolutely nothing to the table.

 

$myusername is being pulled from somewhere else.  It hasn't been causing the problem.

[Ken2k7]

Hmm...I don't see the error  :-\

 

Try:

<?php
mysql_connect("host","username","password") or die(mysql_error());
   
mysql_select_db("db") or die(mysql_error());

$result = mysql_query("select * from table") or die(mysql_error());

if (!$result || (mysql_fetch_assoc($result) < 1)) echo "No results.";
else {
while($r=mysql_fetch_assoc($result))
{
$points=$r['points'];
$plus = '6';

$total = $points + $plus;

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";
mysql_query($sql) or die(mysql_error());
}}
?>

[Tovin]

Still nothin'.

 

Would the problem be maybe that it's pulling data from the same table it's supposed to update?

[Ken2k7]

Still nothin'.

 

Would the problem be maybe that it's pulling data from the same table it's supposed to update?

O snap! My fault. Wrote the wrong thing there. Sorry, try this and tell me what it prints out:

 

<?php
mysql_connect("host","username","password") or die(mysql_error());
   
mysql_select_db("db") or die(mysql_error());

$result = mysql_query("select * from table") or die(mysql_error());

if (!$result || (mysql_num_rows($result) < 1)) echo "No results.";
else {
echo mysql_num_rows($result)." results found.";
while($r=mysql_fetch_assoc($result))
{
$points=$r['points'];
$plus = '6';

$total = $points + $plus;

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";
mysql_query($sql) or die(mysql_error());
}}
?>

[Tovin]

Hey!

 

It handily printed out the number of results it found (17) but the table still did not get updated.

[Ken2k7]

Okay, just making sure there are results.

 

Now try this, but read my comment.

<?php
mysql_connect("host","username","password") or die(mysql_error());
   
mysql_select_db("db") or die(mysql_error());
// edited here. Is $username defined at this point? //
$result = mysql_query("SELECT * FROM table WHERE username='$username'") or die(mysql_error());

if (!$result || (mysql_num_rows($result) < 1)) echo "No results.";
else {
while($r=mysql_fetch_assoc($result))
{
$points=$r['points'];
$plus = '6';

$total = $points + $plus;

$sql = "UPDATE table SET points = '$total' WHERE username = '$myusername'";
mysql_query($sql) or die(mysql_error());
}}
?>

[Tovin]

Ha!

 

That did it!  You're awesome!

 

$myusername was defined by the sessions.

 

So the problem was that I wasn't specifying what row to pull the $points data from?

[Ken2k7]

No, the problem was you're getting the points from other members and continually updating that one member so the number system is all wrong.

 

Say you have 10 points and I have 5.

 

You want to update yours. But you're also using my points which is 5 and you add 6 to get 11 and then you update it to yours. Since my query executes last, your info gets updated last and it's wrong.

[Tovin]

Ohh.

 

So I see.  lol!

 

Thank you SO much for your help.