[SOLVED] Could not update page: You have an error in your SQL syntax;

[ashrobbins87]

I get this error on my page at run time: 

 

Could not update page: 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 name = 'body'' at line 3

 

The page is designed to pull data from MySQL database and display it in a text field in which the user is able to edit the text. Then should then be able to press update and pass the new data back to the database.

 

Code is below, I cant work it out because I'm certain that the query is correct.

<?php
$mysqli = mysqli_connect("localhost", "root", "pass", "opp_group");
  
echo" <form method=\"post\" action=\"".$_SERVER["PHP_SELF"]."\">
      <textarea name=\"body\" cols=\"50\" rows=\"10\">";

if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
else
{	
$getTextHome_sql = "SELECT * FROM home_content WHERE name= 'body'";
$getTextHome_res = mysqli_query($mysqli, $getTextHome_sql) or die (mysqli_error($mysqli));

if ($getTextHome_res) 
{
	while ($newArray = mysqli_fetch_array($getTextHome_res, MYSQLI_ASSOC)) 
	{
		$body = $newArray["content"];
		echo $body;
		$body = $_POST["content"];
	}
} 
else 
{
		printf("Could not retreive records: %s\n", mysqli_error($mysqli));
}

mysqli_free_result($getTextHome_res);
mysqli_close($mysqli);
}	?>

  <?php 
  
  $mysqli = mysqli_connect("localhost", "root", "pass", "opp_group");

  echo "</textarea>
              </td>
    <td align=\"left\" colspan=\"3\"><img src=\"../../Images/homebodychange.jpg\" width=\"350\" height=\"250\" /></td>
  </tr>
  <tr><td><input type=\"submit\" name=\"update\" value=\"Update\" />";
if (mysqli_connect_errno())
{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}
else
{
	$updateTextHome_sql = "INSERT INTO home_content (content)
							VALUES ('".$_POST["content"]."')
							WHERE name = 'body'";

	$updateTextHome_res = mysqli_query($mysqli, $updateTextHome_sql);

	if ($updateTextHome_res === TRUE)
	{
		echo "Page Updated.";
	}
	else
	{
		printf("Could not update page: %s\n", mysqli_error($mysqli));
	}
	mysqli_close($mysqli);
}
  
  echo"</td></tr>
</table></form>";
?>

 

 

[dennismonsewicz]

make sure that there is an entry in your DB that contains body

[ashrobbins87]

yeah the DB has a table with id, name and content, and the name atrribute of this is body. So i'm not sure why its falling over....

[premiso]

You cannot use WHERE in an insert statement (without a sql select statement that is).

 

MySQL Insert INTO

[dennismonsewicz]

yeah what he said

 

Man I can't believe I didn't catch that... its been a LONG weekend

[ashrobbins87]

How do you get around it then? Can you use UPDATE instead?

[ashrobbins87]

I have changed it to be an UPDATE statement, and now the DB gets populated with nothing even when I enter text into the box. Why does it do this?

[dennismonsewicz]

echo out your sql statement to see whats going on and make sure you are using the right syntax for update

[ashrobbins87]

I think it could be caused because I have set the value of $body inside a loop, and perhaps it is not visible outside of this loop. How do I make it visible to the whole script even though it's inside the loop?

[dennismonsewicz]

echo out $updateTextHome_res and post what you get

[ashrobbins87]

It prints the data with a 1 on the end. For example the actual data is "sample text" and it is printing "sample text1"

[dennismonsewicz]

what does your update command look like?

[ashrobbins87]

$updateTextHome_sql = "UPDATE home_content
				SET content='".$body."'
				WHERE name = 'body'";

 

The $body variable is what populates the field in the first place, so when it gets changed do I need to use a different variable?

[ashrobbins87]

Anyone??