fetch data from 2 mysql tables

[mrjameer]

hi,

 

 

i have 2 mysql tables

 

first table name is states

 

fields are

 

state_id,state_name

1---ca

2---ba

3---da

 

second table name is citys

 

fields are

id,state_name,city_name

1--ca---texas

2--ca---newyork

3--ba--losangles

4--ba--perth

5--ba--sydney

6--da--washington

 

what i want to do is i want to fetch data from 2 mysql tables and display as

state_name1

cityname1

cityname2

state_name2

cityname1

cityname2

cityname3

 

like that.just i need the sql query to fetch and display the data in the above format.any of your help will be surely appreciated

 

thanks

mrjameer

 

 

[rhodesa]

You only need the second table

<?php

  $sql = "SELECT * FROM `citys` ORDER BY `state_name`,`city_name`";

  $result = mysql_query($sql) or die('Query error: '.mysql_error());

  $lastState = '';

  while($row = mysql_fetch_array($result)){

    if($lastState != $row['state_name'])

      print "<b>{$row['state_name']}</b><br />";

    print "{$city_name}<br />";

    $lastState = $row['state_name'];

  }

?>

[rhodesa]

sorry...in code tags:

 

<?php
  $sql = "SELECT * FROM `citys` ORDER BY `state_name`,`city_name`";
  $result = mysql_query($sql) or die('Query error: '.mysql_error());
  $lastState = '';
  while($row = mysql_fetch_array($result)){
    if($lastState != $row['state_name'])
      print "{$row['state_name']}<br />";
    print "{$city_name}<br />";
    $lastState = $row['state_name'];
  }
?>

[mrjameer]

hi rhodesa

 

 

thanks for your reply

 

sorry here i have a small adjustment

 

state_id,state_name

1---ca

2---ba

3---da

 

second table name is citys

 

fields are

cityid,state_id,city_name

1--1---texas

2--1---newyork

3--2--losangles

4--2--perth

5--2--sydney

6--3--washington

 

 

[rhodesa]

well...in that case you do need both tables

 

Change the SQL to:

$sql = "SELECT * FROM citys C INNER JOIN states S ON C.state_id = S.state_id ORDER BY S.state_name,C.city_name";

[mrjameer]

hi

 

 

it is just displaying the city names but not state names

 

 

regards

mrjameer

[mrjameer]

hi

 

 

now it is displaying the state names and city names but state name are repeating.i just want to print in the way that first print the state name and all the citys under it like that.i have to add one where condition also so that it display the particular coutry states and citys only

 

thanks again

 

best regards

mrjameer

[rhodesa]

what does your code look like now?

[mrjameer]

hi

 

i have to give some more clear idea

 

CREATE TABLE `category` (

  `cat_id` int(2) NOT NULL auto_increment,

  `category` varchar(25) NOT NULL,

  PRIMARY KEY  (`cat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

 

--

-- Dumping data for table `category`

--

 

INSERT INTO `category` VALUES (1, 'Fruits');

INSERT INTO `category` VALUES (10, 'cars');

 

CREATE TABLE `subcategory` (

  `subcat_id` int(3) NOT NULL auto_increment,

  `cat_id` int(2) NOT NULL default '0',

  `subcategory` varchar(25) NOT NULL default '',

  UNIQUE KEY `subcat_id` (`subcat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;

 

--

-- Dumping data for table `subcategory`

--

 

INSERT INTO `subcategory` VALUES (30, 1, 'heavyfruits');

INSERT INTO `subcategory` VALUES (23, 1, 'bbb');

INSERT INTO `subcategory` VALUES (22, 10, 'aaa');

INSERT INTO `subcategory` VALUES (29, 1, 'middlefruits');

INSERT INTO `subcategory` VALUES (26, 1, 'fruits');

INSERT INTO `subcategory` VALUES (28, 1, 'lightfruits');

 

 

CREATE TABLE `subsubcategory` (

  `cat_id` int( NOT NULL default '0',

  `subcat_id` int( NOT NULL default '0',

  `subsubcat_id` int(11) NOT NULL auto_increment,

  `subcat2` varchar(15) NOT NULL default '',

  PRIMARY KEY  (`subsubcat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=50 ;

 

--

-- Dumping data for table `subsubcategory`

--

 

INSERT INTO `subsubcategory` VALUES (1, 28, 47, 'light2');

INSERT INTO `subsubcategory` VALUES (1, 28, 46, 'light1');

INSERT INTO `subsubcategory` VALUES (1, 30, 45, 'heavy2');

INSERT INTO `subsubcategory` VALUES (1, 30, 44, 'heavy1');

INSERT INTO `subsubcategory` VALUES (1, 26, 42, 'oranges');

INSERT INTO `subsubcategory` VALUES (10, 22, 41, 'hi');

INSERT INTO `subsubcategory` VALUES (1, 23, 40, 'apple');

INSERT INTO `subsubcategory` VALUES (1, 29, 49, 'middle1');

INSERT INTO `subsubcategory` VALUES (1, 28, 48, 'light3');

 

<?php

include 'config.php';

$sql = "SELECT * FROM subsubcategory C INNER JOIN subcategory S ON C.subcat_id=S.subcat_id  ORDER BY S.subcat_id";

 

while($row = mysql_fetch_array($result)){

    if($lastState != $row['subsubcat_id'])

      print "{$row['subcategory']}<br />";

    print "<font color=#FFFFFF>{$row['subcat2']}</font><br />";

   $lastState = $row['subcat_id'];

  }

 

 

i want to display just cat_id='1' data and all subcats and subsubcats of it

[u]output[/u]

aaa

hi

bbb

apple

fruits

oranges

lightfruits

light2

lightfruits

light3

lightfruits

light1

middlefruits

middle1

heavyfruits

heavy2

heavyfruits

heavy1

 

 

thanks

mrjameer

[mrjameer]

hi

 

i have to give some more clear idea

 

CREATE TABLE `category` (

  `cat_id` int(2) NOT NULL auto_increment,

  `category` varchar(25) NOT NULL,

  PRIMARY KEY  (`cat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

 

--

-- Dumping data for table `category`

--

 

INSERT INTO `category` VALUES (1, 'Fruits');

INSERT INTO `category` VALUES (10, 'cars');

 

CREATE TABLE `subcategory` (

  `subcat_id` int(3) NOT NULL auto_increment,

  `cat_id` int(2) NOT NULL default '0',

  `subcategory` varchar(25) NOT NULL default '',

  UNIQUE KEY `subcat_id` (`subcat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;

 

--

-- Dumping data for table `subcategory`

--

 

INSERT INTO `subcategory` VALUES (30, 1, 'heavyfruits');

INSERT INTO `subcategory` VALUES (23, 1, 'bbb');

INSERT INTO `subcategory` VALUES (22, 10, 'aaa');

INSERT INTO `subcategory` VALUES (29, 1, 'middlefruits');

INSERT INTO `subcategory` VALUES (26, 1, 'fruits');

INSERT INTO `subcategory` VALUES (28, 1, 'lightfruits');

 

 

CREATE TABLE `subsubcategory` (

  `cat_id` int( NOT NULL default '0',

  `subcat_id` int( NOT NULL default '0',

  `subsubcat_id` int(11) NOT NULL auto_increment,

  `subcat2` varchar(15) NOT NULL default '',

  PRIMARY KEY  (`subsubcat_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=50 ;

 

--

-- Dumping data for table `subsubcategory`

--

 

INSERT INTO `subsubcategory` VALUES (1, 28, 47, 'light2');

INSERT INTO `subsubcategory` VALUES (1, 28, 46, 'light1');

INSERT INTO `subsubcategory` VALUES (1, 30, 45, 'heavy2');

INSERT INTO `subsubcategory` VALUES (1, 30, 44, 'heavy1');

INSERT INTO `subsubcategory` VALUES (1, 26, 42, 'oranges');

INSERT INTO `subsubcategory` VALUES (10, 22, 41, 'hi');

INSERT INTO `subsubcategory` VALUES (1, 23, 40, 'apple');

INSERT INTO `subsubcategory` VALUES (1, 29, 49, 'middle1');

INSERT INTO `subsubcategory` VALUES (1, 28, 48, 'light3');

 

<?php
include 'config.php';
$sql = "SELECT * FROM subsubcategory C INNER JOIN subcategory S ON C.subcat_id=S.subcat_id  ORDER BY S.subcat_id";

while($row = mysql_fetch_array($result)){
    if($lastState != $row['subsubcat_id'])
      print "{$row['subcategory']}
";
    print "<font color=#FFFFFF>{$row['subcat2']}</font>
";
   $lastState = $row['subcat_id'];
  }
?>

 

i want to display just cat_id='1' data and all subcats and subsubcats of it

output

aaa

hi

bbb

apple

fruits

oranges

lightfruits

light2

lightfruits

light3

lightfruits

light1

middlefruits

middle1

heavyfruits

heavy2

heavyfruits

heavy1

 

 

thanks

mrjameer