New - Pastie

Language:

## mark a section (Learn more)

CREATE TABLE xar_comments ( xar_cid int(11) NOT NULL auto_increment, xar_pid int(11) NOT NULL, xar_modid int(11) default NULL, xar_itemtype int(11) NOT NULL, xar_objectid varchar(255) NOT NULL, xar_date int(11) NOT NULL, xar_author mediumint(9) NOT NULL default '1', xar_title varchar(100) NOT NULL, xar_hostname varchar(255) NOT NULL, xar_text mediumtext, xar_left int(11) NOT NULL, xar_right int(11) NOT NULL, xar_status tinyint(4) NOT NULL, xar_anonpost tinyint(4) default NULL, PRIMARY KEY (xar_cid), KEY i_xar_comments_left (xar_left), KEY i_xar_comments_right (xar_right), KEY i_xar_comments_pid (xar_pid), KEY i_xar_comments_modid (xar_modid), KEY i_xar_comments_itemtype (xar_itemtype), KEY i_xar_comments_objectid (xar_objectid), KEY i_xar_comments_status (xar_status), KEY i_xar_comments_author (xar_author) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table 'xar_comments' -- INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (1, 0, 151, 1, '1', 1185465933, 1, 'ROOT NODE - PLACEHOLDER. DO NOT DELETE!', '', 'This is for internal use and works only as a place holder. PLEASE do NOT delete this comment as it could have detrimental effects on the consistency of the comments table.', 1, 14, 3, NULL); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (2, 1, 151, 1, '1', 1185465933, 3, 'Head 1', '127.0.0.1', 'test', 2, 7, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (3, 1, 151, 1, '1', 1185466867, 3, 'Head 2', '127.0.0.1', 'Test', 8, 11, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (4, 2, 151, 1, '1', 1185472409, 3, 'Re: Head 1', '127.0.0.1', 'test', 3, 6, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (5, 4, 151, 1, '1', 1185472434, 3, 'Re(1): Head 1', '127.0.0.1', 'test', 4, 5, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (6, 3, 151, 1, '1', 1185472468, 3, 'Re: Head 2', '127.0.0.1', 'test', 9, 10, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (7, 1, 151, 1, '1', 1185472479, 3, 'Head 3', '127.0.0.1', 'test', 12, 13, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (14, 13, 151, 1, '2', 1185550631, 3, 'test', '127.0.0.1', 'test', 2, 3, 2, 0); INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (13, 0, 151, 1, '2', 1185550631, 1, 'ROOT NODE - PLACEHOLDER. DO NOT DELETE!', '', 'This is for internal use and works only as a place holder. PLEASE do NOT delete this comment as it could have detrimental effects on the consistency of the comments table.', 1, 4, 3, NULL); SELECT node.xar_cid, node.xar_modid,node.xar_itemtype, node.xar_objectid,node.xar_title, (COUNT(parent.xar_cid) - (sub_tree.depth + 1)) AS depth FROM xar_comments AS node, xar_comments AS parent, xar_comments AS sub_parent, ( SELECT node.xar_cid, (COUNT(parent.xar_cid) - 1) AS depth FROM xar_comments AS node, xar_comments AS parent WHERE node.xar_left BETWEEN parent.xar_left AND parent.xar_right AND node.xar_cid = 1 AND parent.xar_modid = node.xar_modid AND parent.xar_itemtype = node.xar_itemtype AND parent.xar_objectid = node.xar_objectid GROUP BY node.xar_cid ORDER BY node.xar_left )AS sub_tree WHERE node.xar_left BETWEEN parent.xar_left AND parent.xar_right AND node.xar_left BETWEEN sub_parent.xar_left AND sub_parent.xar_right AND sub_parent.xar_cid = sub_tree.xar_cid GROUP BY node.xar_cid HAVING depth <= 1