[SOLVED] subcategory items won't show up in parent categories.

[gmas]

First of all hello everyone here 

 

I'm a newbie in coding, you have to say I have no idea what I'm doing, so I need some help from you experts

 

 

I'm having problem bringing out the items from the subcategory of the parent categories from this script I'm using.

 

They have

 

Table called Categories :

 

id    categoryname    parentcat    catid

-----------------------------------------------

2          Something        *4**12*#      *4**12**7*#

 

 

Table called Item :

 

id    catid          title          itemcomment

--------------------------------------------

22  *4**12**7*#  Blah  Blahinfo

 

 

The problem with their scripts is that they can get the items from the subcategory but the item won't show if you select the parent category.

Their top category is blank

Their catid goes like this *4**12**7*#  While their parentcat goes like this *4**12*#

 

 

This is one of the script they used to bring out the items, i think....

 

$extraquerystring="";

if ($incategories!=""){

$extraquerystring="AND catid IN(";

$catids = explode(",", $incategories);

foreach ($catids as $catid){



    $database->setQuery("SELECT * FROM #__seyret_categories WHERE id='$catid'");

	$cats = $database->loadObjectList();

	foreach ($cats as $cats) {

	$catlongid=$cats->catid;

	}

	$extraquerystring.="'$catlongid',";



}

$extraquerystring=substr($extraquerystring, 0, -1);

$extraquerystring.=")";


//echo $extraquerystring;


$extraquerystring=substr($extraquerystring, 0, -1);	

$extraquerystring.=")";

}

 

 

Anyway, it wont bring out all the items in the subcategory if you only enter the parentcategory. Can anyone help me get all the items within the subcategories if you select the main category?  I hope I made it clear enough.  Thanks a lot folks, I see it's more useful here than the other place I went to 

 

[Barand]

Not tested, but try something like

 

<?php
$sql = "SELECT i.catid, i.title 
	FROM item i
	INNER JOIN categories c1 ON i.catid = c1.catid
	LEFT JOIN categories c2 ON c1.parentcat = c2.catid
	WHERE (c2.catid = '$search') OR (c1.catid = '$search')";

[gmas]

I am so sorry to say that I don't know how to implement that >.<

 

I tried to put it in

 $database->setQuery("SELECT i.catid, i.title
	FROM items i
	INNER JOIN categories c1 ON i.catid = c1.catid
	LEFT JOIN categories c2 ON c1.parentcat = c2.catid
	WHERE (c2.catid = '$search') OR (c1.catid = '$search')");

but it didn't work, what else do i have to change.

[Barand]

I used the table names from your question. You need to substitute column and table names that exist in the database

[gmas]

yes i did change the names when i did it. I didn't know where to put your code tho... I think.

 

 

I believe that I can just add a parentcat into each item and call it from that but I do not know how to input parentcat automatically everytime they put an item into a sub category.  For example if they select to put it into subcategory1 it would read the parentcat of the subcategory1 and put that into parentcat on the item table. Would that be an easier solution.

 

Table called Categories :

 

id        categoryname        parentcat        catid

-----------------------------------------------

2          Something         *4**12*#      *4**12**7*#

 

 

Table called Item :

 

id        catid              title           itemcomment  parentcat

---------------------------------------------------------

22     *4**12**7*#     Blah           Blahinfo        *4**12*#

[Barand]

Having set up a couple of table, the query can be simplified. I am not familiar with the class you are using, but this works

 

<?php
include 'db.php';    // db connect stuff

$search = 1;           // cat or subcat to search for

$sql = "SELECT i.catid, i.title 
	FROM item i
	INNER JOIN categories c ON i.catid = c.catid
	WHERE (c.parentcat = '$search') OR (c.catid = '$search')";
$res = mysql_query($sql) or die (mysql_error());
while (list($cat, $name) = mysql_fetch_row($res))
{
echo "$cat $name <br/>";
}        
?>

[gmas]

YES it's working! kind of.... =p

 

out of wierdness it only showed one of the many items.  I got your logic and implemented it into my earlier code

 

$extraquerystring="";

if ($incategories!=""){

$extraquerystring="AND catid IN(";

$catids = explode(",", $incategories);

foreach ($catids as $catid){



    //--------



    $database->setQuery("SELECT *  FROM items  INNER JOIN categories ON items.catid=categories.catid
WHERE categories.parentcat='$catid'");

	$cats = $database->loadObjectList();

	foreach ($cats as $cats) {

	$catlongid=$cats->catid;

	}

	$extraquerystring.="'$catlongid',";



}

$extraquerystring=substr($extraquerystring, 0, -1);

$extraquerystring.=")";



//echo $extraquerystring;



$extraquerystring=substr($extraquerystring, 0, -1);

$extraquerystring.=")";

}

  I thank you for your guide, now I need to find out what factor is it that it only show one of the many items. >.< no i didn't limit it to 1 xD

[gmas]

I still can't figure it out but changing from INNER JOIN to LEFT / RIGHT change some of the items that appears. 

[Barand]

INNER vs LEFT JOIN

[pre]

table A                    table B

+-----+------------+        +-----+------------+--------+

| id  |  category  |        | id  |  item      | catid  |

+-----+------------+        +-----+------------+--------+

  1 cat aaa              1      item 1      1

  2    cat bbb              2      item 2      1

  3    cat ccc              3      item 3      2

                              4      item 4      2

                              5      item 5      2

                             

SELECT A.category, B.item FROM A INNER JOIN B ON A.id = B.catid

 

+------------+------------+

|  category  |  item      |

+------------+------------+

  cat aaa      item 1          (only matched rows from A and B)

  cat aaa      item 2   

  cat bbb      item 3   

  cat bbb      item 4   

  cat bbb      item 5   

 

SELECT A.category, B.item FROM A LEFT JOIN B ON A.id = B.catid

 

+------------+------------+

|  category  |  item      |

+------------+------------+     

  cat aaa      item 1          (all rows from A, NULL if no match in B)

  cat aaa      item 2   

  cat bbb      item 3   

  cat bbb      item 4   

  cat bbb      item 5   

  cat ccc      NULL

[gmas]

I still haven't figured it out lol. it just show from one subcategory or 2 but not all of it

The earlier code can draw from one subcategory, while this following code show a different subcategory. The items in the subcategies are about the same and all the catid matched each other and there's no null items yet this is not running right. 

$database->setQuery("SELECT items.*, categories.parentcat, categories.catid


FROM items
RIGHT OUTER JOIN categories
ON categories.catid = items.catid
WHERE categories.parentcat='$catid'");

[Barand]

Well, when we don't know what data you have in the tables, and we don't know what results you expect or are getting, then "not running right" doesn't tell us anything.