WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Dynamic Menus (9 posts)

  1. bbhank
    Member
    Posted 2 years ago #

    Looking for how to get menu items from mysql database with WP. Once selected the item will load next tier like the auto parts locators where you select year, then it gives choices of models, then from that, make of vehicle, and etc. The menu system must get data from data from database that can and will, change. The final tier will load into WP where WP does what it does.
    Goal: Media management for changing list of items/sites.

  2. bbhank
    Member
    Posted 1 year ago #

    Clarification: Needing to choose sites by state, then city, then name. Each name will have its page. Each page needs to show dynamically updated address, phone and zip, from table. Menu need to populate with cities on list from table when state is chosen. When city is chosen from the returned list, the menu needs to populate with names. Name chosen takes you to that name's page.

    Clear as mud?

  3. bbhank
    Member
    Posted 1 year ago #

    After exhaustive searching on this subject it seems that such a menu system has only been done with java, jquery, ajax or some other equally cryptic and difficult to understand programming language. PHP is the ticket here. So, not being a programmer or a PHP guru, there was little option but to learn the most efficient and simple method to a menu. Since WordPress is built on PHP, the logic is to keep with the program.
    Having seen that menus can be made with option-select dropdowns that can be fed by PHP from a database I decided to try that. I hacked out some PHP that placed the needed items in each dropdown based on what is selected in the previous dropdown. It's ugly but it works so far.
    That led me to this question: How to get the last selection to actually go to the selected WordPress page. The last dropdown gives a list of titles of WordPress pages, not posts, that once selected need to be opened. A button can also be used - Just need something that works. I will post this once the last part is done. Also looking for how to work with the look of this system - Cosmetics. First, though, and foremost is to get the last selection to go to the selected page. Solutions?
    I tried using lists and css but that requires preloading all the data. Not an option when you're dealing with thousands of rows. I saw some tricky java and stuff that did this but again, this needs to be all PHP.

  4. bbhank
    Member
    Posted 1 year ago #

    superdupersocks posted this request over a year ago. The issue was closed unresolved. I am reprinting it because this description of the issue I think is more concise than mine. At any rate, after much searching, I have also found no usable solution at all.

    Many others might have faced this road block, so I thought to post it here and see if we can find a collective solution. I am trying to create a CMS site based on geo specific services locations. Now, here is the site workflow that I would like to achieve:

    Home page (with 50 states link) → State pages (parent category with links to child categories) → City pages (child categories with links to post titles) → Local Service pages (posts)

    A user has to pick the state, and then select city to see all the available services in that city, and then click on a service business link to be able to go to a specific local services page.

    URL structure would be -

    - http://www.domain.com/california/san-francisco/local-service-page1
    - http://www.domain.com/texas/san-antonio/local-service-page4
    etc.

    Below are list of challenges I am facing currently:

    - How to populate parent category page with all related hyperlinked categories
    - How to populate child category pages with all related hyperlinked post titles
    - What is the best combination of Page / Post format to achieve the above workflow?
    - How to add parent / child category for each post in bulk

    I have tried without much success, so your help and insights will be greatly appreciated.

    Thanks in advance.

  5. bbhank
    Member
    Posted 1 year ago #

    Here's the problem: We have businesses that are the same kind of business. They are in cities which are in states which are in countries. There is no need for all countries to be listed, just the ones used, say 10 to 20. They need to be selected by state-city-business with the latter being their page. There are no parent-child relationships. All pages are necessarily the same except for Home and it is along the same theme.

    I had thought at first of what seemed to be an easy way to do this with WordPress Tags and Categories but no menu has been found that will enable this drill down and no plug in or method to incorporate these en mass into pages or theme driven by state, city. That would be THE best way and one supported by WordPress. If you know how to do this, succinct, complete, direction would be appreciated.

  6. bbhank
    Member
    Posted 1 year ago #

    Here's a piece from another creator with the same issue and no answer. I used some of this code when I started. This is the first step. I will post my results also when workable framework is done. The code can be cascaded.

    We are using Custom Post Types for directory listings - store locations. We are creating a page for every state, but then want to list all the cities that have a location. We are using meta key/values for the address data. Every store location has: name, address, city, state, zip.

    If you are viewing the California page, I want to make a list of all the cities that actually have locations in there. For example, if we had locations in San Diego & Los Angeles, but not San Francisco, we do not want to show San Francisco. The idea is that it will automatically update the list of cities as we add addresses and we don't have to manually make the city list.

    Anyway, I am trying to do a custom query that:

    a) Finds all the locations where the meta_key=California
    b) Within this result set, find all the unique city names

    So far I have:

    $querystr = "
    	SELECT wposts.ID
    	FROM $wpdb->posts wposts
    		LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
    	WHERE wpostmeta.meta_key = 'state'
    		AND wpostmeta.meta_value = '".get_the_title($post->ID)."'
    	ORDER BY wpostmeta.meta_value ASC
    	";

    This only pulls all the locations within a state ( get_the_title($post->ID) returns the name of the state as each state has it's own page).

    How can I take this query one step further and pull all the unique cities within the state?

  7. bbhank
    Member
    Posted 1 year ago #

    Here is some of the code I took to make the start of a dropdown. This pulls the address data associated with a list of names, in this case, page names - businesses:

    <TABLE BORDER="0"><TR><TD WIDTH="350">
    <FONT SIZE="1" COLOR="#123456"><B>Located at</B></FONT>
    <BR>
    <?php
    $username="your user name";
    $password="your password";
    $database="your database name";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $pageName=get_the_title();
    $query="SELECT * FROM your-table-name WHERE field-you-want='$pageName'";
    $result=mysql_query($query);
    
    $num=mysql_numrows($result);
    
    mysql_close();
    ?>
    
    <ul>
    <?php
    $i=0;
    while ($i < $num) {
    
    $f1=mysql_result($result,$i,"Address");
    $f2=mysql_result($result,$i,"City");
    $f3=mysql_result($result,$i,"State");
    $f4=mysql_result($result,$i,"Zip");
    $f5=mysql_result($result,$i,"Phone");
    ?>
    <B><?php echo $f1,'<BR>',$f2,', ',$f3,'   ',$f4,'<BR>',$f5; ?></B>
    
    <?php
    $i++;
    }
    ?>
  8. bbhank
    Member
    Posted 1 year ago #

    So what I did was to use a form and let php get the table data. Basicaly repeating the process. The fields are named State, City, and Name - name of business. The issue here is to take that last selected result to where it opens the selected WordPress "page":

    <?php
    mysql_connect('server', 'username', 'password');
    mysql_select_db('databasename');
    ?>
    <form action="" method="post">
    <select name="state">
    <?php
    $state_query = "SELECT DISTINCT State FROM tablename ORDER BY State ASC";
    $state_result = mysql_query($state_query);
    while($state = mysql_fetch_array($state_result)) {
    if($_POST['state'] == $state['State'] ) {
    echo '<option selected value="' . $state['State'] . '">' . $state['State'] . '</option>';
    } else {
    echo '<option value="' . $state['State'] . '">' . $state['State'] . '</option>';
    }
    }
    ?>
    
    </select>
    
    <input type="submit" name="submit" value="State" /><br /><br />
    <select name="city">
    <?php
    
    if($_POST['submit']) {
    echo $_POST['state'];
    $city_query = "SELECT DISTINCT City FROM tablename WHERE State = '{$_POST['state']}' ORDER BY City";
    $city_result = mysql_query($city_query);
    while($city = mysql_fetch_array($city_result)) {
    echo '<option value="' . $city['City'] . '">' . $city['City'] . '</option>';
    }
    }
    
    ?>
    </select>
    
    <input type="submit" name="submit" value="City" /><br /><br />
    <select name="place">
    <?php
    
    if($_POST['submit']) {
    echo $_POST['city'];
    $city_query = "SELECT City, Name FROM tablename WHERE City = '{$_POST['city']}'";
    $city_result = mysql_query($city_query);
    while($city = mysql_fetch_array($city_result)) {
    echo '<option value="' . $city['City'] . '">' . $city['Name'] . '</option>';
    }
    }
    
    ?>
    </select>
    </select>
    <input type="submit" name="submit" value="Submit"><br /><br />
    
    </form>

    This assumes you have the fields named as they are. You'll need to fill in your server and logon info along with the table name you're pulling the data from. Conveniently using the normal two letter state IDs serves as state codes well without any conversion. Later a method of actually seeing the full state name might be useful but now nuts and bolts. This works to the point of the submission of the result of the last drill down and needs action scripts of whatever sorts, in this case open a WordPress page.

  9. bbhank
    Member
    Posted 1 year ago #

    The name of the submit is "place".

    After some serious searching, this answer was found to be workable:

    <?php
    $my_title = $_POST['place'];
    global $wpdb;
    $mypost = $wpdb->get_row( "SELECT * FROM wp_posts WHERE post_title = '" . $my_title . "' " );
    $permalink = get_permalink($mypost->ID);
    echo "<a href=$permalink>Click Here to Go to Selection</a>";
    ?>

    Once the selected option is submitted, this will get the needed page, provided the name in the selection matches a WordPress page.

    A very workable answer to a legitimate question.

Topic Closed

This topic has been closed to new replies.

About this Topic