php and mysql dynamic drop down default selected.

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]
newbe101Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZylochCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ODBACommented:
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
?>
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

neorushCommented:
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]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
waygoodCommented:
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>
newbe101Author 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.
newbe101Author 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.
newbe101Author Commented:
figured it out thanks to neorush.  I had to add a couple more varialbles and got it working... thanks neorush for the example.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.