Link to home
Start Free TrialLog in
Avatar of homeshopper
homeshopperFlag for United Kingdom of Great Britain and Northern Ireland

asked on

error in your SQL syntax

I am trying to update field 'html' in table 'tblPages' and get syntax error.
textbox contains:bbbb#This is a post#tempDefault#1#0#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 'a post,
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusm' at line 2
Thanks in advance for any help given.
The full code is listed below:
<div style="position:absolute;width:827px;height:537px;left:176px;top:63px;font-size:xx-small;overflow:auto;border:1px solid #722F90;" id='DIV144'>
<?php 
error_reporting(E_ALL); 
//ini_set('display_errors', true);
if (isset($_POST['submit']))
{
echo "<pre>";
var_dump($_POST);
echo "</pre>";
}
?>
<table style="font-size: xx-small;">
<tr>
<?php
$inc = '1';
$connection = mysql_connect('localhost', 'root', 'xxxxx');
mysql_select_db('AdminDB', $connection);
$sql = "SELECT * FROM tblPages WHERE id = $inc";
$result = mysql_query($sql) or die(' Query failed. ' .mysql_error());
while($row = mysql_fetch_array($result))
      {
        $h1 = 'Edit html';
        $id = $row['id'];
        $creator = $row['creatorId'];
        $txt = $row['title'];
        $content = $row['content'];
        $htm = $row['html'];
        $lnk = $row['link'];
        $dateUp = $row['dateUpdated'];
        $dateAdd = $row['dateAdded'];
?>
<td><?php echo $h1?></td>
<td><?php echo $txt?></td>
<td><a href="<?php echo $lnk.$creator.'.php?id='.$id ?>" target="_self">read more...</a></td>
<?php
        }
?>
</tr>
</table>
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="get">
<input type="submit" name="submit" value="Go!" class="Button" />
<input type="text" name="textbox" style="height:216px; width: 764px; font-size:xx-small; " value="<?php echo $htm ?>" /><br />
<?php
  if (isset($_GET["submit"])) 
  {
    $htm =  $_GET["textbox"];
    echo "textbox contains:". $htm;
    echo '#'.$txt.'#'.$lnk.'#'.$id.'#'.$creator.'#';
mysql_query("INSERT INTO tblPages (id,creatorId,title,content,html,link,dateUpdated,dateAdded) 
VALUES($id,$creator,$txt,$content,$htm,$lnk,$dateUp,$dateAdd ) ") or die(mysql_error());  
echo "Data Inserted!";
  }
?>
</form>
</div>

Open in new window

see table create code below:
CREATE TABLE tblPages (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
creatorId INT UNSIGNED NOT NULL,
title VARCHAR(100) NOT NULL,
content TEXT NOT NULL,
html Text NOT NULL,
link Text NOT NULL,
dateUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dateAdded TIMESTAMP NOT NULL,
PRIMARY KEY (id),
INDEX (creatorId),
INDEX (dateUpdated)
);
INSERT INTO tblPages VALUES
(NULL, 0, 'This is a post', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>','<html>','tempDefault', NULL, NOW()),
(NULL, 1, 'This is another post', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', '<html>','tempDefault',NULL, NOW() + 1000000), 
(NULL, 2, 'This is the third post', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', '<html>','tempDefault',NULL, NOW() + 2000000);

Open in new window

Avatar of Marco Gasi
Marco Gasi
Flag of Spain image

Try adding quotes:

mysql_query("INSERT INTO tblPages (id,creatorId,title,content,html,link,dateUpdated,dateAdded)
VALUES('$id','$creator','$txt','$content','$htm','$lnk','$dateUp','$dateAdd' ) ") or die(mysql_error());  
echo "Data Inserted!";

Cheers
Avatar of homeshopper

ASKER

Thanks, your suggestion works, but still get one further error:
Duplicate entry '1' for key 'PRIMARY'
Thanks in advance for help given.
ASKER CERTIFIED SOLUTION
Avatar of Marco Gasi
Marco Gasi
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, the insert works.
now trying an update, but get following:
syntax to use near '(id,creatorId,title,content,html,link,dateUpdated,dateAdded) VALUES(NULL,'0','' at line 1
mysql_query("UPDATE tblPages (id,creatorId,title,content,html,link,dateUpdated,dateAdded)
VALUES(NULL,'$creator','$txt','$content','$htm','$lnk','$dateUp','$dateAdd' ) ") or die(mysql_error());  
echo "Data updated!";

I also tried ("UPDATE tblPages WHERE id = $inc (id,creatorId
When you insert a new record you have to set id (which is an auto-increment field) to null since mysql knows yet what to do. But when you update an existing record you should only use field yopu are updating, sure not the id field:

mysql_query("UPDATE tblPages (creatorId,title,content,html,link,dateUpdated,dateAdded)
VALUES('$creator','$txt','$content','$htm','$lnk','$dateUp','$dateAdd' ) ") or die(mysql_error());  
echo "Data updated!"

You can learn a lot about php and mysql reading this book: http://www.sitepoint.com/books/phpmysql5/

Cheers
Thank you for your suggestions.
I downloaded the book and code examples from http://www.safaribooksonline.com/
It will be usefull for other queries I have in the future.
However, the update query there uses PDO.
I have found the following code that now works, elsewhere on 'ee'.

INSERT query:
$sql =  "INSERT INTO tblPages (id,creatorId,title,content,html,link,dateUpdated,dateAdded) 
VALUES(NULL,'$creator','$txt','$content','$htm','$lnk','$dateUp','$dateAdd' ) ";
mysql_query($sql)  or die(mysql_error());  
echo "Data Inserted!";

Open in new window

UPDATE query:
$sql =  "UPDATE tblPages SET html='$htm' WHERE id='$id' ";
mysql_query($sql)  or die(mysql_error());  
echo "Data updated!";

Open in new window

Thanks again for the help given.
The INSERT query you say having found somewhere in EE is the same I suggested you here: https://www.experts-exchange.com/questions/27934376/error-in-your-SQL-syntax.html?anchorAnswerId=38595325#a38595325, which is the answer to your original question.
I think you should award points to the answer to your original question, don't you?
Yes, your suggestions for Insert works, I'll award full points.
For clarity, the update I found else where and adapted it for my needs.
Thank you for the help given.
Oh, I didn't understand that. Good luck with your project :-)
With INSERT queries, your script typically names the columns that are to be inserted.  Any column names that are omitted will be given their default values.  This includes AUTO_INCREMENT keys, etc.  You do not need to list the column name or value if the default is acceptable (and it should be acceptable if the DB table is designed thoughtfully).

You may also want to learn about this function before something happens that destroys your data base!
http://php.net/manual/en/function.mysql-real-escape-string.php
Thank you Ray for your suggestion, I'll keep that in mind.