You have an error in your SQL syntax; check

Question

I keep getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username = 'username' VALUES ('value')' at line 1. The code is supposed to take the value that the logged in user enters into the form, and then insert that value into the money column of the table.

 <?php include("auth.php");?>

 <?php

 if(isset($_POST['submit']))
 {


 $getmoney = @mysql_query("INSERT INTO players (money) WHERE username = 
'".$_SESSION['username']."' VALUES ('$_POST[amount]')")
 or die("Error: ".mysql_error());

echo '
<div style="
  top: 395;
  left: 99;
  position: absolute;
  z-index: 1;
  visibility: show;">
 Money Received.
 </div>
 ';
  }
  ?>

  </head>
  <body>
  <p>Bank</p>
  Enter amount of money to recieve.<br>
  <form action="bank.php" method="post">
  <table border=2>
  <tr>
  <td>Amount to Receive:</td><td><input type="text" name="amount" size="20px"></input>    
  </td>
  </tr>
  </table>
  <input type="submit" name="submit" value="Get Money"></input>
  </form><br><br>
<hr size=2>
<?php include("footer.php");?>
  </body>
  </html>

Since it's a "bank" I think he needs UPDATE, but to increase the current amount of "money". — Novak, Jul 21 '12 at 06:56

score 3 · Accepted Answer · edited May 23 '17 at 10:09

3

The query syntax is wrong.

@mysql_query("INSERT INTO players (money) VALUES (".$_POST[amount].") WHERE username = '".$_SESSION['username']."'";)

Syntax

INSERT INTO table_name(field1, field2...) VALUES (value1, value2,...) WHERE CONDITION

EDIT: As you want to update the value, (Update Query)

@mysql_query("UPDATE players SET money = money+".$_POST[amount]." WHERE username = '".$_SESSION['username']."'";)

Also, your query is susceptible to SQL Injection, so you might want to use PDO/MySQLi or al the very least, call mysql_real_escape_string() on all values being passed by the user. Read this.

edited May 23 '17 at 10:09

Community

1
1

answered Jul 21 '12 at 06:55

Anirudh Ramanathan

46,179
22
132
191

Thanks this worked. Something I noticed though is that it replaces the previous value. Is there anyway to make it so that it adds onto the existing value, maybe using update instead of insert? – Sunden Jul 21 '12 at 07:01
The update query still sets the value of the column to whatever the user inputs. I tried to add 50 to 1000 and just ended up with 50. Thank you for all of the help. – Sunden Jul 21 '12 at 07:16
+1 for including the security concerns, and PDO. But the query should be "UPDATE ... SET money=money+".$_POST[... etc – cegfault Jul 21 '12 at 07:16
@Sunden `@mysql_query("UPDATE players SET money = money+".$_POST[amount]." WHERE username = '".$_SESSION['username']."'";)` – Anirudh Ramanathan Jul 21 '12 at 07:18
This worked great and I'll look into the mysql_real_escape_string(). I'm very new to PHP so thanks all for the help. – Sunden Jul 21 '12 at 20:12

score 1 · Answer 2 · answered Jul 21 '12 at 06:59

You are trying to make this SQL call:

"INSERT INTO players (money) WHERE username = 
'".$_SESSION['username']."' VALUES ('$_POST[amount]')"

Which is not a valid MySQL query. AND it's bad PHP. You probably meant to do this:

"INSERT INTO players(username,money) VALUES
    ('".$_SESSION['username']."','".$_POST['amount']."')"

And, actually, that's not secure, so you probably want the PHP to look something like this:

<?php
    $usr = mysql_real_escape_string($_SESSION['username']);
    $amt = mysql_real_escape_string($_POST['amount']);
    $sql = "INSERT INTO players(username,money) VALUES ('$usr','$amt')";
    $getmoney = @mysql_query($sql);

But, of course, I am assuming you want to do an INSERT and not an UPDATE.

You have an error in your SQL syntax; check

2 Answers2