MySQL Procedure Get WordPress Site Name

If you manage many WordPress site databases it can get a bit crazy to know which site goes with what database but it gets more “fun” when you have a network site, below I attempt to help document some ways to help.

This first example is a rather long process that doesn’t seem to make since why you would do it until you get further down.

DROP PROCEDURE IF EXISTS GetSiteNameFromDefinedTable;

DELIMITER $$
CREATE PROCEDURE GetSiteNameFromDefinedTable (tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT (
			'SELECT CASE
                    WHEN option_name = ''blogname''
                        THEN option_value
                    END AS Site_Name FROM '
			,tableName
			,' WHERE option_name = ''blogname'''
			);
	PREPARE stmt
	FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END $$
DELIMITER;

call GetSiteNameFromDefinedTable('wp_options');

Complete Process, this is long and likely could be cleaner but works.

DROP PROCEDURE IF EXISTS GetSiteNameFromDefinedTable;

DELIMITER $$
CREATE PROCEDURE GetSiteNameFromDefinedTable (tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT ('INSERT INTO SiteNameDetails (Site_Name,Site_URL, Site_Table, Site_Database)
		SELECT distinct
			(
            SELECT CASE
                    WHEN option_name = ''blogname''
                        THEN option_value
                    END
            FROM ',tableName,'
            WHERE option_name = ''blogname''
            ) AS Site_Name,
            
            (
            SELECT CASE
                    WHEN option_name = ''siteurl''
                        THEN option_value
                    END
            FROM ',tableName,'
            WHERE option_name = ''siteurl''
            ) AS ''URL'',
            ''',tableName,''' AS Table_Name,
            Database() AS Database_Name
		FROM ',tableName);
	PREPARE stmt
	FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

#call GetSiteNameFromDefinedTable('wp_2_options');

DROP PROCEDURE IF EXISTS SiteNameDefinedTables;
DELIMITER $$
CREATE PROCEDURE SiteNameDefinedTables()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id varchar(50);
  DECLARE cur CURSOR FOR (SELECT DISTINCT table_name FROM `information_schema`.`columns` WHERE table_name LIKE '%_options%') ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
  
  OPEN cur;
  
  testLoop: LOOP
    FETCH cur INTO _id;
    IF done THEN
      LEAVE testLoop;
    END IF;
     
    CALL GetSiteNameFromDefinedTable(_id);
   
   
  END LOOP testLoop;
  
  CLOSE cur;
  
END
$$
DELIMITER ;

Once you have the procedures created you can do this

drop temporary table if exists SiteNameDetails;
Create temporary table if not exists SiteNameDetails(
Site_Name VarChar(200),
Site_URL VarChar(200),
Site_Table VarChar(100),
Site_Database VarChar(100)
);

call SiteNameDefinedTables();

SELECT 
    wpb.blog_id,
    wpb.site_id,
    snd.Site_Name,
    snd.Site_URL,
    snd.Site_Table,
    snd.Site_Database
FROM
    SiteNameDetails snd
        LEFT OUTER JOIN
    wp_blogs wpb ON CONCAT(snd.site_url, '/') = CONCAT('http://', wpb.domain, wpb.path)
ORDER BY site_id , blog_id;