Populating a drop-down menu with ENUM data from an SQL column...?

[etymole]

Hi

As the title suggests my problem is that I'm trying to get some of the enumerated fields (e.g. Sex, Age range, etc) to link into my drop down boxes in my script. I also want to be able to update the specific selection for each record with the drop down box.

I have managed to get this populate and update idea working using text form fields but I'm having a real issue trying to figure out how to get it working for a drop down menus and combo boxes.
I'm assuming a while loop, looping through the options available and creating a new <option> each time.
I have seen code snippets that do this which I've been trying to use, but these snippets refer to an entire table, I just want to select the enum options from a specific column.

Below is the snippet I think I could use if only I could get the query correct.

<form method="get" action="view_urls.php">
<select name="type">
<option value="NULL">Choose a Category:</option>
';

// Retrieve and display the available categories.
$query = 'SELECT * FROM url_categories ORDER BY category ASC'; //POSSIBLE TO MAKE THIS QUERY SELECT ONLY THE ENUM OPTIONS????
$result = mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {



echo "<option value=\"$row[0]\">$row[1]</option>
";
}

// Complete the form.
echo '</select>
<input type="submit" name="submit" value="Go!">
</form>

I've been racking my brains out trying to figure this out...
I'm pretty new to PHP and SQL and this is a little out of my grasp right now.
I appreciate all help. 🙂

[niroshan]

Hi etymole,

take a look at here.

refer to the yankee at gmeil dot com comment.

Regards,
niroshan

[etymole]

niroshan;10949772 wrote:

Hi etymole,

take a look at here.

refer to the yankee at gmeil dot com comment.

Regards,
niroshan

Thanks Niroshan
That little snippet of code is great. However, I'm having trouble getting it to work with my form as I have single quoted it (following a book).

As it's quoted I cant get the foreach loop to work, although everything else works.

The $row and $row2 mentioned are declared before the form is started.

// Create the form.
	echo '<h2>Edit a User</h2>




<form action="edit_user.php" method="post">
<table><tr><td><p>First Name: </td>
<td><input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '"/></p></td></tr>

<tr><td><p>Last Name: </td>
<td><input type="text" name="last_name" size="15" maxlength="30" value="' . $row[1] . '"/></p></td></tr>
<tr><td><p>Email Address: </td>
<td><input type="text" name="email" size="20" maxlength="40" value="' . $row[2] . '"/> </p></tr>
<tr><td><p>Sex: </td>
<td>

while ($row2=mysql_fetch_row($result2))
{
   <select name=sex>
   foreach(explode("','",substr($row2[1],6,-2)) as $v)
   {
     print("<option>$v</option>");
   }
   </select>
}

<tr><td><p>About me: </td>
<td><input type="text" name="about_me" size="20" maxlength="40" value="' . $row[3] . '"/> </p></tr></table>
<p><input type="submit" name="submit" value="Submit" class = "submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="' . $id . '" />



</form>';

I know this is a very scrappy excuse, I do apologise, I am new to PHP and haven't quite grasped quotes properly yet.
The snippet works perfectly outside of the form, however, I need it to be within the form to be submitted when everything else is.

Can you help me?

I wonder if I'm just missing a few quotations marks myself and thats all there is to get the while and foreach loops to work within the quotes...:-s

P.S. I'm half Sri Lankan. My family are from Negombo. :-)

[etymole]

Anyone?
This is really getting to me, I've had a few other issues regarding single and double quotes with forms and I'm getting really frustrated by it, and can't seem to figure it out.

So, any help would be ever so appreciated...

[halojoy]

You split your echo into 2 parts.
Using format
echo 'text';
for both parts.

You make one echo before
and
one echo after
the while loop.

echo 'xxxxxx';

while(){


}

echo 'yyyyy';

[etymole]

I've tried what you suggested with different variations. Now, I don't get any parse errors, but the drop down just won't display...I'm really baffled, I've substituted the print for another echo, I've taken the while and foreach loops out of the echo quotes. (Doing just the while gave me an unexpected '<' error for the <select>

	// Create the form.
	echo '<h2>Edit a User</h2>




<form action="edit_user.php" method="post">
<table><tr><td><p>First Name: </td>
<td><input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '"/></p></td></tr>

<tr><td><p>Last Name: </td>
<td><input type="text" name="last_name" size="15" maxlength="30" value="' . $row[1] . '"/></p></td></tr>
<tr><td><p>Email Address: </td>
<td><input type="text" name="email" size="20" maxlength="40" value="' . $row[2] . '"/> </p></tr>
<tr><td><p>Sex: </td>
<td>';
while ($row2=mysql_fetch_row($result2))
{
   echo' <select name=sex>';
   foreach(explode("','",substr($row2[1],6,-2)) as $v)
   {
     <option>$v</option>
   }
    </select> 

}
echo '
<tr><td><p>About me: </td>
<td><input type="text" name="about_me" size="20" maxlength="40" value="' . $row[3] . '"/> </p></tr></table>
<p><input type="submit" name="submit" value="Submit" class = "submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="' . $id . '" />



</form>'; 

[halojoy]

Yes, I see.
There are several places to put in echo:
echo '<option>' . $v . '</option>';
}
echo '</select>';

Now is only to hope the MySQL $result really have those <option>

echo '
<form action="edit_user.php" method="post">
<table><tr><td><p>First Name: </td>
<td><input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '"/></p></td></tr>

<tr><td><p>Last Name: </td>
<td><input type="text" name="last_name" size="15" maxlength="30" value="' . $row[1] . '"/></p></td></tr>
<tr><td><p>Email Address: </td>
<td><input type="text" name="email" size="20" maxlength="40" value="' . $row[2] . '"/> </p></tr>
<tr><td><p>Sex: </td>
<td>';

while ($row2 = mysql_fetch_row($result2))
{
	echo ' <select name="sex">';
	foreach(explode("','",substr($row2[1],6,-2)) as $v)
	{
		echo '<option>' . $v . '</option>';
	}
	echo ' </select>';
}

echo '
<tr><td><p>About me: </td>
<td><input type="text" name="about_me" size="20" maxlength="40" value="' . $row[3] . '"/> </p></tr></table>
<p><input type="submit" name="submit" value="Submit" class = "submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="' . $id . '" />
</form>';

[etymole]

Ok. I seem to have gotten it working using -

while ($row2=mysql_fetch_row($result2))
{
   echo '<select name="sex">';
   foreach(explode("','",substr($row2[1],6,-2)) as $v)
   {
     print("<option>$v</option>");
   }
   echo '</select>';
} 

With the while being out of the echo quotes.

I have one more issue however:
When selecting I cant get the drop down menu to fall on the current value within the sql table. So say if the options were male and female, it always falls on the first option, male.
Trying <option selected> doesnt work either because its within a loop.
I'm in the process of trying to get the drop down to have a "Please select:" option as the first option that will just re-submit the $row[] value when submit is pressed.
No luck so far.

[niroshan]

Hi etymole,

What your trying was right. You can use 'selected' to do this. What you need to do is check the post value against the DB value and if both matched then concatinate the selected to the print.

while ($row2=mysql_fetch_row($result2))
{
   echo '<select name="sex">';
   echo "<option value=\"0\">Please select</option>";   

   foreach(explode("','",substr($row2[1],6,-2)) as $v)
   {
     print "<option value=\"".$v."\"";
	 if(isset($_POST['sex']) && $_POST['sex']==$v)
	 	print ' selected';
	 print ">$v</option>";
   }
   echo '</select>';
}

By the way your missing the value property of the <option> tag. This is compulsory because when you post the form this value property will be assigned to $_POST array as the drop down box value.

One other thing, when you fetch the data from the database always try to use mysql_fetch_assoc because this will give you an associative array. The reason is that I have seen in your code you have used indexes ($row[0], $row[1] ..etc) and this makes your code hard to read without knowing the actual query.

If you read the link about the associative array u`ll understand what i mean 🙂

PS: hey i`m also from negombo 🙂

Best regars,
Niroshan