MediaWiki/Sql
< MediaWiki
Jump to navigation
Jump to search
Revision as of 10:11, 28 January 2009 by Freephile (talk | contribs) (MediaWiki/Sql moved to MediaWiki/Sql: make naming uniform with growing collection of MediaWiki content)
Some database queries that you might want to use and bookmark in your database administration tool such as MySQL Query Browser. http://dev.mysql.com/downloads/gui-tools/5.0.html or SQLyog http://www.webyog.com/en/index.php
Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'.
SELECT DISTINCT
page_title
FROM
page
RIGHT JOIN
revision
ON page_id = rev_page
WHERE
rev_text_id
IN (
SELECT
old_id
FROM
text
WHERE
# old_text LIKE '%#icon%'
old_text REGEXP('#icon') # regex is easier to deal with
)
A more complex query that summarizes some information about an article (You supply the article title)
SELECT
p.page_id 'id',
p.page_title 'title',
p.page_namespace 'namespace',
t.old_id 'latest text id',
t.old_text 'content',
r.rev_id,
r.rev_comment 'comment',
r.rev_user,
r.rev_user_text,
r.rev_parent_id 'previous revision'
FROM
text t
RIGHT JOIN
revision r
ON t.old_id = r.rev_text_id
RIGHT JOIN
page p
ON r.rev_id = p.page_latest
WHERE
p.page_title = 'Main_Page'