Mysql update syntax error

siopaeile · December 11, 2009

trying to get this update query to work

mysql_query("UPDATE records SET note = '$note' WHERE id = '$ID1'",$dsn) or die ("MySQL Error didn't update" . mysql_error() );

here's the error message:

MySQL Error didn't updateYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00',

PRIMARY KEY (id)

) TYPE=MyISAM;

Any ideas?

siopaeile · December 11, 2009

sorry just read the rules: inlcuding further info here on how the table is set up

CREATE TABLE `records` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Title` varchar(150) NOT NULL,
`CatID` int(11) DEFAULT NULL,
`Note` text,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

I have tried messing about with the quotation marks. Pretty new to Mysql so not sure what else I can do. The update statment above should update the note field for the currently selected ID.

PFMaBiSmAd · December 11, 2009

Based on the part of the query being printed in the error message, either $note or $ID1 contains part of your CREATE TABLE query statement. It would take seeing your actual code up to that point to be able to help with what it is doing wrong.

siopaeile · December 11, 2009

thanks.... here is the preceding code

    include_once "base.php"; 
    if(!empty($_POST['note'])){
$note = $_POST['note'];
$ID1 = $_POST['ID'];

'note' comes from a wysywig object called xstandard. 'ID' is from a hidden input box in the same form as the wysywig editor.

base.php contains the database connection and selected table. This works fine as I've tested it on other sql queries.

here's the whole code if you need it:

<?php 
    include_once "base.php"; 
    if(!empty($_POST['note'])){
$note = $_POST['note'];
$ID1 = $_POST['ID'];
mysql_query("UPDATE records SET note = '$note' WHERE id = '$ID1'",$dsn) or die ("MySQL Error didn't update" . mysql_error() );

}


// Build SQL query to find Total Number of Records

$sql = "SELECT ID ";
$sql .= "FROM records ";

// Read in Total Number of Records

$dbResult = mysql_query($sql, $dsn) or die ("MySQL Error: " . mysql_error() );   
$totalRecords = mysql_num_rows($dbResult);

// Calculate Number of Pages Required

$recordsPerPage = 1;
$totalPageNumber = ceil($totalRecords / $recordsPerPage);

// Check for Page Number

if(strlen($_GET['page']) < 1){ 
$currentPage = 1;
} else {
$currentPage = $_GET['page'];
}

// Create Offset Number

$offset = ($currentPage * $recordsPerPage) - $recordsPerPage;

//read data

$dbResult = mysql_query("select note, title, ID from records LIMIT $offset,$recordsPerPage",$dsn) or die ("MySQL Error: " . mysql_error() );

$numRecords = mysql_num_rows($dbResult);
$recordset = "";
//for($i=0;$i<$numRecords;$i++){
// $recordset[] = mysql_fetch_array($dbResult);
//$content = $recordset['note'];
while ($row = mysql_fetch_array($dbResult))
{
        $content = $row["note"];
        $ID = $row["ID"];
}   


   
mysql_close($dsn);

            
         
    
?> 








<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
	<title>XStandard XHTML Strict / 1.1 Editor</title>
	<script type="text/javascript">
	//<![CDATA[
		function myOnSubmitEventHandler() {
			try {
				if(typeof(document.getElementById('editor1').EscapeUnicode) == 'undefined') {
					throw "Error"
				} else {
                                                
					document.getElementById('editor1').EscapeUnicode = true;
					document.getElementById('note').value = document.getElementById('editor1').value;
				}			
			}
			catch(er) {
				document.getElementById('note').value = document.getElementById('alternate1').value;
			}
		}
	//]]>
	</script>



<!-- CSS switch link -->

<SCRIPT type ="text/JavaScript">

function makeDefault()
{

document.getElementById('editor1').css.EscapeUnicode = "http://localhost.localdomain/styles/default.css";

}
</SCRIPT>




</head>

<body>
	<h1>XStandard XHTML Strict / 1.1 Editor</h1>
	<p>This page will post the data from the editor to itself (this page) and load the received data into the editor.  When the page reloads after you click the Submit button, the data in the editor should look exactly the same as before you clicked the Submit button.</p>

	<form action="xstandardread.php" method="post" onsubmit="myOnSubmitEventHandler()">
		<p>
			<object type="application/x-xstandard" id="editor1" width="100%" height="380">
				<param name="Value" value="<?php echo htmlspecialchars($content, ENT_COMPAT) ?>" />
                                        <param name="CSS" value="http://localhost.localdomain/styles/paul.css" />
				<textarea name="alternate1" id="alternate1" cols="60" rows="15"><?php echo htmlspecialchars($content, ENT_COMPAT) ?></textarea>
			</object>
		</p>

<a href="#" 
onclick="makeDefault()">change style to paul</a>


<?php
// Create Dynamic Navigation Bar
$pageURL = $_SERVER['SCRIPT_NAME'];

$html = "";
if($currentPage > 1){
// Create Previous Page Link (<<)
$url = $pageURL . "?page=" . ($currentPage - 1);
$html .= "<a href='" . $url . "'> << </a>";
$html .= " ";
}
echo $currentPage;
if($currentPage < $totalPageNumber){
// Create Next Page Link (>>)
$url = $pageURL . "?page=" . ($currentPage + 1);
$html .= "<a href='" . $url . "'> >> </a>";
$html .= " ";
echo $html;
}
?>

		<p>	
			 <input type="hidden" name="ID" id="ID" value="<?php echo htmlspecialchars($ID, ENT_COMPAT) ?>" />


                                 <input type="hidden" name="note" id="note" value="" />

			<input type="submit" id="btnAction" name="btnAction" value="update" />
		</p>

	</form>
</body>
</html>

siopaeile · December 11, 2009

Based on the part of the query being printed in the error message, either $note or $ID1 contains part of your CREATE TABLE query statement. It would take seeing your actual code up to that point to be able to help with what it is doing wrong.

Ok, now I get you. Yes, I have much of the code, I used to create the tool saved as a note and this includes lots of sql code. Once I get rid of the sql code from the wyswig container and hense $note, the update query works. Oddly, the insert query works fine, even with the sql code in the note.

This is a notetaking tool and I want to be able to update all sorts of code in it. Is there anyway of cloaking the code, so that I don't run into these problems? thanks again

siopaeile · December 12, 2009

Found the solution, I think.

$note = addslashes($_POST["note"]);

string addslashes ( string $str )

Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

http://php.net/manual/en/function.addslashes.php

Sign In

Mysql update syntax error

Recommended Posts

siopaeile

Link to comment

Share on other sites

siopaeile

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

siopaeile

Link to comment

Share on other sites

siopaeile

Link to comment

Share on other sites

siopaeile

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information