whats wrong in this query?

[feelay]

Hey!

 

I have a question. Whats wrong with the following query? I ran it in phpmyadmin, and this is what i get:

 

SQL query:

 

UPDATE tbl_name SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp

 

MySQL said: 

 

#1064 - 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 temphealth < maxhp' at line 1

 

Any Idea?

Thanks for any help.

 

//Feelay

[Caesar]

Can you paste your query exactly as you have it in your PHP code? Also...please use the code tags.

 

Thaniks.

[feelay]

<?php
mysql_query("UPDATE tbl_name SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp");
?>

[NL_Rosko]

i guess the statement is missing the END...

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

 

[feelay]

That wasn't the error =(

 

This is how the code now looks, but still, it isn't working =/

 

<?php
mysql_query("UPDATE tbl_name SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp END CASE");
?>

[NL_Rosko]

That wasn't the error =(

 

This is how the code now looks, but still, it isn't working =/

 

<?php
mysql_query("UPDATE tbl_name SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp END CASE");
?>

 

did you try basic troubleshooting ? Is case supported under mysql 5.0 ?

where does the value maxhp come from, what is the table layout (int, varchar etc..)

try pasting the sql in phpmyadmin sql window and test/try with fixed values.

echo sql to see the values and paste in sql window

 

 

 

[feelay]

I've tryed to echo the query (echo query or error). I got the error that i wrote in first post.

maxhp is the maximum hp that a character level can have. both maxhp and temphealth is int 11.

and yes. CASE is supported by mysql 5

[NL_Rosko]

 

UPDATE tbl_name SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp END CASE WHERE temphealth < maxhp

 

[feelay]

still not working =/

[NL_Rosko]

still not working =/

 

i think the problem is in the values, your statement looks ok

try to echo the sql in your code like this

 

$sql = "";

$res = mysql_query($sql);

echo $sql;

 

past in an sql window if available and run, often gives better error handling

post output complete

 

 

 

[feelay]

is this what you mean?:

 

Result:

 

#1064 - 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 'CASE WHERE temphealth > maxhp' at line 1 

[NL_Rosko]

is this what you mean?:

 

Result:

 

#1064 - 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 'CASE WHERE temphealth > maxhp' at line 1 

 

no

run the php code (script), but echo the query as an string as output into your browser. this will show the values that stand for your variables.

under your statement echo/print the complete statement.

 

echo mysql_query("update ...);

 

[feelay]

no idea how this will help.. but here you go..

 

UPDATE tbl SET temphealth = CASE WHEN temphealth + 0.0833333333333 <= maxhp THEN temphealth + 0.0833333333333 ELSE maxhp END CASE WHERE temphealth > maxhp