?
Solved

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

Posted on 2010-03-29
8
Medium Priority
?
260 Views
Last Modified: 2012-05-09
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?
0
Comment
Question by:Loganathan Natarajan
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 21

Expert Comment

by:K V
ID: 28956321
select concat(countryname,'-',statename) from countrytable,statetable where statetable.countryname=countrytable.countryname;
will return you
country1-state1
country1-state2
...
country2-state1
country2-state2
...
0
 
LVL 3

Assisted Solution

by:double_helix
double_helix earned 600 total points
ID: 28961203
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
 
LVL 36

Author Comment

by:Loganathan Natarajan
ID: 28964349
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 36

Author Comment

by:Loganathan Natarajan
ID: 28965118
@theGhost_k8

good idea, but country has to be displayed once as top, not with all the state how do i that?
0
 
LVL 6

Accepted Solution

by:
nasirbest earned 800 total points
ID: 28979387
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
 
LVL 6

Expert Comment

by:nasirbest
ID: 28979515
there is

ORDER BY cou_name

in second query change it to

ORDER BY sta_name
0
 
LVL 21

Assisted Solution

by:K V
K V earned 600 total points
ID: 28980787
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
 
LVL 36

Author Closing Comment

by:Loganathan Natarajan
ID: 31708238
thanks
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

599 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