For each cat , how to show its subcat !

[manalnor]

Here is problem was always hurts me, i think it is about "where"

 

if we have db table with id,name,

 

CREATE TABLE `cat` (
`catid` int(3) NOT NULL auto_increment,
`catname` varchar(255) default NULL,
PRIMARY KEY  (`catid`),
KEY `catid` (`catid`)
) ENGINE=MyISAM  AUTO_INCREMENT=11 ;

INSERT INTO `cat` VALUES (1, 'cat1');
INSERT INTO `cat` VALUES (2, 'cat2');

 

 

then for subcat i've added table with subcat id,subcat name,cat id

 

CREATE TABLE `subcat` (
`subcatid` int(3) NOT NULL auto_increment,
`subcatname` varchar(255) default NULL,
`catid` varchar(255) default NULL,
  PRIMARY KEY  (`subcatid`),
  KEY `subcatid` (`subcatid`)
) ENGINE=MyISAM  AUTO_INCREMENT=11 ;

INSERT INTO `subcat` VALUES (1, 'x', 1);
INSERT INTO `subcat` VALUES (2, 'y', 2);
INSERT INTO `subcat` VALUES (2, 'z', 2);

 

 

this means for

 

cat1 ==> has subcat  x

cat2 ==> has subcat y and z

 

now my problem with php code.

how to write

 

show subcat for certain cat

i mean when i click on cat, it shows me its subcat

 

so for index file that shows only cat

 

 

 

<?
$qma = "select * from cat";
$rma = mysql_query($qma) or die(mysql_error());
$ama = mysql_fetch_array($rma);

echo "<a href=\"sub.php?id=[color=red]$catid[/color]\">[color=red]$catname[/color]</a>";
?>

 

then for sub.php that will show subcat of each cat id

 

how to code it

also is the index code is right  should shows all cat which i click on any cat will send me to sub.php that shows the sub cat for that cat

 

thanks in advance

 

 

 

 

 

 

[Jax2]

Assuming your current page has catid of 1, I.e.

$id=1

 

SELECT cat.*, subcat.*,
FROM cat INNER JOIN cat ON cat.catid = subcat.catid
WHERE cat.catid = ".$ID."";
$result = mysql_query($sql, $db);
$total=mysql_num_rows($result);
if ($total!="0")
  {
while ($row=mysql_fetch_array($result))
      {
$i="0";
while ($i<$total)
          {
echo "".$row['subcatid']."\r\n";
$i++;
          } //end while
     } //end while
  }; //end if

 

Not sure if that's exactly right, and Im sure it could be written better, but I'm rather noob myself. It's how I'd try it anyhow.

 

[ignace]

SELECT c.catid, s.subcatid, c.catname, s.subcatname FROM cat c, subcat s WHERE c.catid = s.catid

[Jax2]

Like I said, there's an easier way  

[manalnor]

thank you very much for help but i've tring to be more simple

 

i've created file let it be  index.php

then when i do call index.php?id=1

 

where id=1 will be the same as catid then

 

<?
$sql ="SELECT * FROM subcat WHERE `catid` = $id";
$rma = mysql_query($sql) or die(mysql_error());
$ama = mysql_fetch_array($rma);
?>
blah blah anything anything

<?=$ama[subcatname]?>

 

now  for calling index.php?id=2

it should shows the subcat in the cat which has id=2

 

in the above example should show  y  and  z

 

but it shows only  y !!!  that is the point.