INSERT/UPDATE syntax error

[bigbikkuri]

I'm completely at my wits end.

I have a dropdown menu form:

<form method="POST" action="moviescoreinput.php">
<select name="usermoviescore">
<option value="0" selected="selected">0</option>
<option value="1">1 - Terrible</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5 - Average</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10 - Amazing</option>
</select>
<input type="submit" value="Get Club L Point">
</form>

That code with the following SQL query:

if(mysql_result($result,0,0)>0)
{
echo "HERE 4.1";
$sql = "UPDATE usermoviedata
SET moviescore = $moviescore, moviescorepoint = 1
WHERE username ='$user' AND movie ='$movie'";
mysql_query($sql) or die(mysql_error());
}
else
{
echo "HERE 4.2";
$sql = sprintf ( "INSERT INTO usermoviedata
(username, movie, moviescore, moviescorepoint) VALUES
('%s','%s','%s','%s')",
mysql_real_escape_string($_SESSION['name']),
mysql_real_escape_string($_SESSION['movie']),
mysql_real_escape_string($moviescore),
mysql_real_escape_string(1)
);
mysql_query($sql) or die(mysql_error());
}

And that checks out all fine and rockin.

However, now I have a textarea:

<form method="POST" action="moviescorewhyinput.php">
<textarea rows="10" cols="40" wrap="virtual"></textarea>
<input type="submit" value="Get Club L Point">
</form>

That has code with THIS SQL statement:

if(mysql_result($result,0,0)>0)
{
echo "HERE 4.1";
var_dump($user);
var_dump($movie);
$sql = "UPDATE usermoviedata
SET scorewhy = $moviescorewhy, scorewhypoint = 1
WHERE username ='$user' AND movie ='$movie'";
mysql_query($sql) or die(mysql_error());
}
else
{
echo "HERE 4.2";
$sql = sprintf ( "INSERT INTO usermoviedata
(username, movie, scorewhy, scorewhypoint) VALUES
('%s','%s','%s','%s')",
mysql_real_escape_string($_SESSION['name']),
mysql_real_escape_string($_SESSION['movie']),
mysql_real_escape_string($moviescorewhy),
mysql_real_escape_string(1)
);
mysql_query($sql) or die(mysql_error());
}


And it gives me a syntax error. The output of the php file is as follows:
'HERE 0HERE 1HERE 2HERE 3string(20) "my@email.com" string(23) "Raiders of the Lost Ark" HERE 4.1string(20) "my@email.com" string(23) "Raiders of the Lost Ark" 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 ' scorewhypoint = 1 WHERE username ='my@email.com' A' - but it is exactly the same as the dropdown query less a name change. The database designtable is as follows:
col-type-size:
username-varchar-50
movie-varchar-100
moviescore-varchar-2
moviescorepoint-int-1
scorewhy-varchar-100
scorewhypoint-int-1
scene-varchar-100
scenepoint-int-1
scenewhy-varchar-100
scenewhypoint-int-1

Any help in the matter would be SO appreciated. I've been banging my head against the wall for 2 hours already...:(

[guelphdad]

echo out the value for $sql and see what string you are trying to insert.

[darkmaster]

Try putting single quotes ' ' round the database field, like below

SET scorewhy = '$moviescorewhy'

[guelphdad]

darkmaster, you mean the database value. the field is the name of the field or column and you wouldn't want quotes around it or you would generate an error message for sure. :)

[bigbikkuri]

Try putting single quotes ' ' round the database field, like below

SET scorewhy = '$moviescorewhy'

Uhm....that worked. I feel a little silly. Can I ask why?
Because the content of $moviescorewhy is from the $_POST and is sent as a string, $moviescorewhy is already a string so why would I need to cast it as a string AGAIN by putting single quotes around it? I've var_dump()'ed $moviescorewhy and it is most definitely a string, with double-quotes and everything.

But now I have a new problem:
If inside the variable $moviescorewhy the user uses punctuation I get an error because single quotes in the string, say, "Because I'd feel the same way" would register as an error because of the single quotes it seems.
I say this because if I input "die." as the string it checks out fine, but if I input "I'd die." it breaks. I would have thought that once in double quotes it would be fine?

[GJay]

mysql and php are connected, but separate. Strings going into mysql need to be inside single quotes, and any single quotes inside such a string need to be escaped.
myql_real_escape_string() exists for just that reason.

[bigbikkuri]

Thanks GJay, I wound up just using mysql_escape_string in the end, but was curious if there was a way to do it casting via double quotes. It just goes to show, being lazy and trying shortcuts just screws things up :)

Thanks again for your help!

[GJay]

mysql_escape_string is deprecated in favour of mysql_real_escape_string.
The difference is down to charsets, which while it might not matter now, it's probably worth using the supported function ready for the day the deprecated one is taken out.

[bigbikkuri]

mysql_escape_string is deprecated in favour of mysql_real_escape_string.
The difference is down to charsets, which while it might not matter now, it's probably worth using the supported function ready for the day the deprecated one is taken out.

Oh, I did use mysql_real_escape_string() - I just forgot to type the real in the above post.