Jump to content

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

OGBugsy

By OGBugsy
in PHP Coding Help

Recommended Posts

OGBugsy Newbie

OGBugsy

Posted
Posted

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 Prolific Member

Barand

Posted
Posted

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 Newbie

OGBugsy

Posted
  • Author
Posted

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

OGBugsy Newbie

OGBugsy

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

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 Newbie

OGBugsy

Posted
  • Author
Posted

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!

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.