Is there a way to have a form select, read back to the database and auto fill selections?

OGBugsy · April 10, 2015

Hello, new to the forum and was hoping to find a little help.

I have a database and form set that I built with MySQL, html and php. I can submit new data, search and view the db fine from all the forms.

what I am trying to do is have a form select box auto fill with the contents of a table/row. like an "active" select box

example:

I have a workorder form and I would like the form to have a select box that can choose from a list of materials that are populated in another table. So When I add a new material, it will show up in the forms select box.

is there a way to accomplish this?

Many thanks in advance.

OGBugsy

Barand · April 10, 2015

I am assuming you have a table of materials. EG

+----------------------+
| id | material        |
+----+-----------------+
|  1 | wool            |
|  2 | steel           |
|  3 | paper           |
+----+-----------------+

Create a function to build the options from the table

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

function materialOptions($db) {
    $opts = "'<option value=''>- select material -</option>\n";
    $sql = "SELECT id, material
            FROM material
            ORDER BY material";
    $res = $db->query($sql);
    while (list($id, $mat) = $res->fetch_row()) {
        $opts .= "<option value='$id'>$mat</option>\n";
    }
    return $opts;
}

Then

<html>
    <body>
    
    <select name='material'>
        <?php echo materialOptions($db); ?>
    </select>
    
    </body>
</html>

OGBugsy · April 11, 2015

Thank you for the response and the code.

I decided to try the code on the simplest table I have "salesmen". I would like the selectbox to pull up the salesmen "last_name" and "first_name", did I edit the code correctly?

+----------------------+------------+
| id | first_name      |  last_name |
+----+-----------------+------------+
|  1 | dave            |  walker    |       
|  2 | pete            |  smith     |
|  3 | sam             |  jones     |
+----+-----------------+------------+

$db = new mysqli(localhost,XXXXXXXX,XXXXXXXXX,XXXXXXXX);

function salesmenOptions($db) {
    $opts = "'<option value=''>- select salesmen -</option>\n";
    $sql = "SELECT last_name, first_name
            FROM salesmen
            ORDER BY last_name";
    $res = $db->query($sql);
    while (list($last_name, $first_name) = $res->fetch_row()) {
        $opts .= "<option value='$last_name'>$first_name</option>\n";
    }
    return $opts;
}

<html>
    <body>
    
    <select name='salesmen'>
        <?php echo salesmenOptions($db); ?>
    </select>
    
    </body>
</html>

I have put the code into my html doc with no problem. But, I am having trouble creating the "function" in phpmyadmin (I have never done this before). I tried to use the "Routines" tab to create the function but I am having no luck. I honestly tried to figure it out thru google searches but I am at the hair pulling point atm. Any help in pointing me in the right direction or tips on creating the function with phpmyadmin would be very appreciated.

Thanks,

OGBugsy

Edited April 11, 2015 by OGBugsy

OGBugsy · April 11, 2015

Here is another attempt at it, just trying to populate with "last_name" only.

$db = new mysqli(localhost,xxxxx,xxxxx,xxxxx);

function salesmenOptions($db) {
    $opts = "'<option value=''>- select last_name -</option>\n";
    $sql = "SELECT id, last_name
            FROM salesmen
            ORDER BY last_name";
    $res = $db->query($sql);
    while (list($id, $las) = $res->fetch_row()) {
        $opts .= "<option value='$id'>$las</option>\n";
    }
    return $opts;
}

thanks,

OGBugsy

Barand · April 11, 2015

The function would go in your PHP file with the HTML output, not in phpMyAdmin.

example.php:

<?php

$db = new mysqli(localhost,XXXXXXXX,XXXXXXXXX,XXXXXXXX);

function salesmenOptions($db) {
    $opts = "'<option value=''>- select salesmen -</option>\n";
    $sql = "SELECT id, last_name, first_name
            FROM salesmen
            ORDER BY last_name";
    $res = $db->query($sql);
    while (list($id, $last_name, $first_name) = $res->fetch_row()) {
        $opts .= "<option value='$id'>$first_name $last_name </option>\n";
    }
    return $opts;
}

?>    
<html>
<head>
<title>Example</title>
</head>
<body>
    <select name='salesmen'>
        <?php echo salesmenOptions($db); ?>
    </select>
</body>
</html>

OGBugsy · April 11, 2015

You sir, are a Saint !!

That is exactly what I was hoping for and I understand it a lot better now to boot. Many thanks and a bunch of good karma your way!

PS - I am from Texas but I visited Portsmouth Harbor in 1984 via the US Navy. Very beautiful country you have there. I always dreamed of taking my wife back. Hopefully someday. Cheers!

Sign In

Is there a way to have a form select, read back to the database and auto fill selections?

Recommended Posts

OGBugsy

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

OGBugsy

Link to comment

Share on other sites

OGBugsy

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

OGBugsy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information