RE: [phplib] How can I populate a select box from a db query? From: Ignatius Teo (iteo <email protected>)
Date: 11/15/00

Bob,

Here's one way...

function DB_lookup($name,$sql,$js="",$size=1,$multiple=FALSE) {
/* make a HTML drop down selector given name of formvar, and sql statement to execute */
        // grab this to maintain state
        global $$name;

        $selected = isset($$name) ? $$name : "";

        $db = new web_DB;
        if (!$sql) return "";
        $db->query($sql);
        while ($db->next_record()) {
           $id = $db->f(0);
                if ($db->num_fields() > 1) {
                        $values[$id] = $db->f(1);
                } else {
                        $values[$id] = $id;
                }
        }

        $html = "<SELECT NAME=\"$name\"";
        if ($size!=1) $html .= " SIZE=\"$size\"";
        if ($multiple) $html .= " MULTIPLE";
        if ($js) $html .= " $js";
        $html .= ">\n";
        $html .= "<OPTION VALUE=\"\">&nbsp;-- none --&nbsp;</OPTION>\n";

        // list all the options
        if (!isset($values) || !is_array($values)) {
                $values = (array) $values;
        }
        if (is_array($values)) {
                while ( list( $value, $label ) = each( $values ) ) {
                        // printed SELECTED if an item was previously selected
                        // so we maintain the state
                        if ($selected == $value) {
                                $html .= "<OPTION VALUE=\"$value\" SELECTED>$label</OPTION>\n";
                        } else {
                                $html .= "<OPTION VALUE=\"$value\">$label</OPTION>\n";
                        }
                }
  }
        $html .= "</SELECT>\n";
        return $html;
}

Here's another way...

function makeDBDrop ($table,$id,$idname,$selection="") {
/* make a HTML drop down selector given table name, key and labelname*/
        $html = "";
        $dbox = new web_DB;
        $ddlist = "select $id,$idname from $table order by $table.$idname asc";
        $dbox->query($ddlist);
        $i = 0;
        while ($dbox->next_record()){
           $theId=$dbox->f($id);
           $theName=$dbox->f($idname);
           $html .= "<option value=\"$theId\"";
                if (empty($selection) && $i==0) {
                        // select the first item if nothing selected
                        $html .= " selected";
                } else {
                        if(!empty($selected)) {
                           if (is_array($selection)) {
                                         if (in_array($theId,$selection)) {
                                                $html .= " selected";
                                        }
                           } else {
                                        if ($theId==$selection) {
                                                $html .= " selected";
                                        }
                           }
                        }
                }
           $html .= ">$theName</option>\n";
                $i++;
  }
        return $html;
}

And yet another way...

function dropBox($name,$values,$selected,$js="",$size=1,$multiple=FALSE) {
/* make a HTML drop down selector given the form var name and an associative array of values */
/* you will need to run the database query outside of this function and pass to it an associative
array ie. ("label"=>"value","label"=>"value"....) */
        // grab this to maintain state
        global $$name;

        #$selected = isset($$name) ? $$name : "";

        $html = "<SELECT NAME=\"$name\"";
        if ($size!=1) $html .= " SIZE=\"$size\"";
        if ($multiple) $html .= " MULTIPLE";
        if ($js) $html .= " $js";
        $html .= ">\n";
        $html .= "<OPTION VALUE=\"\">&nbsp;-- none --&nbsp;</OPTION>\n";

        // list all the options
        if (!isset($values) || !is_array($values)) {
                $values = (array) $values;
        }
        if (is_array($values)) {
                while ( list( $label, $value ) = each( $values ) ) {
                        // printed SELECTED if an item was previously selected
                        // so we maintain the state
                        if ($selected == $value) {
                                $html .= "<OPTION VALUE=\"$value\" SELECTED>$label</OPTION>\n";
                        } else {
                                $html .= "<OPTION VALUE=\"$value\">$label</OPTION>\n";
                        }
                }
  }
        $html .= "</SELECT>\n";
        return $html;
}

The last method is preferable because you can dynamically change the array elements if you need to.

Hope this helps.

Ignatius

> -----Original Message-----
> From: White, Bob [mailto:rcwhite <email protected>]
> Sent: Thursday, 16 November 2000 00:15
> To: phplib <email protected>
> Subject: [phplib] How can I populate a select box from a db query?
>
>
> Hi All,
> How can I populate a select box from an oracle db query?
> Any help would be greatly appreciated.
> Bob White
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: phplib-unsubscribe <email protected>
> For additional commands, e-mail: phplib-help <email protected>
>