Where are Custom Menus saved in the Database for WordPress 3.0?

1 Views

I’ve been searching and searching my sql database in phpmyadmin but I can’t seem to find where custom menus are stored in the database. Anyone know where it’s stored? I have a large menu basically that takes forever to create. I’m in development now and rather than recreating it in production I want to try and copy the sql over to see if it works. Thanks!

Post Views: 1

Related posts

Leave a Reply

9 comments

  1. When new custom menu is added, its entry is added into several tables.

    1. table: _term_taxonomy with a column taxonomy and value nav_menu.
    2. from 1) term_id is also in table:_terms with the title of menu which is shown in admin panel
    3. table:_term_relationship will contain term_texonomy_id from 1) and connection to all object_id which will be linked to all _post table having sub_menu and below level menu.
    4. table:_postmeta will contain all the details related to menu and submenu for each object_id from _posts table.

  4. I hate to bump a old thread. But if anyone runs into this like I did searching for the answer, as of WP 3.2 the post_type for menu is now called nav_menu_item in the wp_posts table. They didn’t have that on the wp page, they just had nav_menu. Which I couldn’t find.

  6. I wanted to provide a more complete and up to date answer for people who stumble upon this post like I did. There are several places in the database that need to be checked/updated if you’re wanting to manually insert or update menu items.

    The first step is finding your main menu container. This is stored in wp_terms. Just search by name for the name of your menu. Take note of the ID. In my example this will be 33. Likewise, you could insert a new row here to create a new menu. The slug can’t have spaces in it. Just substitute in - instead.

    Now we’ll get to finding items in that menu. Those are stored in wp_posts with a post_type of nav_menu_item. There’s nothing in this table that ties it to your ID above, so you’ll have to go by post_title. If the goal is inserting a new menu item, with phpMyAdmin you can just click Copy on an existing item in any menu and change post_title and post_name. Now take note of the ID of the item you inserted. Mine is 9179. It will also be nice to take note of the ID of the item you copied (even better if it was from the same menu) as we’ll be using that in the next step.

    Now go to wp_postmeta and search on the post_id field for the item we just copied. This will bring up all of the fields that need to be filled in to get the info for our new menu item. Using phpMyAdmin copy each of those entries and change as needed. _menu_item_menu_item_parent, _menu_item_target, and _menu_item_classes are the fields that will most likely need changing. And make sure you’re putting the ID of your new item (9179) in _menu_item_object_id.

    After those are all created we can now tie that menu item to our menu. We need to get the term_taxonomy_id from wp_term_taxonomy. Just search term_id using the ID from the first step, 33 for me. The term_taxonomy_id happens to be 33 for me as well. No we need to insert a new row into wp_term_relationships. object_id will be your post id (9179) and term_taxonomy_id is what we just looked up (33).

    Now your new item should be in your menu.

  7. Here are the SQL scripts I used:

    Get the menus from the wp_term table:

    SELECT * FROM wp_terms AS t LEFT JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id WHERE tt.taxonomy = 'nav_menu'

    Use the term_id to get get all of the menu titles / links from all the other connected tables. In my opinion this is a terrible relational database. Just replace the tt.term_id with yours from the query above, below is 11.

    SELECT p.post_title, p.post_name, p.menu_order, pm.meta_value FROM wp_posts AS p LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id INNER JOIN wp_postmeta AS pm ON p.Id = pm.post_id WHERE p.post_type = 'nav_menu_item' AND tt.term_id = 11 AND pm.meta_key = '_menu_item_url'

  8. Each menu item are saved in posts table and menu hierarchy/options/urls etc. belonging to these items are stored in postmeta table. In postmeta table you can see post_id column belonging to posts table items.

  9. $postId = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM `wp_postmeta` WHERE meta_key='_menu_item_object_id' AND `meta_value`='$id'"));
$postIdc = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM `wp_postmeta` WHERE meta_key='_menu_item_object_id' AND `meta_value`='$a[1]'"));
$sql2 = "SELECT * FROM `wp_postmeta` WHERE meta_key='_menu_item_menu_item_parent' AND `meta_value`='$postId'";
$sql2c = "SELECT * FROM `wp_postmeta` WHERE meta_key='_menu_item_menu_item_parent' AND `meta_value`='$postIdc'";