?
Solved

populating combo box using mysql and php

Posted on 2009-12-30
12
Medium Priority
?
4,225 Views
Last Modified: 2013-12-12
hi all
i'm using the below link
http://roshanbh.com.np/2008/01/populate-triple-drop-down-list-change-options-value-from-database-using-ajax-and-php.html/comment-page-3
to populate 3 combo box country - state - city...
as per the instructions i did and got success..
however my requirement is one more combo box that is locality which should populate based on the city selection.

for this i created findLocality.php with code...
<?
include ("dbconnect.php");
?>

<? $countryId=intval($_GET['country']);
$stateId=intval($_GET['state']);
$cityId=intval($_GET['city']);
$query="SELECT id,locality FROM locality WHERE countryid='$countryId' AND stateid='$stateId' AND cityid='$cityId'";
$result=mysql_query($query);

?>
<select name="locality">
<option>Select Locality</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value=<?=$row['id']?>><?=$row['locality']?></option>
<? } ?>
</select>

To make this work... i added a new function too like this on the form page
function getLocality(countryId,stateId,cityId) {            
            var strURL="findLocality.php?country="+countryId+"&state="+stateId+"&city="+cityId;
            var req = getXMLHTTP();
            
            if (req) {
                  
                  req.onreadystatechange = function() {
                        if (req.readyState == 4) {
                              // only if "OK"
                              if (req.status == 200) {                                    
                                    document.getElementById('localitydiv').innerHTML=req.responseText;                                    
                              } else {
                                    alert("There was a problem while using XMLHTTP:\n" + req.statusText);
                              }
                        }                        
                  }                  
                  req.open("GET", strURL, true);
                  req.send(null);
            }
                        
      }

however to my luck... i get success til city but after that localityis not coming...

Could any one just let me know where i'm wrong, or instead let me know the fresh code to add extra box...

Need urgent help
0
Comment
Question by:global_expert_advice
  • 7
  • 5
12 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26148991
The way I would code that page is I would have each box auto submit on change so I would add
<select name="state" onchange="this.form.submit()">
// then get all your options from the DB like you are doing to populate it. Once it has been submitted I would do another query to get the cities based on the state that was selected. The once the city box has been selected, have that also submit the form (onchange="this.form.submit()") then you can do a final query to populate the localities.

If you have trouble understanding, please give me the column heading names and I will code up an example for you.
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26151010
I have made an example for you. When you setup the tables in the attached sql (at the bottom of my script) and save the script as 'locations.php' - run it and you will see how it works. Select a state first, then watch how it works.

The only improvement I would make to this code is to write a function that works backwards so if you have already chosen a state and a county, then when you select a town, the state should only show the correct state and the county should only show the correct county. A simple function should do that but for now, it works forwards. IE:

1: Select a state and the next box appears with counties of that state
2: Select a county and the final selectbox appears with towns of the selected county.

Hope this helps.
<?php
//*******************// locations.php - coded by Mark Brady //*********************//
//                                                                                 //
//     The code below uses sessions to store selected values from dropdown select  //
//     boxes and populate other select boxes based on the selection of the first   //
//     one. To use, replace the line "Include("DB-connect.php");" with your        //
//     connection script or code. Create a table in a mysql database and name it   //
//     'locations'. Create 4 columns [ID - name - county - state].ID is "Auto Inc" //
//     and the others are set to "varchar(40)". Populate with some different areas //
//     of different counties of different states. Or use the sql dump at the end   //
//     of this script. Save your DB connection script (if external) to the same    //
//     folder as this file (locations.php). Any questions - please send to         //
//     elvin66 at extra dot co dot nz. ------------------ Enjoy                    //
/////////////////////////////////////////////////////////////////////////////////////
 
session_start();
if(is_array($_REQUEST)) {
              foreach($_REQUEST as $key => $value) {
                        $_SESSION[$key]      = $value;
              }
      }

if($_SESSION['reset'] == 1){            // The reset button has been pressed
session_destroy();                      // This is for the reset button and destroys the session and session variables
header("location: locations.php");      // This file

}else{
Include("DB-connect.php");  // Your connection script
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//******// Setup session variables ///////////////////////////////////////////////////////////////////////////////////////

$_state  = $_SESSION['state'];
$_county = $_SESSION['county'];
$_town   = $_SESSION['town'];   // This is the location or town
$_ID     = $_SESSION['ID'];     // Not used in this script but it's a good idea to have an ID for each entry for later use

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Setup a basic form with one selectbox containing "states" only - at this stage
//
$form  = "<form action=\"locations.php\" method=\"POST\">";  // You could also just have "$form = "<form>";  It will do the same thing!
$select_state  = "<select name=\"state\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_state .= "<option value=\"\">Select a State</option>";

// Create a query to populate the states selectbox (remember there are only 3 states in this example)
//
$sql_state = "SELECT DISTINCT `state` FROM `locations` ORDER BY `state` ASC";  // This will return all 3 states and sort them in alphabetical order
$result_state = mysql_query($sql_state)or die("Error with DB search");
$nums = mysql_num_rows($result_state);
//
// Populate the selectbox
while($row = mysql_fetch_array($result_state, MYSQL_ASSOC))
{
$name = "{$row['state']}";
if($name == $_state)
{$select_state .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_state .= "<option value=\"".$name."\">".$name."</option>";}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Now we display the form inside a basic table /////////////////////////////////////////////////////////////
//
echo "<center><table width=400 cellpadding=2 cellspacing=2 bgcolor=\"#234478\" style=\"font-family:arial;color:white\"><tr><td width=185>";
//
echo $form.$select_state."</select></td><td><font color=yellow>";

// See if a state has been selected
//
if($_state != ""){ echo "<strong>You selected <font color=red>".$_state."</td></tr>";
//
// Create a query to search the locations table for counties in the chosen state
$sql_search_counties = "SELECT DISTINCT `county` FROM `locations` WHERE `state`='$_state' ORDER BY `county` ASC";
$result_counties = mysql_query($sql_search_counties)or die("Error with DB search");
$select_county   = "<select name=\"county\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_county  .= "<option value=\"\">Select a County</option>";
//
// Populate the selectbox
while($row = mysql_fetch_array($result_counties, MYSQL_ASSOC))
{
$name = "{$row['county']}";
if($name == $_county)
{$select_county .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_county .= "<option value=\"".$name."\">".$name."</option>";}
}
// Display the County selectbox
//
echo "</tr><td>".$select_county."</select></td><td><font color=yellow>";

                 }else{  // No state has been selected
echo "</td></tr>";
}

/////////////////////////////////// Finally, the location or town selectbox ////////////////////////////////////////////////////////
//
// See if a county has been selected
//
if($_county != ""){ echo "<strong>You selected <font color=red>".$_county."</td></tr>";
//
// Create a query to search the locations table for towns in the chosen county
$sql_search_towns = "SELECT  `name` FROM `locations` WHERE `county`='$_county' ORDER BY `name` ASC";
$result_towns  = mysql_query($sql_search_towns)or die("Error with DB search");
$select_towns  = "<select name=\"town\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_towns .= "<option value=\"\">Select a Town</option>";
//
// Populate the selectbox
while($row = mysql_fetch_array($result_towns, MYSQL_ASSOC))
{
$name = "{$row['name']}";
if($name == $_town)
{$select_towns .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_towns .= "<option value=\"".$name."\">".$name."</option>";}
}
// Display the towns selectbox
//
echo "</tr><td>".$select_towns."</select></td><td><font color=yellow>";

                 }else{  // No county has been selected
echo "</td></tr>";
}
//
// See if a Town has been selected
//
if($_town!= ""){ echo "<strong>You selected <font color=red>".$_town."</td></tr>";}
//
//////////////////////////////////  End of Select Boxes ////////////////////////////////////////////////////////////////////////////
//
echo "</table></form>";
}
?>
<!-- The Reset Button <!-->
<br><br>
<SCRIPT LANGUAGE="JavaScript">
function Reset()
{
session_destroy();
}
</script>

<form method="POST" action="locations.php">
<input type="hidden" name="reset" value="1">
<input type="submit" value="Reset">
</form>
<!--   End of locations.php script <!-->

<!--   The following code is a mysql dump for the sql data. Save everything below into a file and name it data.sql-Import into mysql <!--><!--

-- phpMyAdmin SQL Dump
-- version 3.2.2.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 31, 2009 at 01:12 PM
-- Server version: 5.1.37
-- PHP Version: 5.2.10-2ubuntu6.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `locations`
--

CREATE TABLE IF NOT EXISTS `locations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `county` varchar(40) NOT NULL,
  `state` varchar(40) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`ID`, `name`, `county`, `state`) VALUES
(1, 'winter park', 'orange', 'florida'),
(2, 'edgewood', 'orange', 'florida'),
(3, 'apopka', 'orange', 'florida'),
(4, 'orlando', 'orange', 'florida'),
(5, 'daytona beach', 'volusia', 'florida'),
(6, 'deltona', 'volusia', 'florida'),
(7, 'oak hill', 'volusia', 'florida'),
(8, 'edgewater', 'volusia', 'florida'),
(9, 'boulder creek', 'santa cruz', 'california'),
(10, 'ben lomond', 'santa cruz', 'california'),
(11, 'felton', 'santa cruz', 'california'),
(12, 'corralitos', 'santa cruz', 'california'),
(13, 'rosemont', 'san diego', 'california'),
(14, 'coronado', 'san diego', 'california'),
(15, 'san marcos', 'san diego', 'california'),
(16, 'fallbrook', 'san diego', 'california'),
(17, 'ellerslie', 'harris', 'georgia'),
(18, 'hamilton', 'harris', 'georgia'),
(19, 'kingboro', 'harris', 'georgia'),
(20, 'whitesville', 'harris', 'georgia'),
(21, 'alexander', 'burke', 'georgia'),
(22, 'shell bluff', 'burke', 'georgia'),
(23, 'murray hill', 'burke', 'georgia'),
(24, 'midville', 'burke', 'georgia');

<!-->
<!-- End of dump. Copy only to "24" above  <!-->

Open in new window

0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26151020
This code :

<SCRIPT LANGUAGE="JavaScript">
function Reset()
{
session_destroy();
}
</script>

Just ignore that, it does nothing..... There is a reset button below that works
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:global_expert_advice
ID: 26152260
thanks for the details..
i will go thru it and let u know if any problem
thanks again
0
 
LVL 1

Author Comment

by:global_expert_advice
ID: 26152888
hi
tried ur code.. but in different manner...
more over, i do need to work on field values.. which is to be used for further query....
first i wanted to get country list
state list
city list
locality list

so what i'm doing is fetching the country list using sql query.. and using onchange this.value submit...
to get city list...
but once page refreshes,... i'm loosing the country value id and its name.... i know it has to be saved in session... i tried...
likewise rest of the things has to be done...

i tried ur way... like when countryid=$_country
use of select option...
or else default country select...

my code is below

<?php

session_start();
if(is_array($_REQUEST)) {
              foreach($_REQUEST as $key => $value) {
                        $_SESSION[$key]      = $value;
              }
      }
 
if($_SESSION['reset'] == 1){            // The reset button has been pressed
session_destroy();                      // This is for the reset button and destroys the session and session variables
header("location: locations_iws.php");      // This file
 
}else{
include("dbconnect.php");  // Your connection script
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
//******// Setup session variables ///////////////////////////////////////////////////////////////////////////////////////
 
$_country = $_SESSION['country'];
$_countyid = $_SESSION['countryid'];
$_state  = $_SESSION['state'];
$_stateid  = $_SESSION['stateid'];
$_city   = $_SESSION['city'];   // This is the location or town
$_cityid   = $_SESSION['cityid'];
$_locality   = $_SESSION['locatlity'];
$_localityid   = $_SESSION['locatlityid'];
}
 
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
?>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<div align="center">
      <table border="0" width="100%" cellspacing="0" cellpadding="0">
            <tr>
                  <td>
                  <form method="POST" action="#">
                        <table border="0" width="100%" cellspacing="0" cellpadding="0">
                              <tr>
                                    <td>Country</td>
                                    <td><?php
      $query="SELECT id,country FROM country";
      $result=mysql_query($query);
            ?>
<select name="country" onchange="this.form.submit()">
<option>Select Country</option>
<? while($row=mysql_fetch_array($result)) {
?>
<option value="<?=$row['id']?>"><?=$row['country']?></option>
<? } ?>
</select>
</td>
                              </tr>
                              <tr>
                                    <td>&nbsp;State</td>
                                    <td><?php
      $query="SELECT id,statename FROM state WHERE countryid=$_country";
      $result=mysql_query($query);
            ?>
<select name="state" onchange="this.form.submit()">
<option>Select State</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['statename']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>city</td>
                                    <td><?php
      $query="SELECT id,city FROM city WHERE countryid='$_country' AND stateid='$_state'";
      $result=mysql_query($query);
            ?>
<select name="city" onchange="this.form.submit()">
<option>Select City</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['city']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>Locality</td>
                                    <td><?php
      $query="SELECT id,locality FROM locality WHERE countryid='$_country' AND stateid='$_state' AND cityid='$_city'";
      $result=mysql_query($query);
            ?>
<select name="locality" onchange="this.form.submit()">
<option>Select Locality</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['locality']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>&nbsp;</td>
                                    <td>&nbsp;</td>
                              </tr>
                        </table>
                        <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
                  </form>
                  <p>&nbsp;</td>
            </tr>
      </table>
</div>

</body>

</html>

I want things in this manner... as i'm not comfortable with echo system...
please modify it...
regards
zia
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26153954
I gave you a working code that I coded from scratch. I tested this code and it works to answer your question. The code you have posted is totally rewritten so I can't go through your code and fix up the errors. You will need to do that. When you ask a question on EE, we try to give you guidence by posting code that works. The idea is that you will look at what we have done and ask questions about how to modify it to suit your needs if required. Unfortunately, I have not got the time to go through your latest code and sort it out for you. If you were to use my code and tell me exactly what you want it to do that it does not do already, then I would be able to help you.

I'm just trying to help you with your learning here.
0
 
LVL 20

Accepted Solution

by:
Mark Brady earned 1000 total points
ID: 26154153
Global expert advice:..>>  I think you are making things very much harder for yourself by the way you are coding scripts. Most people need to change the scripts that get given to them by Experts on EE but it is good practice to keep your code where it makes sense. Just looking at your modified version of my code I can see many occurrences of incorrect coding format.

Here is a few tips for you.

1: Never use short tags --- they are very much outdated and if you upload your scripts to a server that does not support them, your code will look like mush and not run. Always use <?php   and not ?>

2: Comment your code segments. It makes it easier to read for yourself and others when trying to work  on it.  Only you (the coder) knows your thought process so make it easy to follow by using // comments

3: Try not to jump in and out of "php" to much. When your browser sees the <?php tag, it starts the PHP engine to process the code. You seem to open the engine and shut it down over and over when this is not necessary. Your line of code :

<option value="<?=$row['id']?>"><?=$row['country']?></option>
<? } ?>

You opened and closed the engine 3 times but why ??? If you typed it like this :

<?php  // leave this tag open
echo "<option value=".$row['id'].">".$row['country']."</option>";}

Or you could put everything into a variable and display it like I showed you in my code. Don't be scared of the "ECHO" command. You are already using PHP so by not closing the tags ( ?> ) every time you can do wonderful things with PHP.

Finally, I am not saying your code will not work, with a little fixing up it will actually run. However, once you start coding, it is like a good chocolate - You keep going back for more so why not get into good habits from the start. We are most happy to help you out with coding and techniques. I am always learning from other experts and I will pass on a valuable piece of advice that one certain expert told me a few years ago.   "Format your Code"  

Dude, it's new years day here so happy new year!!!  Please take my advice with a grain of salt - I just want to help.
0
 
LVL 1

Author Comment

by:global_expert_advice
ID: 26154265
hi
thanks for the wonderful advice... and i'm sure going to take the tips..
i'll start working on ur method...and i will try to modify your code in your way...
so that first i should get country --- than state --- than city --- than locality... my intention is this only..
also after fetching all these details... the value of each field should be the id's...
In my case i already created 4 tables... like country, state, city and locality..

so what i'm going to try now... is using your code... first to fetch country using query to country table.. and taking the country id and country name.. and taking the same in session... so that when i make query to state... i need where countryid = ...

actually first i tried ur code... but got something messes... i will re try.. things and let u know..
once again thanks... and happy new year... my new year will start after 3 hrs.. hope to solve this issue before new year... i don't want to take incomplete coding :-)
thanks
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26154372
Ok if you were to explain how your tables are set up (column names etc) then it would be simple to modify my code to suit. In my code, you would modify the mysql query (each one) to include the ID. Something like

$sql = "SELECT DISTINCT `state`, `ID` FROM `locations WHERE......blah blah... you get the idea.

As for your separate tables, you can do it this way but you can also do it in one table like I have done. I suppose if you have a lot of information then separate tables would be better but if it is just "Country" - "State" - "City" - "County" - "Town" then you could do all this with one table.

If you want to use separate tables then you can use the "join" method to connect values from each table by linking on of the fields which would be a common field like "ID".

Anyway, good luck. I'll be awake for a short time before I pass out with alcohol induced coma !
0
 
LVL 1

Author Comment

by:global_expert_advice
ID: 26158941
okay it has been solved now... i used my first method only using jquery... and without refreshing page state city locality populates...
my code was right.. just a mistake in $country....
your idea too is good... but it makes the page refresh again....
and chances of loosing session variables...
thanks
0
 
LVL 1

Author Closing Comment

by:global_expert_advice
ID: 31671335
not to what i asked
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 26163499
cOOL THANKS ANYWAY.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question