sql query to get custom menu data

  • JMC Creative

    (@jmc-creative)


    15 years, 10 months ago

    I’m trying to use the custom menus function to create a jump-select box. That way my client can use the great admin custom-menus functionality to edit the dropdown. Using wp_nav_menu isn’t going to work cause it wraps everything in a list, but I need select/option tags. So I wrote a function to build the select list, which works fine except one thing:
    Apparently when a menu item is stored in the wp_posts table, it is given a post_type=’nav_menu_item’ (or whatever, I don’t have it in front of me), but it is only given a post_title if the title has been changed from the post/page it links to. Otherwise, the post_title stays blank. I can’t figure out how to structure a query to get the title of the nav-item. Can anyone help with this? I’ll paste in the function.

    function jp_i_want_to_menu ($menuName) {

	global $wpdb;
	$pDB = $wpdb->posts;
	$itemsWant = $wpdb->get_results("
		SELECT $pDB.post_title, $pDB.guid
		FROM
		$pDB INNER JOIN wp_term_relationships
		ON wp_term_relationships.object_id = $pDB.ID
		WHERE post_type = 'nav_menu_item'
		AND wp_term_relationships.term_taxonomy_id =
			( SELECT wp_terms.term_id FROM wp_terms WHERE wp_terms.slug = $menuName)
		ORDER BY $pDB.menu_order ASC;
		");
	if($itemsWant) {
	    foreach($itemsWant as $item)
		echo '<option value="' . $item->guid . '">' . $item->post_title . '</option>';
	}
}
Viewing 1 replies (of 1 total)
  • sevmusic

    (@sevmusic)

    15 years, 7 months ago

    how about this?

    function jp_i_want_to_menu ($menuName) {

	global $wpdb;
	$pDB = $wpdb->posts;
	$itemsWant = $wpdb->get_results("
		SELECT p2.post_title, p2.guid
            FROM $pDB p1
            INNER JOIN wp_term_relationships AS TR
            ON TR.object_id = p1.ID
            INNER JOIN wp_postmeta AS PM
            ON pm.post_id = p1.ID
            INNER JOIN $pDB AS p2
            ON p2.ID = PM.meta_value
            WHERE p1.post_type = 'nav_menu_item'
            AND TR.term_taxonomy_id = ( SELECT wp_terms.term_id FROM wp_terms WHERE wp_terms.slug = $menuName)
            AND pm.meta_key = '_menu_item_object_id'
            ORDER BY p1.menu_order ASC
		");
	if($itemsWant) {
	    foreach($itemsWant as $item)
		echo '<option value="' . $item->guid . '">' . $item->post_title . '</option>';
	}
}

    From what I can tell making a custom menu adds extra (empty) rows in wp_posts ($pDB) that references the “real” page in the wp_postmeta table. So I inner joined the wp_postmeta table to your original query so it can get the original post data and not the added (empty) row data.

Viewing 1 replies (of 1 total)

The topic ‘sql query to get custom menu data’ is closed to new replies.