how to display contents from 3 categories

[afaaro]

I have tried this but no luck

$result = dbquery("SELECT o.*, c.id catid, c.name catname FROM ".NODE." o LEFT JOIN ".ARGUMENT." c ON c.id=o.category WHERE category='57,58,59,60' ORDER BY o.date DESC");
  echo "<table class='tbl-border' width='100%'>";
  echo "<tr><td class='tbl2'>Title</td><td class='tbl2'>Category</td></tr>";
    while ($row = dbarray($result)) {
        echo "<tr><td class='tbl'>".$row['name']."</td><td>".$row['catname']."</td></tr>";
        $i++;
 }
 echo "</table>";

[.josh]

it would be helpful if you provided more details, such as your db structure and more importantly what you expect the output to look like.. also, dbquery and dbarray are arbitrary functions; we have no idea on our end whether or not even those are working properly.

[afaaro]

dbquery stands  mysql_query,  dbarray stands mysql_fetch_assoc

 

".NODE."  is the post table

".ARGUMENT."  is the category table

 

$inf_newtable[1] = "".$db_prefix."nodeCategory (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`parent` int(11) NOT NULL default '0',
`body` text NOT NULL,
`image` varchar(255) NOT NULL default '',
`count` int(11) NOT NULL,
`locked` int(11) NOT NULL default '0',
`access` int(1) NOT NULL default '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;";


$inf_newtable[2] = "".$db_prefix."nodeObject (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`category` int(10) NOT NULL default '0',
`type` enum('news','video','audio') NOT NULL default 'news',
`link` varchar(255) NOT NULL default '',
`body` text NOT NULL,
`image` varchar(255) NOT NULL default '',
`date` int(10) UNSIGNED NOT NULL DEFAULT '0',
`author` int(10) UNSIGNED NOT NULL DEFAULT '1',
`count` int(10) NOT NULL,
`locked` int(10) NOT NULL default '0',
`access` int(1) NOT NULL default '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;";

Edited October 29, 2013 by afaaro

[.josh]

Well that confirms my assumption but not the code. Nor did you respond to the stuff before that. Bottom line is I can't help you without knowing what you actually want.

[afaaro]

I want to get posts table from 3 different category like the one i put  WHERE category='57,58,59,60' ORDER BY o.date DESC

[.josh]

Change your WHERE clause to

 

.. WHERE category IN(57,58,59,60) ..

 

This will select where the category is any of those values.

[afaaro]

Awsome, Thank you so much Josh. I was really searching for this about 3days. again thank you

[afaaro]

one more question if i want to display parent category and it's child and child contents is it possible in that way instead of listing category ids0

[objnoob]

Yes, the quickest way is to sort by category and check in the loop when you hit a new category.

$cat = null;  # no category processed yet
while ($row = dbarray($result)) {
   if($cat===null || $cat <> $row['catname']){
      // first category, or the category has changed
      echo "<tr><th>{$row['catname']}</th></tr>"; # print category name
      $cat = $row['catname']; # set $cat to the new category
   }
   echo "<tr><td class='tbl'>".$row['name']."</td></tr>";  # print the item
   $i++;
}

[afaaro]

is it like this

$result = dbquery("SELECT o.*, c.id catid, c.name catname FROM ".NODE." o LEFT JOIN ".ARGUMENT." c ON c.id=o.category WHERE category='57,58,59,60' ORDER BY o.date DESC");
echo "<table class='tbl-border' width='100%'>";
echo "<tr><td class='tbl2'>Title</td><td class='tbl2'>Category</td></tr>";
$cat = null; # no category processed yet
while ($row = dbarray($result)) {
if($cat===null || $cat <> $row['catname']){
// first category, or the category has changed
echo "<tr><th>{$row['catname']}</th></tr>"; # print category name
$cat = $row['catname']; # set $cat to the new category
}
echo "<tr><td class='tbl'>".$row['name']."</td></tr>"; # print the item
$i++;
}

[objnoob]

Well, if you're sorting by date...  ORDER BY o.date DESC   there's a chance of having duplicate categories sections.

Like, I said you should sort by category table primary key column ( and then by date if you want ? )

 $result = dbquery("SELECT o.*, c.id catid, c.name catname FROM ".NODE." o LEFT JOIN ".ARGUMENT." c ON c.id=o.category WHERE category='57,58,59,60' ORDER BY category, o.date DESC");

[afaaro]

Example i have this category:

 

main category1: 57

                        child: 58

                        child  59

                        child  60

 

main category1: 39

                       child  so on

 

 

there another category has 50 child category

Edited October 29, 2013 by afaaro

[objnoob]

Something about what you're saying doesn't add up...  

The categories 57, 58, 59, and 60 are all categories  included in the result set thanks to 

WHERE category IN (57,58,59, 60)

I don't understand when or where category 58 becomes a child of 57.

[afaaro]

I have 3 main categories and each category has unlimited parent category

 

1. category 1 has 4 child which this one category IN (57,58,59, 60)

2. category 2 has 20 child categories, i dont know wether to use like this category IN (1,,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)

3. category 3 has 20 child categories and child again

 

so how can i do it

[objnoob]

Consider having a single categories table.  In this table you would have a primary key that is auto incrementing.

You would have at least one other column to store the id of the parent category. If the category doesn't have a parent category you just set the parent_cat_id column to null.

 

Then you could do a self left join.

SELECT cat_id, cat.cat_name, parent_cat_id, parent.cat_name  FROM categories cat LEFT JOIN categories parent ON cat.parent_cat_id = parent.cat_id;

This will select all of the categories detailing the category id, the category name, the parent category id and parent category name (if there is a parent)

If you want to select all of the categories that are a child of category  with ID 1, then you can WHERE parent.cat_id = 1

 

SELECT cat_id, cat.cat_name, parent_cat_id, parent.cat_name  FROM categories cat LEFT JOIN categories parent ON cat.parent_cat_id = parent.cat_id WHERE parent.cat_id = 1;

Edited October 29, 2013 by objnoob

[afaaro]

    	$result = dbquery("SELECT o.name name, o.image image, o.type type, o.link link, o.id id, o.date, o.category, c.id catid, c.name catname FROM ".NODE." o LEFT JOIN ".ARGUMENT." c ON c.id=o.category WHERE type='video' AND category IN(39,40,41,42,43,44,45,46,47,48,49,50,62,63,64,65,66,67,68,69,70,71,72,73,74,75) ORDER BY date DESC LIMIT 40");

		$rows = dbcount("(id)", "".NODE."");
		if ($rows != 0) {
		  echo "<div class='video'>";
		    while ($data = dbarray($result)) { 
		      echo "<div class='grid'>";
		      if ($data['image']) {
		        $image = "<a href='node.php?cat_id=".$data['catid']."&id=".$data['id']."'><img src='".INFUSIONS."node/uploads/".$data['image']."'></a>";
		      }elseif ($data['link']) {
		        $image = "<a href='node.php?cat_id=".$data['catid']."&id=".$data['id']."'><img src='".parse_youtube_url($data['link'],'thumb')."' /></a>"; 
		      } else {
		        $image = "<a href='node.php?cat_id=".$data['catid']."&id=".$data['id']."'><img src='".INFUSIONS."node/images/nophoto.jpg' /></a>"; 
		      }
		      echo $image;
		      echo "<a class='gridtitle' href='node.php?cat_id=".$data['catid']."&id=".$data['id']."'>".trimlink($data['name'],30)."</a>";
		      echo "</div>";
		    }
		  echo "</div>";
		}

is there any better than this   category IN(39,40,41,42,43,44,45,46,47,48,49,50,62,63,64,65,66,67,68,69,70,71,72,73,74,75)

[afaaro]

Anyone?

[afaaro]

This will select all of the categories detailing the category id, the category name, the parent category id and parent category name (if there is a parent)

If you want to select all of the categories that are a child of category  with ID 1, then you can WHERE parent.cat_id = 1

 

SELECT cat_id, cat.cat_name, parent_cat_id, parent.cat_name  FROM categories cat LEFT JOIN categories parent ON cat.parent_cat_id = parent.cat_id WHERE parent.cat_id = 1;

 

 

Its working fine but it's not displaying the parent name here is the code

    $result = dbquery("SELECT cat.category_id, cat.category_name, cat.category_parent, parent.category_name
                       FROM ".CATEGORY." cat 
                       LEFT JOIN ".CATEGORY." parent ON cat.category_parent=parent.category_id 
                       WHERE parent.category_id=1 ORDER BY parent.category_name ASC");

    while($data = dbarray($result)){
        echo "<a href='".INFUSIONS."media/media.php?cid={$data['category_id']}'>".$data['category_name']."</a><br>";
    }

[objnoob]

probably because you have two columns with the same name  cat.category_name, parent.category_name

 

You should give one an alias using the  AS keyword:  

SELECT 
    cat.category_id, cat.category_name, cat.category_parent, parent.category_name AS parent_category_name
FROM ....

You'll then have both $data['category_name'] and $data['parent_category_name']  to work with.