Jump to content

Wikipedia:People by year/SQL to find people categories

From Wikipedia, the free encyclopedia
DROP TABLE IF EXISTS temp_peoplecats;

CREATE TABLE `temp_peoplecats` (
  `ct_from_name` varchar(255) binary NOT NULL default '',
  `ct_from` int(8) unsigned NOT NULL default '0',
  `ct_to` varchar(255) binary NOT NULL default '',
  `ct_sortkey` varchar(255) binary NOT NULL default '',
  PRIMARY KEY  (`ct_from`,`ct_to`)
) TYPE=MyISAM; 

INSERT INTO temp_peoplecats VALUES ('People', 691008, 'People', 'People');
Repeat the following for the number of levels to be included:
INSERT IGNORE    temp_peoplecats
SELECT DISTINCT  cur_title, cl_from, cl_to, cl_sortkey
FROM  categorylinks, cur, temp_peoplecats
WHERE cl_to = ct_from_name
AND   cl_from=cur_id
AND   cur_namespace=14;

DELETE FROM temp_peoplecats WHERE ct_from_name LIKE 'Lists%';