populate select box with mysql data

Question

Hi,

I have some trouble with this.

I have one database with the following tables: Countries -> All countries of the world are added Cities -> The cities are also added user_profile -> The profile of the user with the fields "country" & "city".

Everything works fine, even the populating of the boxes is working. But I don't know how to get the SELECTED value of the user for both country & city.

I have the following code:

Select Country: <br />
<select id="countries">
<option value="">--</option>

<?php
$sql = "SELECT country, title FROM countries ORDER BY title ASC";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['country']."\">".$row['title']."\n  ";
}
?>
</select>


Select City: <br />
<select id="cities">
<option value="">Select one</option>

<?php
$sql = "SELECT country, title FROM cities ".
"ORDER BY title ASC";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option class=\"".$row['country']."\"  value=\"".$row['title']."\">".$row['title']."\n  ";
}
?>
</select>

Now I need to check in the table user_profile which country & city he chose and show it to him.

I also use jchained (jquery plugin) to make both select boxes working together.

How can I achieve this? If I need to post anything else, please let me know.

Thanks in advance.

Answer 1

Do you just mean you don't know what to do after they select the values? If so then you would just use the form to post the values to the new page. On the receiving page you can run a query to Update the user's profile.

Sorry if my response isn't detailed enough ><

edit Oh, I may see what you mean now. You're able to store it correctly but you want the correct option to be flagged as selected if it's what the user has previously set? If that's it then you can query for the value, then inside of the while loop just add a check to see if $user_country == $row['country'] and if so echo ' SELECTED '. Hope that helps!

Answer 2

You have a lot of missing code here but I will try to help.

I am going to start by assuming that you have already stored the users selections in the user_profile table and that they are foreign keys to to correct relations.

To get the selected id's for repopulating the select boxes with the selected flag use:

SELECT country, city FROM user_profile where id = $user_id;

To get the text values you would do something like:

SELECT country.title as country, city.title FROM user_profile LEFT JOIN countries ON user_profile.country = country.id LEFT JOIN cities ON user_profile.city = cities.id WHERE user_profile.id = $user_id;

If you are not storing them as foreign key relations but instead by string title in the user_profile table you will need to do this:

SELECT country.title as country, city.title FROM user_profile LEFT JOIN countries ON user_profile.country = country.title LEFT JOIN cities ON user_profile.city = cities.title WHERE user_profile.id = $user_id;

You can then use these results to either display to the user or set the "SELECTED" flag in the options box.