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