无法通过变量减去表值

Question

I can not get an SQL update statement to subtract a variable from a table value. Here is my code:

$_SESSION_Job101=mysql_fetch_array(mysql_query("SELECT * FROM job_101 WHERE job_101.username='$_SESSION_User'"));

mysql_query("UPDATE characters SET currenergy=currenergy-$_SESSION_Job101['ecost'] WHERE username='$_SESSION_User'");

$_SESSION_Job101 is a perfectly valid result, as I pull from it on another page; I even pull the 'ecost' on said page. I also update currenergy this way in another script, except I use the number 1 instead of the variable. So I've narrowed it down to that variable.

It wouldn't matter that $_SESSION_Job101 is the result from a second table (job_101), and that query is updating to the table characters, would it?

Answer 1

We don't have enough information, but since you don't perform ANY error handling or validation that SQL resultset is returned, it could be an error caused by issues such as:

• no rows returned in first query
• some other parsing issue not directly evident

I would propose that you use temporary strings and echo the actual SQL queries. Continue by actually testing them with MYSQL (through workbench, queryviewer, or console) in order to see where and what the error is.

Also, it's not recommended to skip error checking and try to combine so many lines/steps into 2 lines. Imagine the first query does not return any results for example...

Debugging:

$query1 = "SELECT * FROM job_101 WHERE job_101.username='$_SESSION_User'";
echo $query1."<br/>";
$_SESSION_Job101=mysql_fetch_array(mysql_query($query1 ));
$query2 = "UPDATE characters SET currenergy=currenergy-$_SESSION_Job101['ecost'] WHERE username='$_SESSION_User'";
echo $query2."<br/>";
mysql_query($query2);

Update

Based on your comment I suggest you try the following two options:

1) Add a space between the - and $_SESSION_Job101['ecost'].

2) If that doesn't work, change your string to:

mysql_query("UPDATE characters SET currenergy=currenergy-".$_SESSION_Job101['ecost']." WHERE  username='".$_SESSION_User."'";`