php and mysql dynamic drop down default selected.

newbe101
newbe101 used Ask the Experts™
on
Hi All,

I have created a dynamic drop down list in php that I am using for editing records.  I want the initial value of the select box to be what ever was entered when the record was created.  I have some code, but the default is always the last record in the database... please help.

[CODE]

<?php
include("connect.php");
$id=$_POST['id'];
@mysql_select_db($db) or die( "Unable to select database");
//Vendor select
 $sql="SELECT id, company FROM vendor";
 $result=mysql_query($sql);
 $options="";
 while ($row=mysql_fetch_array($result)) {
     $id=$row["id"];
     $company=$row["company"];
     $options.="<OPTION VALUE=\"$id\">".$company;
}
mysql_close();
//End Vendor
?>

<SELECT NAME=vendor><OPTION VALUE=0><? echo "$company"; ?><?=$options?></SELECT>

[/CODE]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004

Commented:
Is the id you want selected in $_POST['id']? If so, you only need to do:

while ($row = mysql_fetch_array($result)) {
    $cid = $row['id'];
    $company = $row['company'];
    $options .= '<option value="' . $cid . '"';
    if ($cid == $id) {
        $options .= ' selected="selected"';
    }
    $options . '>' . $company . '</option>';
}
mysql_close();
//End Vendor
?>

<select name="vendor"><option value="0"><? echo $company; ?><?=$options?></select>
Ryan ChongSoftware Team Lead

Commented:
something like this:


<?php
include("connect.php");
$pid=$_POST['id'];
@mysql_select_db($db) or die( "Unable to select database");
//Vendor select
 $sql="SELECT id, company FROM vendor";
 $result=mysql_query($sql);
 $options="";
 while ($row=mysql_fetch_array($result)) {
     $id=$row["id"];
     $company=$row["company"];

if ($id == $pid) {
     $options.="<OPTION VALUE=\"$id\" selected>".$company;
} else {
     $options.="<OPTION VALUE=\"$id\">".$company;
}

}
mysql_close();
//End Vendor
?>

<SELECT NAME=vendor><OPTION VALUE=0><? echo "$company"; ?><?=$options?></SELECT>
Jason MintonEpic ODBA

Commented:
my turn:

<?php
include("connect.php");
$id=$_POST['id'];
@mysql_select_db($db) or die( "Unable to select database");
//Vendor select
 $sql="SELECT id, company FROM vendor";
 $result=mysql_query($sql);
 $options="";
 while ($row=mysql_fetch_array($result)) {
     $id=$row["id"];
     $company=$row["company"];
     $options.="<OPTION VALUE=\"$id\"";
     $options.="selected" if ($id eq $cid);
     $options.=">".$company."</OPTION>";
}
mysql_close();
//End Vendor
?>
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Cleaner less memory intensive, if you had a ton of vendors you could eat through PHP's memory with the above, granted a doubt its likely for you...but its something to keep in mind, also don't be afraid to make array calls, and you'll find one echoing HTML using single quotes rather than doubles will save you some serious back slash typing, and as jasonsbytes points out, make sure you close your <option> tags, although most browsers are forgiving of this, in unique situations you'll get some pretty funky results.

[CODE]

<?php
      include("connect.php");
      $id = $_POST['id'];
      @mysql_select_db($db) or die( "Unable to select database");
      $sql = "SELECT `id`, `company` FROM `vendor`";
      $result = mysql_query($sql);
      
      echo '<select name="vendor">';
      while ($row=mysql_fetch_array($result)) {
            $selected = '';
            if($row["id"]==$_POST["id"]){
                  $selected = ' selected';
            }
            echo '<option value="'.$row["id"].'" '.$selected.'>'.$row["company"].'</option>';
      }
      echo '</select>';
      mysql_close();
      //End Vendor
?>

[/CODE]

Commented:
I use the following, which also uses htmlentities incase the values contain html values.

<?php

function run_mysql_query($query, $limit="")  
{  
      // call using $result=run_mysql_query("SELECT * FROM table","3,6");
      // limit 3 means give me first 3
      // limit 3,6 means ignore first 3 then give me next 6
      // returned mysql link, so we use mysql_fetch_assoc($result); to retrieve a row (see above)

      $location='';
      $user='';
      $password='';
      $db_name='';

    if(!empty($query))  
    {
            if (trim($limit)!="")
            {
                  $query .= " LIMIT " . $limit;
            }

            // Create database connection
            $connection = mysql_connect($location, $user, $password) or die ("Couldn't connect to database");

            // Select the database
            $db = mysql_select_db($db_name, $connection) or die ("Couldn't select database");

            // Execute SQL query and get result
            $link = mysql_query($query, $connection) or die ("Couldn't execute query ".$query);

            return $link;  
    }
      return FALSE;
}

function drop_down_box_options($sql,$default="",$all=null,$all_value=0)
{
      // SQL statement gets option value and display value first. ie "SELECT user_id, user_name FROM users"
      $output='';
      if(isset($all))
      {
            $output.= '<option';
            $output.= ' value="'.htmlentities($all_value).'"';
            if ($default==$all_value)
            {
                  $output.= ' selected="selected"';
            }
            $output.= '>' . htmlentities($all);
            $output.= '</option>';
            $output.= "\n";
      }

      $result=run_mysql_query($sql);
      if($result)
      {
            while($row=mysql_fetch_row($result))
            {
                  $output.= '<option value="'.htmlentities(stripslashes($row[0])).'"';
                  if ($default==$row[0])
                  {
                        $output.= ' selected="selected"';
                  }
                  $output.= '>';

                  $output.= htmlentities(stripslashes($row[1]));
                  $output.= '</option>';
                  $output.= "\n";
            }
      }
      return $output;
}
?>

<select name="user">
<?php echo drop_down_box_options("SELECT user_id, user_name FROM users", $current_user_id); ?>
</select>

Author

Commented:
I tried the code from neorush, jasonsbytes and ryancys.  They all created the select box, but none of them really worked.  Let me be a little more clear; I have 2 tables: option and vendor.  Each option needs a vendor.  The vendor records have an id and company fields (along with others of course).  When you add an option, the vendor field (under option) is a select box that lists the company fields (from the vendor table).  When you pick a vendor, it stores the vendor id in the vendor field of the option table (this is working just fine).  Now, if you edit an option record, I need the vendor field (in option) to be a dynamic select box with the appropriate vendor selected by default.  If no vendor was selected when the option record was created, then the default should be blank.  If the vendor field is changed, then vendor field (in option) needs to store the appropriate id for the selected vendor.

You probably got this already from my original post, but the code I tried didn't work.  They all created the dynamic select box (which I already made), and the defaulted a vendor company, but they didn't match the id of the original record.... I hope this makes sence.

thanks again.

Author

Commented:
The code from neorush is very close, except that the dropdown select box is defaulting the wrong vendor... maybe there is a variable conflict or something.

Author

Commented:
figured it out thanks to neorush.  I had to add a couple more varialbles and got it working... thanks neorush for the example.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial