[Resolved] UPDATE sql error.

[xarnet]

I am attemping to create a script to edit reviews in my database but when use the following:

$query = "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id='$id'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
echo "Review successfully edited";

I receive the following error:

Error in query: UPDATE animereview SET animeid='1', staffid='1', review='test', animation='10', plot='10', character='10', music='10', value='10', enjoyment='10', date='2003-06-02', WHERE id='1'. You have an error in your SQL syntax near 'character='10', music='10', value='10', enjoyment='10', date='2003-06-02', WHERE' at line 1

Any help resolving this issue would be much appreciated, also if you want my full code just let me know and I will post it.

[nnichols]

The error returned by mysql_error() is saying that you have a "," before the "WHERE" clause, though this does not appear to be the case in the code you posted above.

Is this an error in your posting?

[xarnet]

Sorry that was an older error message, I had played around with a few things since then. The current error would be:

Error in query: UPDATE animereview SET animeid='1', staffid='1', review='test', animation='10', plot='10', character='10', music='10', value='10', enjoyment='10', date='2003-06-02' WHERE id='1'. You have an error in your SQL syntax near 'character='10', music='10', value='10', enjoyment='10', date='2003-06-02' WHERE ' at line 1

[nnichols]

I can't see any glaring errors. Can you post the CREATE TABLE statement?

Strictly speaking the integers should not be quoted, but MySQL does not worry about that.

[xarnet]

I created the table using phpMyAdmin, but here is the table info:

field: id
type: int(11)
extra: auto_increment

field:animeid
type: int(11)

field:staffid
type: int(11)

field: review
type: text

field: animation
type: int(11)

field: plot
type: int(11)

field: character
type: int(11)

field: music
type: int(11)

field: value
type: int(11)

field: enjoyment
type: int(11)

field: date
type: date

[niroshan]

HI,
I think the reason for this error is you have put single quotes between id in the where clouse. Just remove them because your id is integer so you dont need single quotes.

Use your query like this,

$query = "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id=$id";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
echo "Review successfully edited";

Hope this would help.

regards,
Niroshan

[xarnet]

Unfortunately it's still not working.

Here is the full code that I am using, in case it may help out.

<?php
if (!$submit)
{
	$query = "SELECT * FROM animereview WHERE id = '$id'";
	$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

if (mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_object($result);

	if ($row->staffid = 1)
	{
?>
<table width="100%" cellpading="1" cellspacing="0" border="0" id="con">
<form action="<? echo $PHP_SELF; ?>" method="POST">
<input type="hidden" name="id"  value="<?php echo $id; ?>">
<input type="hidden" name="staffid"  value="1">
<input type="hidden" name="animeid"  value="<?php echo $row->animeid; ?>">
<input type="hidden" name="date"  value="<?php echo $row->date; ?>">
<tr>
<td width="25%" valign="top"><b>Review:</b></td>
<td width="75%"><textarea cols="50" rows="10" name="review"><? echo $row->review; ?></textarea></td>
</tr>
<tr>
<td width="25%"><b>Animation:</b></td>
<td width="75%"><input size="4" type="text" name="animation" value="<? echo $row->animation; ?>"></td>
</tr>
<tr>
<td width="25%"><b>Plot:</b></td>
<td width="75%"><input size="4" type="text" name="plot" value="<? echo $row->plot; ?>"></td>
</tr>
<tr>
<td width="25%"><b>Character:</b></td>
<td width="75%"><input size="4" type="text" name="character" value="<? echo $row->character; ?>"></td>
</tr>
<tr>
<td width="25%"><b>Music:</b></td>
<td width="75%"><input size="4" type="text" name="music" value="<? echo $row->music; ?>"></td>
</tr>
<tr>
<td width="25%"><b>Value:</b></td>
<td width="75%"><input size="4" type="text" name="value" value="<? echo $row->value; ?>"></td>
</tr>
<tr>
<td width="25%"><b>Enjoyment:</b></td>
<td width="75%"><input size="4" type="text" name="enjoyment" value="<? echo $row->enjoyment; ?>"></td>
</tr>
<tr>
<td width="25%"></td>
<td width="75%"><input type="Submit" name="submit" value="Edit Review"></td>
</tr>
</form>
</table>
<?
		}
		else
		{
			echo "You do not have permission to edit this review";
		}
	}
	else
	{
		echo "That review does not exist";
	}
}
else
{
	$query = "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id=$id";
	$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
	echo "Review successfully edited";
}
?>

Now on my site it loads the correct information, such as the review, the plot score, etc, but when it comes time to submit I receive the error already mentioned.

[xarnet]

The problem was occuring as a result of "character" being a reserved MySQL word and thus complications occured. I simply changed the column (field) name and it's all working now ^_

Thanks to everyone who tried to help, it's much appreciated.