[SOLVED] Help with deleting from database, "SQL sytax error"

July 9, 2007

Hey,

I searched the forums and I couldn't come up with anything, so I'm sorry if this has been solved before. But my problem is that when I want to delete a comment from the database, it just says, "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 '' at line 1". At line one I have a Session_start...and I doubt that would interfere with deleting.

Here's my query:

$com = $_GET['del'];
if ($com != NULL && is_numeric($com)) {
  if ($ip == $okip) {
mysql_query("DELETE FROM `comments` WHERE `id` = '".$com."' LIMIT 1 ");
echo "Comment has been deleted.<META HTTP-EQUIV=\"REFRESH\" CONTENT=\"2; URL=show.php\"> ";
}
else {
echo "You do not have permission to delete comments."; }
}

Thank you for your time

~L

Lumio · July 9, 2007

put mysql_error(); after

mysql_query("DELETE FROM `comments` WHERE `id` = '".$com."' LIMIT 1 ");

and give us the result

per1os · July 9, 2007

make sure $com contains a value. check for www.php.net/empty

On a side not, post the form code that this gets submitted from. Make sure it is GET you should be using and that del should contain the comment id and is not just a verification hidden field.

soycharliente · July 9, 2007

Try putting the query string on it's own line and assigning it to a variable. It might be because you've separated the query and inserted other things.

clanstyles · July 9, 2007

here is what you want.

if(empty($com))
echo "This is empty.";
else
mysql_query("DELETE FROM `comments` WHERE `id`=$com LIMIT 1") or die(mysql_error());

July 9, 2007

Alright, the mysql_error...just gave me the same result...but when i check to see if it's empty it shows that it's empty....but isn't it supposed to be empty until i click the delete link so it can get the del value?

EDIT:

Also i just replaced the delete query and etc with

$com = $_GET['del'];
if(empty($com)) {
echo "This is empty.";
}
else {
echo "hello"; 
}

But I'm still getting a syntax error

Carterhost · July 9, 2007

Not sure how valid this is, but you could try:

<?php
mysql_query("DELETE FROM comments WHERE id = '$com' LIMIT 1") or die(mysql_error()); 
?>

That looks more right to me. Does it give the same error?

July 9, 2007

yah it does....and also if u read the edit above...im still getting the same error even when im not deleteing anything, which is quite odd

Carterhost · July 9, 2007

Okay, do you have any other SQL Queries in your code?

marcus · July 9, 2007

Do the smart thing and check if that ID even exists first.

per1os · July 9, 2007

Alright, the mysql_error...just gave me the same result...but when i check to see if it's empty it shows that it's empty....but isn't it supposed to be empty until i click the delete link so it can get the del value?

EDIT:

Also i just replaced the delete query and etc with
$com = $_GET['del'];
if(empty($com)) {
echo "This is empty.";
}
else {
echo "hello"; 
}
But I'm still getting a syntax error

<?php
$com = isset($_GET['del'])?$_GET['del']:NULL;
?>

If del has a value set it to that value else set it to null. Check for www.php.net/is_null

And your statement is true, it is suppose to be empty until the form is submitted. So you do NOT want to run the query if $_GET['del'] has NOT been set.

soycharliente · July 9, 2007

Is your table name actually 'comments' or did you reference a table that doesn't exist?

Did you show what mysql_error() output?

July 9, 2007

yah the table exits and that all works fine because it is displaying comments...

ohh...i was limiting myself to the delete query only, interesting...hehe.... ::)

after checking them the query with problems is this

$news = mysql_fetch_array(mysql_query("SELECT * FROM `news` WHERE `id`=$id ")) or die(mysql_error());

I don't see what the problem is with it though

EDIT: Yah the smart thing would be to check is the variables are even set and have data...but i already did that and it all checks out...the problem is with the above query....

per1os · July 9, 2007

$id must not have a value. The error is stating that `id`= after the = there is nothing there to check against it. Which is throwing the error off. If you want to see it clearer add this:

$news = mysql_fetch_array(mysql_query("SELECT * FROM `news` WHERE `id`=$id ORDER BY `id`")) or die(mysql_error());

You will notice the portion of where the order by is.

July 9, 2007

well after i do $id = $_GET['id']; and then echo it out it shows the correct id that is in the url...which means that it does have a value. and also the new query is giving me an 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 'ORDER BY `id`' at line 1"

Carterhost · July 9, 2007

 $news = mysql_fetch_array(mysql_query("SELECT * FROM news WHERE id='$id' ")) or die(mysql_error());

July 9, 2007

nothing is happening now...im not getting an error...but it's not deleting now...but when i get ride of that query(the whole news query line) comments can delete....i really don't see whats wrong here.

clanstyles · July 9, 2007

So what do you have exactly? Please post it from like empty on.

per1os · July 9, 2007

well after i do $id = $_GET['id']; and then echo it out it shows the correct id that is in the url...which means that it does have a value. and also the new query is giving me an 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 'ORDER BY `id`' at line 1"

Which means that $id does indeed not have a value. Make sure case is correct and something wouldn't be overriding it. Because I bet if you did this it would work:

$news = mysql_fetch_array(mysql_query("SELECT * FROM `news` WHERE `id`= 1 ORDER BY `id`")) or die(mysql_error());

IE: Post more code so we can see what might be the cause. (Like upto where you define $id)

clanstyles · July 9, 2007

well after i do $id = $_GET['id']; and then echo it out it shows the correct id that is in the url...which means that it does have a value. and also the new query is giving me an 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 'ORDER BY `id`' at line 1"

Which means that $id does indeed not have a value. Make sure case is correct and something wouldn't be overriding it. Because I bet if you did this it would work:
$news = mysql_fetch_array(mysql_query("SELECT * FROM `news` WHERE `id`= 1 ORDER BY `id`")) or die(mysql_error());
IE: Post more code so we can see what might be the cause. (Like upto where you define $id)

I don't think theres anything wrong with his mysql query. I belive its just something else like grabing the value its geting obstructed along the way some where. and empty should help but I bet he didn't put it in or something.

July 9, 2007

here is the code up to where i define $id

<?
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head><title>Infinite Vortex</title>
<meta http-equiv="content-type" content="text/html; charset=us-ascii" />
<link rel="stylesheet" type="text/css" href="/css/newmain.css" />
<link rel="stylesheet" type="text/css" href="/css/newupdate.css" />
<link rel="stylesheet" type="text/css" href="/css/inter.css" />
<script type="text/javascript" src="/javascript/extternal.js">
</script>
<script type="text/javascript" src="/javascript/updatepic.js">
</script>
<link rel="alternate" title="Infinite Vortex News Feed" href="/rss.xml" type="application/rss+xml" />
</head>


				<!-- Start Web -->
<body>
<div class="border">
					<!-- START Banner -->

				<!-- END Banner -->
<?php include("includes/listmenu.php"); ?>
<div class="left">
<!-- MAIN -->
<?PHP
include("database.php");
$userID = $_SESSION['userid'];
$username = $_SESSION['username'];
$query = mysql_query("SELECT * FROM `users` WHERE `userid`='$userID'")or die(mysql_error());
$row = mysql_fetch_array($query);

// Get News ID
$id = $_GET['id'];

EDIT:

I also did the empty on $id to echo " I am not empty" if not empty and it echos, "I am not empty

per1os · July 9, 2007

Where is the code after $_GET['id']; ??? We need to see the whole picture bud. (Up to I meant literally go up from that point...)

July 9, 2007

haha...my bad...its kinda long since its a news/comments script...and also i did a find on the script to see if it's value is being replaced, and i only used defined $id once...and that's with the GEt....but like u requested heres the rest of the code under $id=$GET['id'];

// Get the Actual News that matches the News ID
$news = mysql_fetch_array(mysql_query("SELECT * FROM `news` WHERE `id`=$id ")) or die(mysql_error());
// Get Comments for that specific News ID
$comid = mysql_query("SELECT * FROM `comments` WHERE `newsid`='".$news['id']."' ORDER BY `id` DESC LIMIT 0,50") or die(mysql_error());
// Display News Article
echo "<p class=\"breakoff\"><b>".$news['subject']."</b><br />By ".$news['user']." on ".$news['date']."<br /></p>".stripslashes($news['news'])."<br />";
?>
<?
// Set up an IP address that has access to delete comments
$ip = $_SERVER['REMOTE_ADDR'];
$okip = "24.15.10.41";

while ($view = mysql_fetch_array($comid)) {
$aquer = mysql_query("SELECT * FROM `users` WHERE `username`='".$view['user']."'")or die(mysql_error());
$avatar = mysql_fetch_array($aquer);
// Display all comments for artical, with Delete Comment Option if your IP matches the OK IP
if ($ip == $okip) {
echo "<br /><br /><table width=\"100%\" border=\"border\"><tr><td class=\"av\" valign=\"top\" width=\"125px\" length=\"125px\"><img src=\"profile/avatar/".$avatar['photo']."\" alt=\"".$avatar['photo']."\" /></td>".
"<td valign=\"top\"><p class=\"breakoff\">By <a href=\"account.php?user=".stripslashes($view['user'])."\">".stripslashes($view['user'])."</a> on ".$view['date']."</p><a href=\"show.php?del=".$view['id']."\">[X]</a><br />
<p class=\"news\">".stripslashes($view['comment'])."</p></td></tr></table>";
}

// If it doesn't match it, display comments without delete option
else {
echo "<br /><br /><table width=\"100%\" border=\"border\"><tr><td class=\"av\" valign=\"top\" width=\"125px\" length=\"125px\"><img src=\"profile/avatar/".$avatar['photo']."\" alt=\"Avatar\" /></td>".
"<td valign=\"top\"><p class=\"breakoff\">By <a href=\"cp.php?user=".stripslashes($view['user'])."\">".stripslashes($view['user'])."</a> on ".$view['date']."</p>
<p class=\"news\">".stripslashes($view['comment'])."</p></td></tr></table>";
}
}
?>
<?PHP
// Delete code
$com = $_GET['del'];
if ($com != NULL && is_numeric($com)) {
  if ($ip == $okip) {
mysql_query("DELETE FROM `comments` WHERE `id`=$com LIMIT 1") or die(mysql_error());  
echo "Comment has been deleted.<META HTTP-EQUIV=\"REFRESH\" CONTENT=\"2; URL=show.php\"> ";
}
else {
echo "You do not have permission to delete comments."; }
}

$user = addslashes(strip_tags($_POST['username']));
$contact = addslashes(strip_tags($_POST['contact']));
$date = date("F j, Y | h:i A");
$comment = addslashes(strip_tags($_POST['comment']));
$newsid = $_GET['id'];
$check1 = @mysql_num_rows(mysql_query("SELECT * FROM `comments` WHERE `comment`='".$comment."' "));

if ($_POST['submit']) {
  if((empty($_SESSION['username'])) || (empty($_SESSION['userid']))) {
  echo "<b><br /><br />Please login to post a comment.</b>";
  }
  else
  {
  if ($user != $row['username']) {
echo "<b><br /><br />That alias is being used by a member, please Login if you are that member</b>"; 
}
else {
  if ($check1 >= 1) { // Check to see if comment already exists
  echo "<b><br /><br />Comment has already been posted!</b>"; }
  elseif ($contact == NULL || $comment == NULL || $user == NULL) {
   echo "<b><br /><br />Please fill in all fields.</b>"; }
   else {
mysql_query("INSERT INTO `comments` (`user`, `contact`,`comment`,`date`, `newsid`) VALUES ('".$user."','".$contact."','".$comment."','".$date."','".$newsid."')");
mysql_query("UPDATE `news` SET `comments` = comments + 1 WHERE `id` ='".$newsid."' ") or die(mysql_error());

   echo "<b><br /><br />Comment has been posted! </b><META HTTP-EQUIV=\"REFRESH\" CONTENT=\"2; URL=/show.php?id=$id\">"; 
   }
   }
   }
   }
?>
<br />
<br />

<form id='1' name='1' method='post'>
<table width="90%" border="0" cellspacing="0" cellpadding="0">
   <tr>
      <td><div align="right">Alias:   </div></td>
       <td><input name="username" type="text" size="30" value=""></td>
   </tr>
    <tr>
      <td><div align="right">Contact:   </div></td>
      <td><input type="text" name="contact" size="30" value=""> <i>(Email Address)</i></td>
    </tr>
    <tr>
      <td><div align="right">Comment:   </div></td>
      <td><textarea name="comment" cols="45" rows="5" wrap="VIRTUAL"></textarea></td>
    </tr>
    <tr>
      <td></td>
      <td colspan="2">     
        <input style="font-size: 8pt; color: #000000; word-spacing: 0; margin-top: 0; margin-bottom: 0" type="submit" name="submit" value="Add Comment"></td>
    </tr>
  </table>
</form>
	</div>
	</div>
<?php include("includes/footer1.php"); ?>

I wrote in some comments to help you move along faster...

clanstyles · July 9, 2007

heres a few things right now i see.

1)$com = $_GET['del'];

change to something like:

$com = (int) trim(mysql_real_escape_string(strip_tags($_GET['del'])));

2)if ($com != NULL && is_numeric($com))

Change to something like:

if(!empty($com))

July 9, 2007

$com = (int) trim(mysql_real_escape_string(strip_tags($_GET['del'])));

Would that be for added security? And also since we;re talking about security...whats a good way to prevent sql injection...is there any more enforcements other than addslashes/striptags?

Sign In

[SOLVED] Help with deleting from database, "SQL sytax error"

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information