Update and MYSQL

[Aravinthan]

Hi,
I am doing a ladder for my clan website. I already have a database for the tournament( points which is INT and eso(username0 which is Varchar). What I need is that, that I can add points to ppl after a game. So i tought of doing this a form that asks the username of the player and how much to be added:

                        <form name="Join Tournament" action="update_final.php" method="post">
        <table border="0" cellspacing="0" cellpadding="0">
         <tr>
          <td>
           User's ESO to be updated:
          </td>
          <td>
           <input type="text" name="user">
          </td>
         </tr>
         <tr>
          <td>
           Points to be added:
          </td>
          <td>
           <input type="text" name="newp">
          </td>
         </tr>
         <tr>
          <td>
           <input type="submit" name="submit" value="Submit">
           <input type="reset" name="reset" value="Reset">
          </td>
         </tr>
        </table>
       </form>

And here is my MYSQL update script(update_final.php)

<?php
$user = $_POST['user'];
$newp = $_POST['newp'];
$point = $newp + points;
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
mysql_query("UPDATE wrwrtc SET points=$point WHERE eso=$user")
or die(mysql_error());
echo "The player got succesfully his points. <a href='home.php'>Go back</a>";
?>

But I get this error:

Unknown column 'Aravinthan' in 'where clause'

THanks for your help.

 

[worldwise001]

You need to add another set of quotes in your user variable, especially since eso is a varchar field, it's like a string and thus mysql treats it as such. PHP won't automatically put quotes, so you have to add them yourself, escaping them as necessary with a \ (backslash);

So instead of

$user = $_POST['user'];

put

$user = "\"".$_POST['user']."\"";

Try that. It also can prevent security flaws/hacks

If it doesn't work, post back.

 

[easykey]

The above might be right but I believe the problem is in this line:

mysql_query("UPDATE wrwrtc SET points=$point WHERE eso=$user")

Should say WHERE eso='$user' - you need to enclose the data in single quotes. Same goes for $point.

It is also good practice to escape the input using:

$user=mysql_real_escape_string($_POST['user']) - check this function out for proper usage.

 

[Sohail]

Do you have PHP version 2 or 1?

 

[Aravinthan]

This is hte php version: PHP 5.2.5 - Intermediate Ver.
I've edited the code:

<?php
$user = "\"".$_POST['user']."\"";
$newp = $_POST['newp'];
$point = $newp + points;
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
mysql_query("UPDATE wrwrtc SET points=$point WHERE eso='$user'")
or die(mysql_error());
echo "The player got succesfully his points. <a href='home.php'>Go back</a>";
?>

I get the sucelfully edited message, but when i check the ladder, it still at 0 points...
I tried putting :

mysql_query("UPDATE wrwrtc SET points='$point' WHERE eso='$user'")
or die(mysql_error());

Single-quotes on the $points but the same thing...
Thanks for your help

 

[easykey]

Do some debugging:
echo $user, $newp, $point;

and see if they hold what you expect...

not sure about this:
$user = "\"".$_POST['user']."\"";

i dont think there is any problem with what you did originally with this line
Edit:
Another debugging tip. Instead of

mysql_query("UPDATE wrwrtc SET points=$point WHERE eso='$user'")

try

$sql="UPDATE wrwrtc SET points=$point WHERE eso='$user'";
mysql_query($sql);

No difference except during debugging you can insert echo $sql; before the query to display what will be sent to MySQL. Often the query is syntactically correct but the data is wrong and unexpected results come back.

Not sure if x10 has access to phpmyadmin since i cant get my account going, but if it does you can copy this echoed sql statement from your web page and paste it into phpmyadmin. This will prove if any error id on the php side or the MySQL side.

 

[Aravinthan]

I found the error. its:

$user = "\"".$_POST['user']."\"";

It ptus a " and a " the name of hte user, so the system can't find it. But the thing is it doesnt add the points he had before... It jsut replaces...
THanks for your help.
I have soem questions to make it better. I woud like to ptu a system that verifies that whe nppl apply for the tournament, there isn't already a player with their name in the ladder.
THis is the script that insert the datas:

$points = $_POST['points'];
$eso = $_POST['eso'];
mysql_connect("localhost", "ara_ara", "gohabsgo") or die(mysql_error());
mysql_select_db("ara_tournaments") or die(mysql_error());
mysql_query("INSERT INTO wrwrtc
(points, eso) VALUES('$points', '$eso' ) ")
or die(mysql_error());
echo "You have been succesfully added in the Warfaring Warriors Training Camp tournament";
?>

 

[easykey]

do a sql select query:

$sql="SELECT COUNT(*) AS count
		FROM wrwrtc
		WHERE eso='$eso'";
$result=mysql_query($sql);
$row=mysql_fetch_assoc($result);

if ($row['count']>0){
   //code for handling existing user
   }
else {
   //add the new user
   }

As far as $point not updating, your code $point=$newp+points is not right. This might be a typo but at this stage there is no $point so this expression will only ever equal $newp.

You will have to extract the data using a SELECT statement, add $newp, then UPDATE the table again. There may be a SQL way of doing this but I am not a SQL expert...

 

[Adam!]

<?php
$user = $_POST['user'];
$newp = $_POST['newp'];
$point = $newp + points;
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
mysql_query("UPDATE wrwrtc SET points=$point WHERE eso='$user'")
or die(mysql_error());
echo "The player got succesfully his points. <a href='home.php'>Go back</a>";
?>

Try that. It's something with that WHERE clause. With the eso=$user

 

[Aravinthan]

Thansk guys, I done it.
I was supposed to call the old points by using select then save it into a variables. the add then update! THanks

 

[worldwise001]

What I was doing WAS enclosing the data in quotes... just double quotes instead of single ones. I thought of putting it in the query itself, but it would have looked messy.