error in SQL syntax, can't work it out..

[helraizer]

Hi folks,

 

I'm moving my image based chatbox to a database system, currently I am working on the post deletion aspect of it.

 

I have this code (mysql connection in seperate file, working perfectly)

 

delete.php5 -

<?php //only using tags to get syntax highlighting.


$sqlx = "SELECT * FROM tblcomments INNER JOIN tblip ON tblcomments.id = `tblip`.`id` ORDER BY tblcomments.`id`";

$resultx = mysql_query($sqlx) or die("Error in part X: ".mysql_error());


while ($row = mysql_fetch_array($resultx)) {
        $ip = $_SERVER['REMOTE_ADDR'];
        
       $id = $row['id'];
        


if($ip == $row['ip']) {

$message = $row['comment'];

echo '<table align="center"><tr><td>';
echo '<a href="?comment_id='.$id.'">delete comment: "'.$message.'"</a>';
echo '</td></tr></table>';

if (isset($_GET['comment_id']) && is_numeric($_GET['comment_id'])) {


    $comment_id = $_GET['comment_id'];
    $my_ip = $_SERVER['REMOTE_ADDR'];

$sql1 = "SELECT * FROM tblcomments INNER JOIN tblattributes ON tblcomments.id = tblattributes.id INNER JOIN tblip ON tblattributes.id = tblip.id WHERE `tblcomments`.`id`='$comment_id'";

    $result1 = mysql_query($sql1) or die("Error perforing 1 :" . mysql_error());

while ($row = mysql_fetch_array($result1)) {
    	
    	         if ($my_ip === $row['ip'] && $comment_id === $row['id']) {
    	         	
   $comment_id = $_GET['comment_id'];
    $my_ip = $_SERVER['REMOTE_ADDR'];
    
   $sql2 = "DELETE * FROM `tblcomments`, `tblip` WHERE `tblcomments`.`id`='$comment_id' AND `tblip`.`ip`= '$my_ip'";

            $result = mysql_query($sql2) or die("Error performing 2 :" . mysql_error());
            
             

            die("Post deletion successful <br><br> Please click <a href='./index.php5'>here</a> to go back. <br><br> Thank you!");
           

        } else {

		$error = _NO_POST;
		$_SESSION['post'] = $error;
            header('Location: index.php5');
            exit();


        }
}

}
else {
die('<div class="ddgb_entrybox">
	<table width="100%" border="0" cellspacing="8" cellpadding="0">
	<tr>
    <td width="42%" align="right" valign="top"></td>
	<td align="center" valign="top">
            <h2> '. _ERROR . '</h2><ul>
             
              <li> '. _MAL . '</li>
            
            </ul>
            <br><br><br>
</td>
	</tr>
	</table>
	</div>');
}
}
}

?>

 

Error given:

 

Error performing 2 :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 '* FROM `tblcomments`, `tblip` WHERE `tblcomments`.`id`='10' AND `tblip`.`ip`= '8' at line 1

 

This corresponds to this part of the code:

 

   $sql2 = "DELETE * FROM `tblcomments`, `tblip` WHERE `tblcomments`.`id`='$comment_id' AND `tblip`.`ip`= '$my_ip'";

            $result = mysql_query($sql2) or die("Error performing 2 :" . mysql_error());
            

 

For the life of me I can't work out why there is an error.. am I missing something obvious?

 

Thanks,

Sam

[peranha]

Yes, you do not use * in a delete command

 

$sql2 = "DELETE FROM `tblcomments`, `tblip` WHERE `tblcomments`.`id`='$comment_id' AND `tblip`.`ip`= '$my_ip'"

[helraizer]

Delete All Rows

 

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

 

DELETE FROM table_name

 

or

 

DELETE * FROM table_name

so that's not quite true

 

but still, I deleted the * from the command and now I get:

 

Error performing 2 :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 `tblcomments`.`id`='1' AND `tblip`.`ip`= '86.140.73.183'' at line 1

 

Sam

[peranha]

$sql2 = "DELETE FROM `tblcomments`, `tblip` WHERE `tblcomments.id`='$comment_id' AND `tblip.ip`= '$my_ip'"

 

Try that I took out the ticks in the tblcomments.id and tblip.ip

 

also is tblip.ip supposed to be tblip.id

[trq]

Delete All Rows

 

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

 

DELETE FROM table_name

 

or

 

DELETE * FROM table_name

so that's not quite true

 

w3schools have it wrong, and to be honest, its not the first time. Your much better off using the mysql manual as a reference. Here{/url] is the syntax for a DELETE statement.

[helraizer]

$sql2 = "DELETE FROM `tblcomments`, `tblip` WHERE `tblcomments.id`='$comment_id' AND `tblip.ip`= '$my_ip'"

 

Try that I took out the ticks in the tblcomments.id and tblip.ip

 

also is tblip.ip supposed to be tblip.id

 

it's .ip because it uses the comment id and user ip to make sure they can only delete their own posts. won't `tblip.ip` and `tblcomments.id` now look for a column called tblcomments.id rather than a column called id in tblcomments? (hope that made some sense ).

 

The error appears to be coming from the fact I'm using the two tables in the FROM clause, but I take out leave it as:

 

"DELETE FROM tblcomments WHERE tblcomments.id='$comment_id' AND tblip.ip = '$my_ip'" then it says

Error performing 2 :Unknown column 'tblip.ip' in 'where clause'

.

 

Is it possible to use INNER JOIN in a DELETE statement?

 

Sam

[trq]

Did you look at the link to the manual I gave you?

[helraizer]

Did you look at the link to the manual I gave you?

 

I did/am currently doing so.