-2

love this site, I have been learning mysql and php to help me design and iPhone Messaging client (for recreational purpose, and not publishing to app store)

I am having an issue, trying to update a specific item in a table, basically I am trying to let the message be marked as read once the user reads the message, however have been getting the error

Error Message:

Could not update data: 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 'read='MAYBE' WHERE id='19'' at line 1

Here is the URL EXAMPLE I am sending the GET from. this should find row/id # 19 and mark READ as YES, so it has been read, previously it was set to NO.

 http://myawesomesite.com/markasread.php?idnumber=19&readmessage=MAYBE

Here is my code:

<?php
$useridnumber = $_GET['idnumber'];
$didread = $_GET['readmessage'];

$conn = mysql_connect("sqlurl.com","sqllogin","sqlpassword");
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydatabase');
$sql = ("UPDATE messages SET read='$didread' WHERE id='$useridnumber'");


$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>

any help or clarification would be amazing, I understand having my login information on the same php and sql injections, but figured since this app is dedicated to only two people for private chatting that I would not need to really address any security concerns at this point.... :-)

Knitsu
  • 84
  • 1
  • 1
  • 7
  • Is `$useridnumber` a string or a number. I would suspect it is a number in which case you don't need the quotes around it. – War10ck Apr 03 '13 at 16:49
  • 4
    Why do you have parentheses around your sql statement? – j08691 Apr 03 '13 at 16:50
  • What type of data is column `read`? – Pitchinnate Apr 03 '13 at 16:53
  • 4
    On another note, please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Instead you should learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you pick PDO [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). – War10ck Apr 03 '13 at 16:53
  • 5
    read is a [reserved keyword](http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html), use `\`read\`` – Esailija Apr 03 '13 at 16:53

1 Answers1

4

read is a reserved word in mysql - docs: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You will need to escape it using backticks - `. also, remove the parentheses () around the $sql as they are not necessary.

$sql = "UPDATE `messages` SET `read`='$didread' WHERE `id`='$useridnumber'";
Sean
  • 12,443
  • 3
  • 29
  • 47