I am using php to list out categories for a particular item while making blog.But i am listing out all the categories which are present in database in the list.I created a category table in my database.my code is listed as follows:

<?php
                                               include 'connection.php';
                                               

      $list  ="select name from categories";
    //$list  = "SELECT DISTINCT name FROM categories";
$rlist = mysql_query($list) or die(mysql_error());

while($rows = mysql_fetch_assoc($rlist)) 
 { 
 $catname = $rows['name'];
 echo '<li><a href="/index.php?id=' . $catname . '">' . $catname . '</a></li>';

} 
                                                   

?>

this is categories table
id name p_id
1 new release 3
2 videos 3
3 Trailer 3
4 Latest song 3
5 New Devices 4
6 Mobiles 4
7 PC 4

i created blog for movies,gadget,design..p_id is the respective ids of movies,gadget,design.Name is the subcategory of movies,gadget,design.movies,gadget,design comes under another table called page.Any help would be appreciated

Member Avatar
diafol

So what do you want? I can't see an outlines problem in your post. Are you looking for a JOIN??

So what do you want? I can't see an outlines problem in your post. Are you looking for a JOIN??

Yes,is there way to use join here

Member Avatar
diafol

Sorry bavenbabu, but this is like drawing blood from a stone. You want to use join, but you don't say which fields you need to show/format or which are the PK/FK fields

Sorry bavenbabu, but this is like drawing blood from a stone. You want to use join, but you don't say which fields you need to show/format or which are the PK/FK fields

movie is a category under which there arise subcategories like new release,latest song.I want to display in the same page categories and subcategories of different items on clicking the named categories.

Member Avatar
diafol
SELECT c.`name`,s.`subname` FROM categories AS c INNER JOIN subcategories AS s ON s.cat_id = c.id ORDER BY c.`name`, s.`subname`;

THe you run query and loop:

$output="";$head="";
while($data = mysql_fetch_assoc($result)){
   $output .= ($data['name'] != $head) ? "<h3>{$data['name']}</h3>" : "<p>{$data['subname']}</p>";
   $head = $data['name'];
}

echo $output;