check for unlinked posts in my forum tables.

[newbeee]

I have my own forum scripts and wish to know if some posts have for what ever reason become unlinked.

 

list all the results where the category_id number in the category table is not in the topic table field category_id

 

and the second seperate query...

 

list all the results where the topic_id number in the comments table not in the topic table field topic_id

 

below are the tables i have and their fields that would be needed.

 

CREATE TABLE IF NOT EXISTS `forum_category` (
  `category_id` bigint(20) NOT NULL auto_increment,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `topic_id` bigint(20) NOT NULL auto_increment,
  `category_id` bigint(20) NOT NULL default '0',
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

CREATE TABLE IF NOT EXISTS `forum_comments` (
  `postcounter` bigint(20) NOT NULL auto_increment,
  `topic_id` bigint(20) NOT NULL default '0',
  KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

[corbin]

DELETE FROM forum_topics ft LEFT JOIN forum_category fc ON fc.category_id = ft.category_id WHERE fc.category_id IS NULL;

 

Might want to backup just incase I did something horribly wrong ;p.

 

 

Deleting from the posts table would be the same concept, just different tables/fields.

 

 

(You would want to delete topics first by the way, or a post could lose its topic later on.)

[newbeee]

sory i was not clear with my OP

 

did not want to delete just to list them.

 

what i did not say is that these posts could be valid but just not linked properly for errors in my code or something, so just wanted to list all the topics and the comments that may have category_id or topic_id but just linked to one another.

 

[fenway]

Then replace "DELETE FROM" with "SELECT *", or something equivalent.