In the posts table, I have 13,155 posts.
The software shows 12,989.

I have 166 posts that were deleted, but not removed from the database??

Edit, actually it means I have 13,155 post ID's, but only 12,989 posts with a text.

[php]
SELECT _fpro_posts_text.post_id
FROM _fpro_posts, _fpro_posts_text
WHERE _fpro_posts.poster_id='2'
AND _fpro_posts_text.post_id != _fpro_posts.post_id
[/php]

Shouldn't this display a list of posts_text.post_id's that do not have a corresponding posts.posts_id for the poster_id 2?

This is hopeless. There's a ton of NULL's under id 2, okay, 136 or so, but I can't delete them records!

From what you say this forum software works like other forum software with a POSTS table, a MEMBERS/USERS table and a TOPICS table. Each of them refer to each other by an auto-incremented value (topicID, postID and memberID). If you want to delete all the posts from members that don't exist you'd have to use multiple queries. For example:


<?php

// establish variables
$memberposts = "0";
$nonmemberposts = "0";

// first query all posts
$post_query ="SELECT * FROM posts";
$post_result = mysql_query($post_query);
while($post_row = mysql_fetch_array($post_result)) {
// now we can address each post individually
// we should set some variables we need
$postID = $post_row["id"]; // The post ID
$memberID = $post_row["memberid"]; // The member that made this post
$isMember = true; // boolean for whether the member exists or not
// now we need to figure out if the member with that id still exists
// to do that you need to do a num_rows query
$member_query = "SELECT * FROM members WHERE id='$memberID'";
$member_count = mysql_num_rows($member_query);
// check if it returns a result
if($member_count < "1") {
// the member does not exist
$isMember = false;
}
// now we return the result of our script
if($isMember) {
// post verified
$memberposts++;
} else {
// delete the post?
// I wouldn't
// $delete_query = "DELETE FROM posts WHERE id='$postID'";
// mysql_query($delete_query);
$nonmemberposts++;
}
}

// display a report at the end
echo "Total posts by members: " . $membersposts . "<br />total posts by non-members: " . $nonmemberposts;

?>


Obviously you'll have to replace the references I make to mysql rows and tables because I don't know whats yours are exactly. But if I do understand what you want this code should work, although I haven't tested it.

EDIT: Please don't run the script with the delete query in it until you've verified that it works. I would not want you to accidentally delete all the posts in this forum because of my bad code. I've already commented the delete query out so that if you want to you can just comment it back in.

Yeah, I don't want to delete them just yet. I want to see them first, that's why I was using the SQL query in the phpAdmin.

I can provide the three tables for you guys if that'll help.


<?php

// $delete_query = "DELETE ?????FROM posts WHERE id='$postID'";
// mysql_query($delete_query);
$nonmemberposts++;
?>


wouldn't work if it wasn't commented out, this would cause an error, not telling what you want to delete.

why don't you just delete id #2, the whole row? if you are using auto increment for the id field, it wouldn't let you delete the row unless you delete the id

I've deleted members in the users table, but not their posts. Therefore there's posts out there that cause errors while searching.

imo, the best way to maintain your site would be to manually go into the database through php admin and delete the rows that way.

a script would be nice for maintenance, but you are risking wiping out data you want to keep.

just wondering, did you join the mysql forum. you should, and post questions in the forum.

you'll get your solution...if you are driven.

BTW..I find it humorous, a .NET Developer is running his forum using PHP, Microsoft's main nemesis.

Isn't that like sayin you work at Ford but drive a Toyota?

:icon_smile: just sayin!

I was looking into developing my own software using ASP-MVC2 with SQL Server, but it seems like too much work. I'd be reinventing a lot of the options here.

Might as well stick with this and enhance it, instead of recreating everything, right?

yes, stick with the PHP site. Classic ASP is an old technology. You have to write a ridiculous amount of "spaghetti" code to do even a simple database call.

Microsoft updated to .NET for a reason!