Need help selecting values from a MySQL populated dropdown menu :)

[manuelV]

Hello, I realize that I just joined your great site, but I have searched everywhere I could and just can't find a solution to this. Any help is appreciated

 

I will try to make this as clear as I can!

 

Basically I have a drop down menu, which is populated by MySQL columns, and that works great!

Now, what I'm trying to accomplish is for people to be able to edit the forms the previously inputted (I got all the forms to work, including the radio buttons, I'm just stuck on this drop down). They have a list of all the clients they can edit, they click on the name of the client and they get sent to a page that displays the forms, with all the info in them. They get sent to: eg, "/workOrder.php?workOrderID=42"

 

I have two SQL tables that I'm dealing with for this example, one is workOrder, and the other is clients. Inside the workOrder table we have a clientID column, which tells us which client has which workOrder (each client may have more than one work order). When they add a new work order, they have the dropdown with the list of all the clients names, and they pick the corresponding client. So when they go to edit, it needs select that client name from the drop down.

I just have no idea how to go about this (I am a beginner programmer, so excuse me for any poor coding)

 

Here's the code for the drop down that I have.

 

<?php
// connect to the DB
$con=mysql_connect(localhost,username,password);
@mysql_select_db(database) or die( "Unable to select database");

$sql="SELECT * FROM workOrder WHERE workOrderID = $workOrderID"; //this selects the work order row corresponding to the ID from "/workOrder.php?workOrderID=42"
$result=mysql_query($sql); 
$sql="SELECT * FROM clients ORDER BY clientName ASC"; //and this selects the client names/ID, for the drop down menu
$result2=mysql_query($sql); 


while ($row=mysql_fetch_array($result2)) {
$id=$row["clientID"];
$name=$row["clientName"]; 
$options.="<OPTION VALUE=\"$id\">".$name.'</option>';
}
?>
<SELECT NAME=workOrderClients><?=$options?></SELECT>

 

I know I have to use the "selected" function for the <option> tag, but I'm unsure how to implement this, as I only have one <option> tag.

 

Again, thanks for any help. I just don't really know where to even start :s

[voip03]

$options.="<OPTION VALUE=\"$id\">".$name.'</option>';

use echo

echo "<OPTION VALUE=\"$id\">".$name.'</option>';

 

http://www.w3schools.com/TAGS/tryit.asp?filename=tryhtml_option_value

[WebStyles]

you need to compare the value you're putting into the <option> with the one they inputted.

 

instead of:

$options.="<OPTION VALUE=\"$id\">".$name.'</option>';

try

$options.='<option value="$id"';
if($_POST['workOrderClients'] == $id) echo ' selected';
$option .= '>'.$name.'</option>';

[manuelV]

you need to compare the value you're putting into the <option> with the one they inputted.

 

instead of:

$options.="<OPTION VALUE=\"$id\">".$name.'</option>';

try

$options.='<option value="$id"';
if($_POST['workOrderClients'] == $id) echo ' selected';
$option .= '>'.$name.'</option>';

The problem is, "$_POST['workOrderClients']" doesn't seem have a value, as its not getting it from anywhere. I'm not sure if $_POST is the right thing to use.

Also, correct me if I'm wrong, but shouldn't the 'if' have the {} tags? and shouldn't the last "$option" be "$options" aswell? As $option just screwed everything over

 

I went ahead and replaced the $_POST with a straight ID number "159" (which is an existent client ID) and that should in theory select that certain client, right? But it just printed ' selected' right beside the dropdown menu, as if it's not inside the <option> tag.. and when I put a non existent ID number, it didn't print anything.

 

[WebStyles]

The problem is, "$_POST['workOrderClients']" doesn't seem have a value, as its not getting it from anywhere. I'm not sure if $_POST is the right thing to use.

use $_POST if your form method is post, $_GET if form method is get, and $_REQUEST if you're not sure.

 

Also, correct me if I'm wrong, but shouldn't the 'if' have the {} tags?

not needed here, because it's only 1 statement.

 

and shouldn't the last "$option" be "$options" aswell? As $option just screwed everything over

$option is obviously a typo. should be $options

 

 

[manuelV]

Alright, this is what I have now.. It's not really doing anything though... Am I on the right track at least?

 

<?php
include("config.php");
$con=mysql_connect(localhost,$username,$password);
@mysql_select_db(frontstep) or die( "Unable to select database");

$sql="SELECT clients.clientID,clients.clientName, workOrder.clientID FROM clients, workOrder WHERE clients.clientID = workOrder.clientID";
$result2 = mysql_query($sql);

$sql="SELECT clients.clientID,clients.clientName, workOrder.clientID FROM clients INNER JOIN workOrder ON clients.clientID = workOrder.clientID WHERE workOrder.workOrderID = $workOrderID ";
$result3 = mysql_query($sql);

$row = mysql_fetch_array($result3);
$selectedID = $row["clientID"];
// echo $selectedID; echos the correct ID, so I'm not sure why it's not doing anything..
while ($row=mysql_fetch_array($result2)) {
$id=$row["clientID"];
$name=$row["clientName"]; 
$options.="<option value='$id'";
if($id == $selectedID)" SELECTED";
$options.= " >".$name."</option>";
}
?>
<a><SELECT NAME=workOrderClient><?=$options?></SELECT></a>

 

[WebStyles]

There's no way that code was working.... I've corrected a few things, but haven't tested it.

(you should find out where your logs are and make a habit of checking them while coding, it will make your life easier)

 

* comments in code:

<?php
include("config.php");
// localhost needs to be enclosed in quotes
$con = mysql_connect("localhost",$username,$password);
// added $con to mysql_select_db parameters
mysql_select_db("frontstep",$con) or die( "Unable to select database");

// assuming this is correct, I have no way of knowing
$sql1="SELECT clients.clientID,clients.clientName, workOrder.clientID FROM clients, workOrder WHERE clients.clientID = workOrder.clientID";
// added connection resource
$result2 = mysql_query($sql1,$con);
// also, no way of knowing if this is correct, although it seems a bit "MUCH" if all you want is the clientID
$sql2="SELECT clients.clientID,clients.clientName, workOrder.clientID FROM clients INNER JOIN workOrder ON clients.clientID = workOrder.clientID WHERE workOrder.workOrderID = '$workOrderID'";
// added connection resource again
$result3 = mysql_query($sql2,$con);

$row = mysql_fetch_assoc($result3);
$selectedID = $row["clientID"];

while ($row=mysql_fetch_assoc($result2)) {
        // just changed a few things and added $options.= before ' selected'
$options.='<option value="'.$row["clientID"].'"';
if($row["clientID"] == $selectedID) $options .= ' SELECTED';
$options.= '>' . $row["clientName"] . '</option>';
}
?>
<select name="workOrderClient"><?php echo $options;?></select>

 

try running this code and check your error logs! (if you're using mamp, xamp, or wamp, you'll have a folder somewhere in there called logs, if your on a linux server, check in /var/log or similar)

[manuelV]

Hmm, I tried the code and adding the $options.= seems to have helped a bit. However, it only ever selects the very last option for some reason :s

 

Also, I'm not entirely sure how to access the server log at the moment. (I'm using FTP to access the files) But I'll let you know if I find out how

[manuelV]

 

We got it to work!!! (there was 2 of us trying to figure this out )

 

Here's the code, thanks very much for your help! MUCH appreciated!

 

<?php
$row = mysql_fetch_array($result3);
$selectedID = $row["clientID"];


while($row=mysql_fetch_array($result2)){
$option .= "<option value = '{$row['clientID']}'";
if ($row['clientID']==$selectedID) $option.= " selected";
$option .= ">" . $row['clientName'] . "</option>";
}
?>

[manuelV]

and used this for selecting the SQL

 

$sql="SELECT * FROM clients ORDER by clientName";
$result2 = mysql_query($sql);
$sql="SELECT clients.clientID,clients.clientName, workOrder.clientID FROM clients, workOrder WHERE workOrder.clientID = clients.clientID AND workOrder.workOrderID = $workOrderID ";
$result3 = mysql_query($sql);

 

Like WebStyles mentioned, the INNER JOINs were a bit 'much'. This does the exact same thing, and isn't nearly as 'complex'