Jump to content


Photo

how to display contents from 3 categories


  • Please log in to reply
18 replies to this topic

#1 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 08:17 PM

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>";


#2 .josh

.josh

    .josh

  • Staff Alumni
  • 14,828 posts

Posted 28 October 2013 - 08:24 PM

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.

Did I help you? Feeling generous? Donate to me! || Donate to phpfreaks!
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 08:28 PM

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 by afaaro, 28 October 2013 - 08:32 PM.


#4 .josh

.josh

    .josh

  • Staff Alumni
  • 14,828 posts

Posted 28 October 2013 - 08:32 PM

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.

Did I help you? Feeling generous? Donate to me! || Donate to phpfreaks!
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 08:35 PM

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



#6 .josh

.josh

    .josh

  • Staff Alumni
  • 14,828 posts

Posted 28 October 2013 - 08:39 PM

Change your WHERE clause to

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

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

Did I help you? Feeling generous? Donate to me! || Donate to phpfreaks!
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#7 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 08:44 PM

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



#8 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 08:51 PM

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



#9 objnoob

objnoob

    Advanced Member

  • Members
  • PipPipPip
  • 332 posts

Posted 28 October 2013 - 08:58 PM

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++;
}


#10 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 09:01 PM

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++;
}


#11 objnoob

objnoob

    Advanced Member

  • Members
  • PipPipPip
  • 332 posts

Posted 28 October 2013 - 09:07 PM

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");


#12 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 09:09 PM

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 by afaaro, 28 October 2013 - 09:11 PM.


#13 objnoob

objnoob

    Advanced Member

  • Members
  • PipPipPip
  • 332 posts

Posted 28 October 2013 - 09:17 PM

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.



#14 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 09:26 PM

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



#15 objnoob

objnoob

    Advanced Member

  • Members
  • PipPipPip
  • 332 posts

Posted 28 October 2013 - 09:52 PM

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 by objnoob, 28 October 2013 - 09:54 PM.


#16 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 28 October 2013 - 10:19 PM

    	$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']."&amp;id=".$data['id']."'><img src='".INFUSIONS."node/uploads/".$data['image']."'></a>";
		      }elseif ($data['link']) {
		        $image = "<a href='node.php?cat_id=".$data['catid']."&amp;id=".$data['id']."'><img src='".parse_youtube_url($data['link'],'thumb')."' /></a>"; 
		      } else {
		        $image = "<a href='node.php?cat_id=".$data['catid']."&amp;id=".$data['id']."'><img src='".INFUSIONS."node/images/nophoto.jpg' /></a>"; 
		      }
		      echo $image;
		      echo "<a class='gridtitle' href='node.php?cat_id=".$data['catid']."&amp;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)



#17 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 29 October 2013 - 03:09 PM

Anyone?



#18 afaaro

afaaro

    Advanced Member

  • Members
  • PipPipPip
  • 71 posts

Posted 13 February 2014 - 06:56 PM

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>";
    }


#19 objnoob

objnoob

    Advanced Member

  • Members
  • PipPipPip
  • 332 posts

Posted 19 February 2014 - 01:20 AM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com