HELP!!! w/ displaying certain items from databases

[mikefrederick]

So if I create two databases, one called Countries and the other called States, and countries has two fields called United States and Mexico and states has one state in each country, how do I create a page that only displays the states in a certain country? For example, a user will select a country that he wants and be brought to a page where he will select a state, but with my experience it always shows all of the states from both countries. I know that I could make an individual page for each country and do select from * countries where state='North America' but there are going to be far too many countries, states, and cities to do this over and over again. Any suggestions?

 

Also, when users add a country or state, they select the country and then they select the state, but right now it shows all states and not just the states within a country. These selections are made from drop down field boxes. Note that when you add a state it records in the state table which country the state is in.  For the field boxes for adding a state (where you select region, which should show all regions, and then you select countries, which should show the countries in a region) this is the code I have:

 

 

 

<form name="form1" method="post" enctype="multipart/form-data" action="addstate.php">

        <table width="100%" border="0" cellspacing="1" cellpadding="0">

        <tr>

            <td class="text2">Select Region</td>

            <td> 

          <?

mysql_select_db($database_localhost, $localhost);

$query_Rs = "SELECT * FROM regions";

$Rs = mysql_query($query_Rs, $localhost) or die(mysql_error());

$totalRows_Rs = mysql_num_rows($Rs);

 

?>

              <select name="region1" class="text2" id="region1">

                <? while ($row_Rs = mysql_fetch_assoc($Rs))

  {

  ?>

                <option value="<? echo $row_Rs['regionid'];?>"><? echo $row_Rs['regionname'];?></option>

                <? } ?>

              </select>

         

          </td></tr> <tr>

            <td class="text2">Select Country</td>

            <td> 

          <?

 

mysql_select_db($database_localhost, $localhost);

$query_Rs = "SELECT * FROM countries";

$Rs = mysql_query($query_Rs, $localhost) or die(mysql_error());

$totalRows_Rs = mysql_num_rows($Rs);

 

?>

              <select name="country" class="text2" id="country">

                <? while ($row_Rs = mysql_fetch_assoc($Rs))

  {

  ?>

                <option value="<? echo $row_Rs['countryname'];?>"><? echo $row_Rs['countryname'];?></option>

                <? } ?>

              </select>

          </td></tr> <tr>

            <td class="text2">State Name</td>

            <td><input name="statename" type="text" class="text2" id="statename"></td>

     

         

         

       

       

         

            <td><input name="Submit" type="submit" class="text2" value="Add State"></td>

          </tr>

        </table>

      </form>

[BlueSkyIS]

wow, a huge question. first, you mean two TABLES, not two databases. A database has tables, tables have rows and columns. anyway...

 

The idea is to have 1 or 2 pages that display all data depending on the selected value(s) passed via GET or POST. For instance, on the page there may be a link to view data from USA and that link might be something like this:

 

<a href='countrypage.php?country=USA'>USA</A>

 

so when you click the link, you are sent to countrypage.php?country=USA. We can get that bit at the end of the link on countrypage.php:

 

$selected_country = $_GET['country']; // Now we know what country was selected.

 

Now you can use the value of $selected_country to get only the records for that country:

 

$sql = "SELECT * FROM some_table WHERE country = '$country'";

 

 

[mikefrederick]

In the option fields, how do I get it to show only info related to the selected country or region? And yes i meant tables not databases.

[GingerRobot]

As was said, you only select those states from the database which are from the previously selected country. That is the idea of the where clause in this statement:

 

$sql = "SELECT * FROM some_table WHERE country = '$country'";

[BlueSkyIS]

you'll need to run a query to only load the appropriate data, ala:

 

$sql = "SELECT FROM states_table WHERE country = '$country'";
$result = mysql_query($sql) or die(mysql_error());
echo "<SELECT NAME='states'>";
while ($row = mysql_fetch_array($result)) {
     $state_id = $row['id'];
     $state_name = $row['state_name'];
     echo "<OPTION VALUE='$state_id'>$state_name</OPTION>";
}
echo "</SELECT>"

[mikefrederick]

In this example, what would $country be equal to? Is my problem that the first field is being selected from one table and the second field is being selected from another?

 

Posts: 1,196

 

Call me Ben

 

smithers.ben@googlemail.com

View Profile

 

 

Re: HELP!!! w/ displaying certain items from databases

« Reply #3 on: Today at 12:28:58 PM »

Reply with quoteQuote

As was said, you only select those states from the database which are from the previously selected country. That is the idea of the where clause in this statement:

 

Code:

 

$sql = "SELECT * FROM some_table WHERE country = '$country'";

[GingerRobot]

The $country variable comes from the previous form selection, probably stored in a session. The process would be something similar to:

 

1.) User first navigates to your page, and selects a country

2.) They submit the form

3.) You set the county in a session variable, pull all of the states/regions for the selected country from the database, and display a select box with this information in

4.) The user selects a state/region and submits the form

5.) You then do whatever you wanted to do with the state and country - you can get the selected state from the form submission, and the country from the session variable.

 

Im not sure if that clarfies the problem or not; im not 100% sure what the issue is - hope it helps though.

[mikefrederick]

Let me clarify:

I am talking about one form with two drop down boxes, the first selecting a region from the region table and the second selecting a country from the country table where the region equals the region selected in the first box. I can get the first box to display all data by simply selecting it from the region table by regionname. The second box I can get to show all countries, but I can't get it to show only countries w/ the selected region.

[BlueSkyIS]

okay, so you want to dynamically update the OPTIONS in the second SELECT based on the OPTION selected on the first SELECT, correct?

 

if so, 2 ways:

 

1. Ajax (Javascript plus PHP)

2. Javascript (with preloaded OPTIONS from PHP)

 

add a Javascript onchange() handler to the first <SELECT>. when onchange() is invoked, re-popluate the second SELECT with new OPTIONs based on the selectedIndex and/or value of the first SELECT.

[mikefrederick]

Yes, you are right. Can you help me wrap this up? I got this far, the bold is what I need to change:

 

<form name="FormSubmit" action="">

    <?

mysql_select_db($database_localhost, $localhost);

$query_Rs = "SELECT * FROM regions";

$Rs = mysql_query($query_Rs, $localhost) or die(mysql_error());

$totalRows_Rs = mysql_num_rows($Rs);

 

?>

<select NAME="SELECT1" ID="SELECT1"

onChange="subselect(this.options.selectedIndex)" ="combobox">

<? while ($row_Rs = mysql_fetch_assoc($Rs))

  {

  ?>

 

 

 

<option value="<? echo $row_Rs['regionid'];?>"><? echo $row_Rs['regionname'];  ?></option><? } ?>

</select>

<?

mysql_select_db($database_localhost, $localhost);

$query_Rs = "SELECT * FROM countries";

$Rs = mysql_query($query_Rs, $localhost) or die(mysql_error());

$totalRows_Rs = mysql_num_rows($Rs);

 

?>

<select NAME="SELECT2" ID="SELECT2" class="combobox">

<option value="" selected>-</option>

</select>

<input type="submit" name="submit">

 

<script>

<!--

var groups=document.FormSubmit.SELECT1.options.length

var group=new Array(groups)

for (i=0; i<groups; i++)

group=new Array()

 

group[0][1]=new Option("option1")

group[0][2]=new Option("option2")

 

group[1][0]=new Option("all ")

group[1][1]=new Option("sub1-1","1")

group[1][2]=new Option("sub1-2","2")

group[2][0]=new Option("- all - ")

group[2][1]=new Option("sub2-1","3")

group[2][2]=new Option("sub2-2","4")

 

var temp=document.FormSubmit.SELECT2

 

function subselect(x){

for (i=temp.options.length-1;i>0;i--)

  temp.options=null

for (k=0;k<group[x].length;k++){

  temp.options[k]=new Option(group[x][k].text,group[x][k].value)

}

temp.options[0].selected=true

}

//-->

</script>

</form>