SQL WordPress Database Menu

gistfile1.txt

Information about currently select menu is located in wp_options table as serialized PHP array. 
For example if we use TwentyEleven theme, 
then we will have a record in wp_options table with option_name column equals to theme_mod_twentyeleven and 
option_value column equals to ...;s:18:"nav_menu_locations";a:1:{s:7:"primary";i:103;}}. 
Here you can see that menu with term_id equals to 103 is currently selected as "primary" menu.

The key principle here is that we always have separate options record for each WP theme. 
Such options have the same name structure: theme_mods_{your-theme-name}.


UPDATE wp_posts
  LEFT JOIN wp_term_relationships AS tr ON tr.object_id = wp_posts.ID
  LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
  SET `menu_order` = `menu_order` + 164
 WHERE wp_posts.post_type = 'nav_menu_item'
   AND tt.term_id = 1285
   
   
UPDATE `new-sos`.`wp_term_relationships` 
SET `term_taxonomy_id` = '1284' 
WHERE `wp_term_relationships`.`term_taxonomy_id` = 1285;
   
   
SELECT * 
  FROM `wp_term_taxonomy` 
 WHERE `taxonomy` = 'nav_menu'
 
 change count
   

wp_posts

Menu item is custom post type in WordPress. They are stored in wp_posts table. 

SELECT * 
  FROM wp_posts 
 WHERE post_type = 'nav_menu_item';

wp_term_relationships

Relations between menus and menu items are stored in wp_term_relationships table.

	
object_id
term_taxonomy_id
term_order

wp_term_relationships.object_id = wp_posts.ID

wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id

     SELECT * 
    FROM wp_posts
    LEFT JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
    LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    LEFT JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id 
    WHERE post_type = 'nav_menu_item'
    AND wp_term_taxonomy.taxonomy = 'nav_menu'
    AND wp_term_taxonomy.term_id = /*your menu ID*/;
    
    SELECT p.* 
  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
 WHERE p.post_type = 'nav_menu_item'
   AND tt.term_id = /*your menu ID*/;

wp_term_taxonomy

term_taxonomy_id
term_id
taxonomy
description
parent
count


SELECT * 
  FROM `wp_term_taxonomy` 
 WHERE `taxonomy` = 'nav_menu'


Take term_id and go to wp_terms


Note:
Menu by itself is a taxonomy in WordPress.

wp_terms

	
term_id
name
slug
term_group

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

We get

term_id
name
slug
term_group
term_taxonomy_id
term_id
taxonomy
description
parent
count

Where name is name of users menu and count is elements of menu