join tables to display data on css drop down menu

[jonnyw6969]

Hi all,

 

Got really stuck trying to group data from 2 db tables to display on a css dropdown menu as main cat and sub cats.

 

This is th code im using

 

<li><a href="/countries.php">destinations</a><ul>
     <?
$query1 = "SELECT country.name, country.region_id , regions.region_name, regions.region_id FROM country join regions where country.region_id = regions.region_id group by regions.region_name,country.name order by regions.region_name,country.name";
if ($result1 = mysql_query($query1)) {
while ($row1 = mysql_fetch_array($result1)){
	echo '<li><a href="cheapholidays/'.urlencode($row1['region_name']).'">'.htmlentities($row1['region_name']).'</a><ul>';?>
          		<? echo'<li class="first"><a href="cheapholidays/'.urlencode($row1['name']).'">'.htmlentities($row1['name']).'</a></li>
          		
          	</ul></li>';
}}
?>
   </ul></li>

 

The css dropdown works for a static data so i know theres no probs there.

 

Problem is the drop down drops and shows all the main cats for as mainy times as there are sub cats of the main cats. 

 

Making any sense?

 

so if main cat Europe has 5 sub cats on the main dropdown there is 5 main cats called europe each holding 1 of the 5 sub cats.

 

Ok what i obviously want is just one main cat then 5 sub cats from that.

 

Can anyone please help?

 

Jon

[mjahkoh]

Download navicat. Its query is simple to implement

 

Try this

 

Try this

 

the tables are

 

/*

MySQL Data Transfer

Source Host: localhost

Source Database: webcrawler

Target Host: localhost

Target Database: webcrawler

Date: 4/13/2008 1:05:55 AM

*/

 

SET FOREIGN_KEY_CHECKS=0;

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

-- Table structure for continents

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

CREATE TABLE `continents` (

  `continent` varchar(100) default NULL,

  `continentID` int(11) NOT NULL auto_increment,

  PRIMARY KEY  (`continentID`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

 

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

-- Records

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

INSERT INTO `continents` VALUES ('Africa', '1');

INSERT INTO `continents` VALUES ('Europe', '2');

 

/*

MySQL Data Transfer

Source Host: localhost

Source Database: webcrawler

Target Host: localhost

Target Database: webcrawler

Date: 4/13/2008 1:06:03 AM

*/

 

SET FOREIGN_KEY_CHECKS=0;

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

-- Table structure for country

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

CREATE TABLE `country` (

  `country` varchar(100) default NULL,

  `countryid` int(11) NOT NULL auto_increment,

  `continentID` int(11) default NULL,

  PRIMARY KEY  (`countryid`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 

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

-- Records

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

INSERT INTO `country` VALUES ('England', '1', '2');

INSERT INTO `country` VALUES ('Spain', '2', '2');

INSERT INTO `country` VALUES ('France', '3', '2');

 

 

SELECT

country.countryid,

country.country

FROM

continents

Inner Join country ON continents.continentID = country.continentID

WHERE

continents.continent =  'Europe'

 

[jonnyw6969]

Hi

Thanks for the help but still no joy. It prints out the same.

 

I used your code and adapted it to mine like this

 

 <?
$query1 = "SELECT country.name, country.region_id , regions.region_name, regions.region_id FROM regions  inner join country on regions.region_id = country.region_id where regions.region_id = country.region_id";
if ($result1 = mysql_query($query1)) {
while ($row1 = mysql_fetch_array($result1)){
	echo '<li><a href="cheapholidays/'.urlencode($row1['region_name']).'">'.htmlentities($row1['region_name']).'</a><ul>
          		<li class="first"><a href="cheapholidays/'.urlencode($row1['name']).'">'.htmlentities($row1['name']).'</a></li>
          		
          	</ul></li>';
}}
?>

 

 

Jon

[jonnyw6969]

Anyone????

[jonnyw6969]

???