SELECTED value for drop down list 1

[d0nny]

Again, not sure of this is a PHP question or a mySQL (SQL) question, but I'll give it a shot.
I have a database with 3 tables in it (i'm sure I could get some lessons on DB design as well!).

Two of the tables are simply reference tables for category_id and region_id. So in my main data table I reference an ID for both region and category from these two tables. This all works fine. I can print out the details of the data and include the text from these two reference tables using the ID.

The problem I now have is in editing a record in the data table. I have managed to get an edit page setup for the data and even have drop boxes for region and category, using the code below (the example is for the caegory):

<?php
	include ('/blah/blah/includes/mysql_connect.php');
	$query="SELECT id,category FROM category ORDER BY category";
	$result = mysql_query ($query);
		
	echo "Category: <select name='category'>";
	while($cat=mysql_fetch_array($result)){
		echo "<option value=$cat[id]>$cat[category]</option>";
	}
	echo "</select>";
?>

The problem with the above code is that it simply lists the options from the category table, rather the value stored in the main data table.

What I want is a list as above displayed on my edit page, but the value of these two fields (region and category) to be the value stored in the data table.
So the category_id stored in the data table might be 3, which relates to 'business' in the category table and I want that as the selected value in the edit form rather than just the first value from the select statement.

Make sense??

 

[Spork52]

Just retrieve the category_id from the data table and do a comparison in your while loop. If there is a match, then echo "selected" inside the option tag. Something like:

<option value="<? echo $category_id; ?>" <? if ($category_id == $data_category_id) {echo "selected";} ?>><? echo $category_name; ?></option>

although my variables and syntax are not the same as yours.

 

[jpadie]

i do it a slightly different way. but the same net result occurs

<?php
    include ('/blah/blah/includes/mysql_connect.php');
    $query="SELECT id,category FROM category ORDER BY category";
    $result = mysql_query ($query);
   [red] $defaultCatID = 3;[/red]
    echo "Category: <select name='category'>\r\n";
    while($cat=mysql_fetch_array($result)){
       [red] $select = ($cat['id'] == $defaultCatID) ? 'selected="selected"' : '';[/red[
        echo "\t<option value=\"$cat[id]\" [red]$select[/red]>$cat[category]</option>\r\n";
    }
    echo "</select>";
?>

 

[d0nny]

jpadie - is there some problems with some of the design/styling in your response?
I can't seem to get any of these working, but I see there are some [red] tags... I suspect this is where the text should be red (incorrect close tag).

But I can't understand the $select statement...

 

[d0nny]

Actually, I think there might be a misunderstanding of what I want to achieve here. I can see there is a defaultID of 3, but that isn't what I want.

I have a table of famous people and within that table there is a reference to the category table. So the famous person is say a Politician, which has a value of say 5. So 5 is stored in the People table and the category_id for 5 is Politician.

When I edit the record, I get all the details from the People table, but I don't want to display just a number for the category. I want to display what the category ID references, ie Politician. But I also want a list of the all the categories so I can change it if I need to.

At the moment, when i go to my edit form, I simply list all the choices from the category table with the edit form displaying the first option from the category table. What I want is for the choices from the category table to be displayed in the drop-down list, but with the current value from the People table displayed by default.

Does that make more sense?

 

[Spork52]

Read my answer again.

 

[d0nny]

Thanks Spork52.
The problem I have with your response is that there is a PHP statement within the code, but all of my current code is with a PHP section of my page.
I suppose the issue for is how to include the IF statement in my current code.
??

 

[Spork52]

echo "<option value=$cat[id];"
if ($category_id == data_category_id)
{
echo " selected";
}
echo ">$cat[category]</option>;"

 

[Spork52]

Well, I mixed up some of the variables and left off a $, but you get the idea.

So why does all the HTML have to be printed by the PHP?

 

[jpadie]

d0nny

yes - i was trying to highlight the changed bits for you using TGML (the tek-tips mark up language). i inverted the last square bracket.

the offending line should have been:

[red] $select = ($cat['id'] == $defaultCatID) ? 'selected="selected"' : '';[/red]

this is a use of the ternary expression. in each iteration of the loop it tests whether the value of $cat['id'] is the same as the default value that you have specified. if it is then $select becomes the xhtml attribute-value pair 'selected="selected"'. if it is not then it becomes a blank string. the result is printed inside the option xhtml tag.

 

[Spork52]

Cleaner:

if ($cat[id] != $data_category_id)
{
echo "<option value=$cat[id]>$cat[category]</option>";
}
else
{
echo "<option selected value=$cat[id]>$cat[category]</option>";
}

 

[jpadie]

Spork52: please post code within [ignore]

[/ignore] tags.

whilst i think that most browsers will accept your code for other readers i would warn that the correct xhtml attribute-value pair to select an option is 'selected="selected"'

Additionally, it is canonically correct to enquote all values in an attribute-value pair. so the value should be enquoted in the option tag. with double quotes.

from a php point of view whilst the if statement will work it will also through a E_NOTICE which may or may not get displayed depending on the php.ini settings. associative keys in an array MUST be enquoted unless they appear within a double quoted string. For an explanation see the manual section 'Why is $foo[bar] wrong?' here

 

[d0nny]

Thanks
Managed to get it working now. Many thanks for your help.