Php Function - Drop-down Based On Database Entries | Greg Bialowas. Full Stack Developer and Designer

This one is slightly trickier than the Array Based function, but at the same time it allows you to do a little more, too.

The function requires, as the name suggests, your values being stored in a database, so you will need to have a table in your database dedicated to that function. Well, not really dedicated, since this script can work with any table you want.

The Function


<?php
/**
 * dropMenuDB Function
 *
 * This function generates an HTML dropdown menu populated with data from a database table.
 * It simplifies the process of creating dynamic dropdowns with database values.
 *
 * @param string $f_name         - The name attribute for the dropdown.
 * @param mixed  $f_value        - The selected value for the dropdown.
 * @param string $f_table        - The name of the database table.
 * @param string $f_key          - The column name for option values.
 * @param string $f_val          - The column name(s) for option labels (comma-separated for multiple).
 * @param string $f_default      - Default text for the initial option.
 * @param string $f_whereclause  - Optional WHERE clause for database query (default is empty).
 * @param string $f_css          - Additional CSS classes for styling (optional).
 * @param string $f_tabindex     - The tabindex attribute for keyboard navigation (optional).
 * @param string $f_js           - Additional JavaScript attributes (optional).
 *
 * @return void Outputs the HTML for the dropdown menu.
 *
 * You are free to use this function without limits. If you include the name or both
 * the name and URL in your code, attribution to the original author is appreciated.
 *
 * Usage example:
 * dropMenuDB('deliverto', $row['deliverto'], 'staff', 'id', 'name', '- Select Staff -', 'jobtypeID = '5' && active > '0'');
 */
function dropMenuDB($f_name, $f_value, $f_table, $f_key, $f_val, $f_default, $f_whereclause='', $f_css='', $f_tabindex='', $f_js='')
{
    echo '<select name="' . $f_name . '" id="' . $f_name . '"';

    if ($f_css != '') echo ' class="' . $f_css . '"';
    if ($f_tabindex != '') echo ' tabindex="' . $f_tabindex . '"';
    if ($f_js != '') echo ' ' . $f_js;
    if ($f_whereclause != '') $wcl = ' where ' . $f_whereclause; else $wcl = '';

    echo '>';

    echo'<option value="">' . $f_default . '</option>
';

        $sql = mysql_query("select * from " . $f_table . $wcl . "") or die (mysql_error());
        while ($row = mysql_fetch_array($sql))
        {

echo '<option value="' . $row[$f_key] . '"';
            if ($row[$f_key] == $f_value)
            {
            echo ' selected';
            }

echo '>';

        /*  ***  find how many columns were inserted       ***  */
        $colElements = array();
        $counter = 0;

        $colElements = explode (', ', $f_val);
        $countit = count($colElements);

            while ($counter < $countit) {
              echo $row[$colElements[$counter]] . ' ';
              $counter++;
            }

echo '</option>
';
        }
    echo "</select>";
    /*  ***      dropMenuDB end     ***  */
}

For the example let's use one of the tables created for this very site. Let's display all the projects that are listed in the main page.

I have this WP table where all of my posts, pages, attachments etc. are stored.

Here it is:

ID	post_date	post_title	post_status	guid	post_type
4	2014-01-10	CPW Living	inherit	http://gregbialowas/uploads/cpw.jpg	attachment
5	2014-01-10	CPW Style	inherit	http://gregbialowas//uploads/cpwstyle.jpg	attachment
..	..	..	..	..	..
47	2014-03-03	me, Greg	publish	https://localhost/gregbialowas/?p=47	post
48	2014-03-03	Real Estate Manager	publish	https://localhost/gregbialowas/?p=48	post

(This is only a small exerpt from the table to give you a better picture of what's going to happen.)

First Example - selecting everything from the Project table:


<?php
dropMenuDB('gregs_projects', '', 'gb_posts', 'ID', 'post_title', '- Select Project -', '');

As you can see the function selected everything from the table including attachments, pages and even empty strings. HTML result:


<select name="gregs_projects" id="gregs_projects">
    <option value="">- Select Project -</option>
    <option value="4">CPW Living </option>
    <option value="5">CPW Style </option>
    <option value="6">Golden State Games </option>
    ...
    <option value="46">meGreg </option>
    <option value="47">me, Greg </option>
    <option value="48">Real Estate Manager </option>
    ...
    <option value="119">Skillset / Services </option>
    <option value="120"> </option>
    ...
</select>

We want to narrow the display to only Posts printed out in the landing page. These posts are marked in the db as 'publish' (in "post_status" column) and 'post' (in "post_type" column).

To narrow the display we're going to use the 7th parameter from the function's options, $f_whereclause.


<?php
dropMenuDB('gregs_projects', '', 'gb_posts', 'ID', 'post_title', '- Select Published Project -', 'post_status = 'publish' AND post_type = 'post'');

The drop down menu was reduced to posts only.

PRE-SELECTING output.

This function, same as the Array Based one allows you to pre-select one item directly on the first display, eg. highligting your country so the websurfer doesn't have to scroll the whole list down.

For doing so, we going to use the 2nd parameter which initialy stayed empty. Say we wan't to pre-select one of my other sites, meGreg. We already know that the ID of this site is 47 so as the second parameter we would use 47 and that's it!


<?php
dropMenuDB('47_selected', '47', 'gb_posts', 'ID', 'post_title', '- Pre-select Published Projects of mine -', 'post_status = 'publish' AND post_type = 'post'');

Note, how easy is to change the name of your <select> tag. HTML result:


<select name="47_selected" id="47_selected">
    <option value="">- Pre-select Published Projects of mine -</option>
    <option value="4">CPW Living </option>
    <option value="5">CPW Style </option>
    <option value="6">Golden State Games </option>
    ...
    <option value="46">meGreg </option>
    <option value="47" selected>me, Greg </option>
    <option value="48">Real Estate Manager </option>
    ...
    <option value="119">Skillset / Services </option>
    <option value="120"> </option>
    ...
</select>

Additional parameters.

By default the additional parameters are empty, so all you need to do is to fill the proper variable with your own input.

1. Add CSS to the dropdown


<?php
dropMenuDB('gregs_projects', '47', 'gb_posts', 'ID', 'post_title', '- Select Published Project -', 'post_status = 'publish' AND post_type = 'post'', 'free_db_class');

2. Tabindex.

If you want your form's elements to be focused in particular order, you can add a value as the next-to-last parameter of the dropdown function ($f_tabindex).


<?php
dropMenuDB('gregs_projects', '47', 'gb_posts', 'ID', 'post_title', '- Select Published Project -', 'post_status = 'publish' AND post_type = 'post'', '', '10');

3. inline JavaScript (jump menu)


<?php
dropMenuDB('gregs_projects', '', 'gb_posts', 'guid', 'post_title', '- Select Published Project -', 'post_status = 'publish' AND post_type = 'post'', '', '', 'onchange="document.location.href=this.value"');

Add all three Additional parameters at once


<?php
dropMenuDB('gregs_projects', '', 'gb_posts', 'guid', 'post_title', '- Drop w/ CSS, Tabindex and JS -', 'post_status = 'publish' AND post_type = 'post'', 'free_db_class', '15', 'onchange="document.location.href=this.value"');

Also see:

Drop Down based on an Array

PHP Function - drop-down based on database entries