Where does Wordpress store custom menus?

Question

I had to copy a whole wordpress-installation to another server. I simply installed a new Wordpress there and imported all the data from an xml-file previous Wordpress "exported" for me. Anyway, custom menus were not included. Do you know where in the database are they located, so I could grab them from there?

Answer 1

I've got a site with a Magento install and WordPress install sitting next to each other, and cross-linking.

I recently spent several hours writing a class to drop into the Magento installation so that I could render the WordPress menu as the navigation menu in the Magento site.

Posts here have been helpful, but none of them have completely explained the structure of how WordPress menus are stored. Like many WP things, it is stored in a series of relationships. Here's the structure:

(note that this example presumes a table prefix if of "wp_")

1. First, it's important to recognize that a menu item can be a post (technically it's a page, but pages are stored in the post table), a category, or it can be a custom link.
2. Because WP supports multiple menus, you first look in the wp_term_taxonomy table table to find any terms with the taxonomy of 'nav_menu'. Note the term_id from that table.
3. To find the name and slug of the menu, visit the wp_terms table and find the term with the id noted from step 2 above.
4. Go to wp_term_relationships table and list all of the records with the term_taxonomy_id that matched the term_id from step 1. The object_id field tells you the wp_post.id record where you can find the menu record.
5. Finally, go to wp_postmeta to find many elements describing the menu. Of particular interest are:
   • _menu_item_object - the TYPE of menu item (page, custom, or category)
   • _menu_item_object_id - the id of the actual POST (or category, if it's a category) that the menu item references
   • _menu_item_menu_item_parent - the heirarchical parent structure of the MENU (which can be different than the post parent relationships)
6. _menu_item_url - the slug of the menu item (if it is a custom link menu item)

Sample SQL statements to perform the above described operation:

SELECT t.term_id  FROM wp_term_taxonomy as tax  LEFT JOIN wp_terms as t ON tax.term_id = t.term_id  WHERE taxonomy = 'nav_menu' and name like '%top%' 

(looks for a menu item with the name of 'Top', and gets the term id)

SELECT p.ID, p.post_title, p.post_name, p.menu_order, n.post_name as n_name, n.post_title as n_title, m.meta_value, pp.meta_value as menu_parent FROM wp_term_relationships as txr  INNER JOIN wp_posts as p ON txr.object_id = p.ID  LEFT JOIN wp_postmeta as m ON p.ID = m.post_id  LEFT JOIN wp_postmeta as pl ON p.ID = pl.post_id AND pl.meta_key = '_menu_item_object_id'  LEFT JOIN wp_postmeta as pp ON p.ID = pp.post_id AND pp.meta_key = '_menu_item_menu_item_parent'  LEFT JOIN wp_posts as n ON pl.meta_value = n.ID  WHERE txr.term_taxonomy_id = 3 AND p.post_status='publish'      AND p.post_type = 'nav_menu_item' AND m.meta_key = '_menu_item_url'  ORDER BY p.menu_order 

(loads the data for the menu, based on the term_id of 3)

Note that this sql statement will work for pages and custom menus (I don't have any categories, so didn't include that). The data loaded will allow you to build the permalink using the siteurl from the wp_options table, and appending the post_name to the end (technically, it's not getting the parent structure, but WP finds the page/post properly without it)

Update
A commenter asked about assembling the child menu items with the parent menu items. That will need to be done with PHP. Something like below will do that for you:

// run the query from above $results = $wpdb->get_results('SELECT....');  // declare new variable to store "assembled" menu $menu = array();  // loop over the items assigning children to parents foreach( $results AS $row ) {     // assemble key bits for the menu item     $item = array(         // handles custom navigation labels         'title' => ( $row->post_title ) ? $row->post_title : $row->n_title,         // handles custom links         'permalink' => ( $row->meta_value ) ? $row->meta_value : get_permalink( $row->ID ),         // declares empty placeholder for any child items         'children' => array()     );      // if the menu item has a parent, assign as child of the parent     if ( $row->menu_parent ) {         $menu[ $row->menu_parent ][ 'children' ][] = $item;     } else {         $menu[ $row->ID ] = $item;     } }  var_dump( $menu );  // outputs something like below: /**  * array (size=6)  *  77 =>   *     array (size=3)  *       'title' => string 'About Us' (length=8)  *       'permalink' => string 'http://www.example.com/about' (length=33)  *       'children' =>   *        array (size=7)  *          0 =>   *            array (size=3)  *              'title' => string 'Welcome' (length=22)  *              'permalink' => string 'http://www.example.com/welcome' (length=35)  *              'children' =>   *                array (size=0)  *                  empty  *          1 =>   *            array (size=3)  *              'title' => string 'Mission' (length=20)  *              'permalink' => string 'http://www.example.com/mission' (length=33)  *              'children' =>   *                array (size=0)  *                  empty  *  90 =>   *    array (size=3)  *      'title' => string 'Contact Us' (length=10)  *      'permalink' => string 'http://www.example.com/contact' (length=33)  *      'children' =>   *        array (size=5)  *          0 =>   *            array (size=3)  *              'title' => string 'Why Us' (length=12)  *              'permalink' => string 'http://www.example.com/why' (length=35)  *              'children' =>   *                array (size=0)  *                  empty  *  1258 =>   *    array (size=3)  *      'title' => string 'Login' (length=12)  *      'permalink' => string 'https://customlink.example.com/some/path/login.php' (length=82)  *      'children' =>   *        array (size=0)  *          empty  */