[SOLVED] Trouble with-IN

[Xeoncross]

I am having some trouble with the "IN" clause in MySQL and I can't seem to find any half way decent tutorials on it (probably because it is the word "in"). So first off, can anyone point me in the right direction?

 

Second, here is the query that I am getting an error on.

 

DELETE `posts`, `content` FROM `posts`, `content` WHERE posts.id = content.id AND posts.id in (59)

 

MySQL says, "We could not find any posts in the list 59". However, I know that post id exists.

[roopurt18]

I think your query needs to change to:

DELETE `posts`.*, `content`.* FROM `posts`, `content` WHERE posts.id = content.id AND posts.id in (59)

 

I could be wrong though, so try that on test data first.

[Xeoncross]

Ok, well I just took a look at the database and it said that the row with the id "59" WAS deleted.

 

But the function mysql_affected_rows() isn't showing any rows deleted. What am I doing wrong?

 

<?php
...
if (mysql_affected_rows($result["result"]) > 0) { //There was a row changed....
    $output = '<div class="message"><h3>'. mysql_affected_rows($result["result"]). ' Successfully Deleted!</h3></div>';
} else {
    $errors[] = '<h3>No POSTS Deleted</h3>We could not find any posts in the list '. $list. ' And the query<br />'. $query;
}
..
?>

 

 

Also, I changed the code to this and it still does the same thing.

DELETE FROM `posts`, `content` USING `posts`, `content` WHERE content.id = posts.id AND posts.id in (59)

[roopurt18]

I could be mistaken, but I think I once read somewhere that mysql_affected_rows has trouble when used with statements that delete from multiple tables.

[Xeoncross]

 

So, how can I figure out how many rows were deleted?

 

 

[roopurt18]

From the documentation on mysql_query().

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

 

I'd say in most instances if you've run the query multiple times in testing and it appears to work, just check the return value of mysql_query and don't worry on the exact number of queries deleted, updated, or inserted.  If you really start to think about it, trying to define success in queries across multiple tables becomes difficult.  Even in an UPDATE statement that could affect many rows, mysql_affected_rows() only returns the ones actually affected.

 

In your case, it looks like you're deleting forum posts or messages.  It'll be pretty easy to know when it's not working because the post / message will still be there.