Thank you Eric for your SQL queries. I will try them.
Best Regards. Julien Lemoine.
Eric Astor wrote:
links and get the target of redirections for the whole summer. Let me see if I can combine the SQL commands I've found, and come up with one to give you the target of all redirects in the database...
Here we go:
SELECT from_page.page_title AS from_title, to_page.page_title AS to_title FROM page from_page INNER JOIN pagelinks pl ON pl.pl_from = from_page.page_id INNER JOIN page to_page ON to_page.page_namespace = pl.pl_namespace AND to_page.page_title = pl.pl_title WHERE from_page.page_is_redirect=1;
I hope that helps!
Sincerely, Eric Astor
P.S. My original SQL commands follow.
To resolve links:
CREATE TABLE resolvedlinks ( rl_from INT(8) UNSIGNED NOT NULL, rl_to INT(8) UNSIGNED NOT NULL, PRIMARY KEY (rl_from, rl_to) ) SELECT from_page.page_id AS rl_from, to_page.page_id AS rl_to FROM pagelinks INNER JOIN page from_page ON from_page.page_id=pl_from INNER JOIN page to_page ON to_page.page_namespace=pl_namespace AND to_page.page_title=CONCAT(UPPER(SUBSTRING(pl_title,1,1)),SUBSTRING(pl_title, 2));
To re-resolve the links to their actual targets (eliminating one level of redirects):
DROP TEMPORARY TABLE IF EXISTS dl; CREATE TEMPORARY TABLE dl ( dl_from INT(8) UNSIGNED NOT NULL, dl_to INT(8) UNSIGNED NOT NULL ) SELECT rl_from AS dl_from, rl_to AS dl_to FROM resolvedlinks; UPDATE dl INNER JOIN page ON page.page_id=dl.dl_to INNER JOIN resolvedlinks links ON links.rl_from=dl.dl_to SET dl.dl_to=links.rl_to WHERE page.page_is_redirect=1; DROP TABLE IF EXISTS directlinks; CREATE TABLE directlinks ( dl_from INT(8) UNSIGNED NOT NULL, dl_to INT(8) UNSIGNED NOT NULL, PRIMARY KEY (dl_from, dl_to) ) SELECT DISTINCT dl_from, dl_to FROM dl;
Julien, I've been working on a project that's required me to both resolve