correct syntax near WHERE id=

[aaro]

hi i have downloaded this as source code and tried with my limited abilities to make it a <textarea> instead of <input type text. everything seems to work until the final update page update_ac.php when i submit i get the error..... 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 id='2'' at line 2

if anyone can see what im doing wrong it would stop me wanting to punch myself in the head :)

// update.php

<?php
$host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name  WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>

<form name="form1" method="post" action="update_ac.php">

Villaname <textarea name="villaname" rows="1" cols="40" wrap="virtual"   id="villaname" <? echo $rows['villaname']; ?></textarea>
<br>
Location <textarea name="location" rows="1" cols="40"  wrap="virtual" id="location" <? echo $rows['location']; ?></textarea>
<br>
Details <textarea name = "details" rows ="20" cols="140" wrap="virtual" id="details"><? echo $rows['details']; ?></textarea>
<br>
Bedrooms<textarea name="bedrooms" rows="1" cols="40"  wrap="virtual" id="bedrooms" ><? echo $rows['bedrooms']; ?></textarea>
<br>
Bathrooms<textarea name="bathrooms" rows="1" cols="40"  wrap="virtual" id="bathrooms"><? echo $rows['bathrooms']; ?></textarea>
<br>
housekeeping <textarea name="housekeeping" rows ="20" cols="140" wrap="virtual"  id="housekeeping"><? echo $rows['housekeeping']; ?></textarea>
<br>
transfers<textarea name="transfers"  rows ="20" cols="140" wrap="virtual"type="textarea"  id="transfers"><? echo $rows['transfers']; ?></textarea>
<br>
additional details <textarea name="additionaldetails" rows ="20" cols="140" wrap="virtual"  id="additionaldetails"><? echo $rows['additionaldetails']; ?></textarea>
<br>
<input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
<input type="submit" name="Submit" value="Submit">

</form>

<?

// close connection 
mysql_close();

?>

and the second page
update_ac.php

<?php
$host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$villaname=$_POST['villaname'];
$location=$_POST['location'];
$details=$_POST['details'];
$bedrooms=$_POST['bedrooms'];
$bathrooms=$_POST['bathrooms'];
$housekeeping=$_POST['housekeeping'];
$transfers=$_POST['transfers'];
$additionaldetails=$_POST['additionaldetails'];
$id=$_POST['id'];
// update data in mysql database 
$sql="UPDATE $tbl_name SET villaname='$villaname', location='$location', details='$details',bedrooms='$bedrooms', bathrooms='$bathrooms', housekeeping='$housekeeping',
 transfers='$transfers', additionaldetails='$additionaldetails',  WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
die (mysql_error());
}
?>

[mikeflash]

at the end of the second file, replace this:

die(mysql_error());

with:

die(mysql_error().' <br />SQL statement: '.$sql);

That way you could review the sql statement that's going through the mysql_query function and see where the error is.

[aaro]

hey mike
tried what you suggested it seems to be this statement

$sql="UPDATE $tbl_name SET villaname='$villaname', location='$location', details='$details',bedrooms='$bedrooms', bathrooms='$bathrooms', housekeeping='$housekeeping',
transfers='$transfers', additionaldetails='$additionaldetails', WHERE id='$id'";

im on server version 5.0.91

[mikeflash]

But I need to see the actual SQL query, not the code, so we can check where the error is at runtime.

[mikeflash]

OK I see the error now.

before the WHERE, there should be no "comma" (,).

Your statement looks like this:

$sql="UPDATE $tbl_name SET villaname='$villaname', location='$location', details='$details',bedrooms='$bedrooms', bathrooms='$bathrooms', housekeeping='$housekeeping',
transfers='$transfers', additionaldetails='$additionaldetails'-->,<-- WHERE id='$id'";

The problem with that query is the comma before the WHERE statement. Just remove it from your code and you should be good to go.

[aaro]

hi mike funny i found the answer from one of the other posts and removed the offending comma and it worked .
now the problem im having is that i have html in the text stored in the database , is this why i should remove strip slashes ? is there a free richtext editor that is easy to install ?.