how do I load 2 combo boxes optiones combined in single combo box?

I have 2 tables linked each other and I want to show it in a single combo box (drop down) ... I want to show both tables data as single combo.

For example, Country & State ... I want to show

--country1
----state1
----state2
----state3
--country2
----state1
----state2
----state3

how can i do that?
LVL 36
Loganathan NatarajanLAMP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theGhost_k8Database ConsultantCommented:
select concat(countryname,'-',statename) from countrytable,statetable where statetable.countryname=countrytable.countryname;
will return you
country1-state1
country1-state2
...
country2-state1
country2-state2
...
0
double_helixCommented:
First of all you need to get your data. I know you already have the  data, but the way it's joined will make a difference to the way you have  to render things, so bear with me :-)

You'll want to list  everything in alphabetical order so your query might look like this (making some very broad assumptions regarding your data structure of course):
   SELECT c.countryname,  s.statename     FROM country c INNER JOIN state s ON s.countryid = c.id    ORDER  BY c.countryname, s.statename
 
 So, your data will already be in the correct order for rendering your  control.

Next you simply need to loop through the data...
(See attached snippet 585890)

What this code does is render your drop down combo as you have specified.
Additionally, the country option items are disabled so you can't select them. You can only select the State options.

Hope this provides a reasonable example that you can work with...

Regards.

<?php
#load data from query into an associative array...

$output = '<select name="countries">';
$newcountry = false;
$country = '';
foreach ($rows as $row) {
    if ($row['countryname'] != $country) $newcountry = true;
    $country = $row['countryname'];
    $state = $rows['statename'];
    if ($newcountry) {
        $output.= '<option disabled="disabled">'. $country .'</option>';
    }
    $output .= '<option value="'.$state.'">'. $state .'</option>';
    $newcountry = false;
}
$output .= '</option>';

echo $output;
?>

Open in new window

0
Loganathan NatarajanLAMP DeveloperAuthor Commented:
thanks, i tried like this, but still it is not loading the correct value,
<?php

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
//echo 'Connected successfully';

$db_selected = mysql_select_db('test', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}


$query = " SELECT c.cou_name,  s.sta_name FROM tbl_country c INNER JOIN tbl_state s ON s.sta_id = c.cou_id ".
		"  ORDER  BY c.cou_name, s.sta_name";
	
	echo $query;
	
$result = mysql_query($query) or die(mysql_error());

if(!$result) die("error 1. ".mysql_error());

//$Progress_from_database[0] = "";

while ($rowData = mysql_fetch_array($result)) { 
	$rows[] = $rowData['cou_name']; 
	$rows[] = $rowData['sta_name']; 
}


$output = '<select name="countries">';
$newcountry = false;
$country = '';
foreach ($rows as $row) {
    if ($row['cou_name'] != $country) $newcountry = true;
    $country = $row['cou_name'];
    $state = $row['sta_name']; 

   
    if ($newcountry) {
        $output.= '-><option >'. $country .'</option>';
    }
    $output .= '--- <option value="'.$state.'">'. $state .'</option>';
    $newcountry = false;
}
$output .= '</option>';

echo $output;

?>

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Loganathan NatarajanLAMP DeveloperAuthor Commented:
@theGhost_k8

good idea, but country has to be displayed once as top, not with all the state how do i that?
0
nasirbestCommented:
I have modified you code . try this
<?php

$link = mysql_connect('localhost', 'root', '') or
  die('Could not connect: ' . mysql_error());
$db_selected = mysql_select_db('test', $link) or
    die ('Can\'t use foo : ' . mysql_error());

$query = "SELECT cou_id, cou_name FROM tbl_country
		      ORDER BY cou_name";
$cou_result = mysql_query($query) or die("error 1. ".mysql_error());

$output = "<select name=\"countries\">\n";

while ($country = mysql_fetch_array($cou_result)) {
    $output .= "    <option>$country[cou_name]</option>\n";

    $query = "SELECT sta_id, sta_name FROM tbl_state
              WHERE cou_id = $country[cou_id]
    		      ORDER BY cou_name";
    $sta_result = mysql_query($query)

    while ($state = mysql_fetch_array($sta_result)) {
        $output .= "<option value=\"$country[cou_name]&$state[sta_name]\">&nbsp;&nbsp&nbsp;&nbsp;$state[sta_name]</option>";
    }
}

$output .= '</option>';

echo $output;

?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nasirbestCommented:
there is

ORDER BY cou_name

in second query change it to

ORDER BY sta_name
0
theGhost_k8Database ConsultantCommented:
select countryname, group_concat(statename) from countrytable,statetable where statetable.countryname=countrytable.countryname group by countryname ;

This will give you 2 columns:

country1    state1, state2...
country2    state1, state2...


OR:: select concat(countryname,'-', group_concat(statename)) from ....
This will give you 1 column:

country-state1, state2...
country2-state1, state2...


I assume there are explode in php!!!
Or else as said above looping / writing SP can do the job...
0
Loganathan NatarajanLAMP DeveloperAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.