Bureaucrats, confirmed, Administrators
4,558
edits
Changes
Jump to navigation
Jump to search
adds more complex query, plus reference to tools
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'.
<source lang="sql">
SELECT DISTINCT page_titleFROM page RIGHT JOIN revision ON page_id = rev_pageWHERE rev_text_id IN ( SELECT old_id FROM ` text` WHERE # old_text LIKE '%#icon%' old_text REGEXP('#icon') # regex is easier to deal with )
</source>
A more complex query that summarizes some information about an article
(You supply the article title)
<source lang="sql">
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'
</source>
== See Also ==
[[Category:Development]]
[[Category:Help]]
[[Category:Tools]]
