Fix for mainmenu-xx in Joomla SEF urls

When converting a Joomla 1.0 site to 1.5 and activating SEF URLs, you may discover that the URLs suddenly have the menuname in them. For instance like this: http://example.com/about-us-mainmenu-12. This is because of changes to aliases in the migration process. A menu item titled “About us” might have had the alias “about-us” in Joomla 1.0, but in Joomla 1.5 it is given the alias “about-us-mainmenu-12″ after the migration, where 12 is the menu item’s ID, or Itemid. Here is a fix you can run on your database to correct all of this.

First of all, doing a search-replace is pretty straightforward, but the problem with the REPLACE function in MySQL is that it does not allow for wildcards. Another possible solution would be the REGEX operator, but that only returns true/false (0,1) for match or no match. So I have come up with two solutions. Make sure to back up your database before trying any of them.

1) A simple UPDATE query

UPDATE jos_menu
SET alias = SUBSTRING(alias, 1, LOCATE('-mainmenu-', alias)-1)
WHERE alias LIKE '%-mainmenu-%'

Replace “jos_” with your table prefix and both occurrences of “mainmenu” with the menutype you’d like to look for. Then repeat this query for all menutypes you have in your jos_menu_types table.

2) A function that loops through all menutypes

This function will find all menutypes, loop through them and perform the above UPDATE query for each menutype. Handy if you have lots of menus.

DROP FUNCTION IF EXISTS menuUpdate;
DELIMITER $$

CREATE FUNCTION menuUpdate()
    RETURNS VARCHAR(255)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE menuname VARCHAR(75);
    DECLARE cur1 CURSOR FOR SELECT menutype FROM jos_menu_types;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur1;

    REPEAT
        FETCH cur1 INTO menuname;
        IF NOT done THEN
            UPDATE jos_menu
                SET alias = SUBSTRING(
                    alias,
                    1,
                    LOCATE(CONCAT('-',menuname,'-'), alias)-1)
                WHERE alias LIKE CONCAT('%-',menuname,'-%');
        END IF;
    UNTIL done END REPEAT;

    RETURN NULL;

END$$

DELIMITER ;

Make sure to replace both occurrences of “jos_” with your own table prefix. After that, you run this function like this:

SELECT menuUpdate();

This should just return NULL, and all your menu items should be up to date.