select option from <SELECT> based on record in mysql 1

[y2k1981]

Hi all,

I have a page which pulls data from a mysql table. What I want to know is, is it possible to select an option from a HTML <SELECT> based on the value of one of the fields? I know I could use case or if, but I'd was hoping there was an easier way?

 

[figmatalan]

If you are using PHP to build the page then you can build the select option list around the field you want rather than having a static list.

 

[y2k1981]

my God, that was a quick reply. That's a good idea but I don't know how it would work. The field is question is title (as in Mr etc). When adding new records I have a <SELECT> where the user selects the appropriate value. That doesn't come from another mysql table or anything. So if I were to dynamically build the select for the page I'm talking about (it's one where you can edit a record), PHP wouldn't have anywhere to get all the possible values, I guess it could loop through the title column in the table, but what if there just happened to be no Mrs's or something.

 

[figmatalan]

So this select statement is for peoples titles like Mr, Mrs, etc, etc?

If so, you can populate the select field with a loop, and include in the loop that if the option matches the field value that it is then selected automatically.

Or have I misunderstood? It's still early for me!

 

[y2k1981]

No, you understand right, and yeah, I think I see what you're getting at. create an array with the values Mr. Mrs. ... etc in it and then loop through the array to create the values for <OPTION>'s. But how would I tell it to select the value that matches the value I've just selected from the table?

 

[figmatalan]

assuming the option from the table is in $titleopt and your select array is in $selarray:

echo '<SELECT NAME="title">';
for ($counter=0; $counter < count($selarray); $counter++) {
if ($selarray[$counter] === $titleopt) {
echo '<OPTION SELECT VALUE='.$selarray[$counter].'>'.$selarray[$counter];
} else {
echo '<OPTION VALUE='.$selarray[$counter].'>'.$selarray[$counter];
}
}
echo '</SELECT';

This SHOULD work (can't check it here) but it'll loop through the array and if the field from the db matches the select box will automatically have that option selected.

 

[figmatalan]

OOOPS

Should be SELECTED not SELECT....

echo '<SELECT NAME="title">';
for ($counter=0; $counter < count($selarray); $counter++) {
if ($selarray[$counter] === $titleopt) {
echo '<OPTION SELECTED VALUE='.$selarray[$counter].'>'.$selarray[$counter];
} else {
echo '<OPTION VALUE='.$selarray[$counter].'>'.$selarray[$counter];
}
}
echo '</SELECT';

 

[y2k1981]

thanks for the post, I'll give that a go. You also forgot the closing widget on the closing select tag and the option tags now I'm just being picky !!

Just 2 questions as I'm quiet new to both PHP and MySQL. Why are you using ===? what does that do? From my JavaScript experience, = assigns a value, == checks for equality and === I've never seen before !! Also, what are the .'s for around the '$selarray[$counter] ?

thanks again for your reply. I'll give that a go and let you know what the outcome is, but I'm sure it'll work

 

[y2k1981]

sorry, I was wrong, you didn't forget the closing widgets in the option tags

 

[y2k1981]

ok, here's what I've got:

$db = mysql_connect("myhost", "user");



mysql_select_db("mydb",$db);



$result = mysql_query("SELECT * FROM employees",$db);

$selarray[0]= "Mr";
$selarray[1]= "Mrs";
$selarray[2]= "Miss";

$titleopt = ($_GET["title"]);

echo '<SELECT NAME="title">';
for ($counter=0; $counter < count($selarray); $counter++) {
  if ($selarray[$counter] === $titleopt) {
    echo '<OPTION SELECT VALUE='.$selarray[$counter].'>'.$selarray[$counter];
  } else {
    echo '<OPTION VALUE='.$selarray[$counter].'>'.$selarray[$counter];
  }
}
echo '</SELECT>';

but it's not working. Have I declared the array incorrectly or something?

thanks for all your help

 

[figmatalan]

the .'s append strings so:
echo '<OPTION SELECT VALUE='.$selarray[$counter].'>'.$selarray[$counter];

it will construct the string and where it goes '.$selarray[$counter].' it is basically putting the value from the array in there and then adding the rest of the string to the end.

=== means exactly equals and it checks the variable types, thinking about it you are best using == instead as your query could return a number.

 

[y2k1981]

me again !! still can't get this code to work, for some reason the if statement never equates to true, regardless of whether I use == or ===. I can't see why though

 

[figmatalan]

Put a few extra echo statements in your code to make sure it's making the database connection and to show the results.

Try:

$db = mysql_connect("myhost", "user");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT * FROM employees",$db);

$selarray = array("Mr", "Mrs", "Miss");

$titleopt = ($_GET["title"]);

echo 'TITLE PARAMETER: '.$titleopt."<BR>";

echo '<SELECT NAME="title">';
for ($counter=0; $counter < count($selarray); $counter++) {
  if ($selarray[$counter] === $titleopt) {
    echo '<OPTION SELECT VALUE='.$selarray[$counter].'>'.$selarray[$counter];
  } else {
    echo '<OPTION VALUE='.$selarray[$counter].'>'.$selarray[$counter];
  }
}
echo '</SELECT>';

 

[DRJ478]

It is important to catch possible MySQL errors:

$db = mysql_connect("myhost", "user") [COLOR=red]OR die("Cannot connect :".mysql_error())[/color];
mysql_select_db("mydb",$db) [COLOR=red]OR die("Cannot open database :".mysql_error())[/color];
$result = mysql_query("SELECT * FROM employees",$db);

Next: I don't see anywhere that a row from the table is retrieved - there's no mysql_fetch_.... function. That means the database code in this example is completely superfluos.

-> use foreach($arrayVar as $key => $value){...} instead of the for loop. It's cleaner and less typing.

-> when a compariosn fails constantly it is a good idea to inspect the two items being compared. How are they different? Is it whitespace? Case?

 

[y2k1981]

Ok, I found out where I was going wrong. the above example was just a simplified one to just test out the code. I didn't need a database, I just created the array and then when I called the page in the browser I added ?title=mr, etc. So I didn't need to have the $connect etc at all really.

But I did discover where I was going wrong. It's because in the URL the title was all lowercase, but in the array, it was in title case, ie Mr, Mrs etc. So you were spot on DRJ478!!. I wrapped the above code in strtolower() to compare it and it worked. It's now working perfectly, thanks for all your posts and sorry about all the unnecessary hassle