List All Products in Section

[sandymra]

Hello everybody!
I am trying for hours to figure out how actinic stores the data in the catalog.
I need to list all products from one section (not products in subsections).
Until now i figure out that sections are in the table "Catalog section", and they have nparentsectionID=0.
Table product and table catalog section are related by nparentsectionid=nsectionid.
But here i come to the problem.
I can't figure out how subsections are related to sections in the table "Catalog section".If i want to list products in the section i will get only the products that are directly in the section, and not the ones that are in the subsections but belonging to the section.
This is really confusing and any help will be appreciated.

[malbro]

Hello everybody!
I am trying for hours to figure out how actinic stores the data in the catalog.
I need to list all products from one section (not products in subsections).
Until now i figure out that sections are in the table "Catalog section", and they have nparentsectionID=0.
Table product and table catalog section are related by nparentsectionid=nsectionid.
But here i come to the problem.
I can't figure out how subsections are related to sections in the table "Catalog section".If i want to list products in the section i will get only the products that are directly in the section, and not the ones that are in the subsections but belonging to the section.
This is really confusing and any help will be appreciated.

Each section (subsections are sections for this discussion) has its own Id and a parent ID which may be 0 if it is a top level section or any other number if it is a subsection, you can work your way back up a section tree from a given section by looking in its record for the parent, then look in that sections record to find the parent then look in that sections record for the parent etc. until the parent is zero. At this point you are at the top level.

In the Product table each product has a record and in that record you have the ID of the parent section. All products in the same section have the same parent ID, if the parent ID of two sections are different they are not in the same section.

To list all the products in a section write an SQL query of the Products table looking for all Product ID's where the Parent ID is the section you want.

If you do not know the section ID you can find it using the Section Name to query the Catalog Section Table.

If you search the Forum looking for Gabes Free Nav you will find PHP code that shows how to do all this and more.

[NormanRouxel]

I need to list all products from one section (not products in subsections).

No SQL or PHP needed. Here we list all products in Section "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
	</actinic:block>
</actinic:block>

If you also want all product from all Sub Sections of Cameras (your post seems to contradict itself as to whether you want this) then it's more complex.

UPDATE:

This will display all products from Section "Cameras" and the next 3 sub-Sections deep from "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
		<actinic:block type="ChildSectionList">
			<actinic:block type="ProductList">
				<br/><actinic:variable name="ProductName" />
			</actinic:block>
			<actinic:block type="ChildSectionList">
				<actinic:block type="ProductList">
					<br/><actinic:variable name="ProductName" />
				</actinic:block>
					<actinic:block type="ChildSectionList">
						<actinic:block type="ProductList">
							<br/><actinic:variable name="ProductName" />
						</actinic:block>
					</actinic:block>		
			</actinic:block>		
		</actinic:block>
	</actinic:block>
</actinic:block>

[sandymra]

Thank you all!
I figure out how to do it just some hours after posting, but i thank you a lot for helping me. In case somebody needs it also this is how is the structure:
1. Sections have nparentid=0 . Each section that has nparentsectionid=0 has unique nsectionid. It's identifier of section.
2. SubSections - each subsection has nparentsectionid number which indicates nsectionid of it's upper section. For example: nparentsectionid=29. That subsection belongs to the section that has nsectionid=29.
3. Listing all products - as Marlbro said previously first step is to look in the table products, all products that have nparentid as section. Example:in the table catalog section there is section -cameras - nsectionid=29. Find all products from table products that have nparentsectionid=29.
But here is the trick also. Some products will have nparentsectionid of subsection. So you won't select all products. What you need to do is to make query that will join table products with table catalog section and to get nparentsectionid from nsectionid's of table catalog. Example:
Product.nparentsectionid=20,catalog section.nsectionid=20,[catalog section].nparentsectionid=29. It means product also belongs to the section where nsectionid=29.

And general example for section - cameras - nsectionid=29

1. Select nsectionid,nparentsectionid from [catalog section] where
nparentsectionid=29
2. Join this query result with product table with relating nsectionid-nparentsectionid

3. Select nparentsectionid from product where nparentsectionid=29

4. Union query results and you will get a list of all products.

It's how i did and i hope this can help somebody, as i lost many hours trying to figure out.

[globalee]

No SQL or PHP needed. Here we list all products in Section "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
	</actinic:block>
</actinic:block>

If you also want all product from all Sub Sections of Cameras (your post seems to contradict itself as to whether you want this) then it's more complex.

UPDATE:

This will display all products from Section "Cameras" and the next 3 sub-Sections deep from "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
		<actinic:block type="ChildSectionList">
			<actinic:block type="ProductList">
				<br/><actinic:variable name="ProductName" />
			</actinic:block>
			<actinic:block type="ChildSectionList">
				<actinic:block type="ProductList">
					<br/><actinic:variable name="ProductName" />
				</actinic:block>
					<actinic:block type="ChildSectionList">
						<actinic:block type="ProductList">
							<br/><actinic:variable name="ProductName" />
						</actinic:block>
					</actinic:block>		
			</actinic:block>		
		</actinic:block>
	</actinic:block>
</actinic:block>

Hi Norman, is it possible to have instead of "cameras" a variable SectionID in the code so it will automatically display products in the subsections automatically acording to what section you are in rather than manually entering in the section name, as i have many sections and subsections i need to apply this to, and wish to just place the code in the layout without having to manualy specify the section.

I tried to a custom variable in place of "Cameras" for the section in the block if statement... but this didint work.

Cheers, IN ADVANCE, Lee

[globalee]

No SQL or PHP needed. Here we list all products in Section "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
	</actinic:block>
</actinic:block>

If you also want all product from all Sub Sections of Cameras (your post seems to contradict itself as to whether you want this) then it's more complex.

UPDATE:

This will display all products from Section "Cameras" and the next 3 sub-Sections deep from "Cameras":

<actinic:block type="EntireSectionList">
	<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionName%22%20%2f%3e%20%3d%3d%20%22Cameras%22" >
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
		<actinic:block type="ChildSectionList">
			<actinic:block type="ProductList">
				<br/><actinic:variable name="ProductName" />
			</actinic:block>
			<actinic:block type="ChildSectionList">
				<actinic:block type="ProductList">
					<br/><actinic:variable name="ProductName" />
				</actinic:block>
					<actinic:block type="ChildSectionList">
						<actinic:block type="ProductList">
							<br/><actinic:variable name="ProductName" />
						</actinic:block>
					</actinic:block>		
			</actinic:block>		
		</actinic:block>
	</actinic:block>
</actinic:block>

ps, also would it be possible to limit the count to say '15" or any given number of products displayed??. All products in these subsections are duplicates and some will appear more than once in the subsections, so also to only include one instance of duplicate, if it is already in the given list.

sorry know its a big ask

[Duncan Rounding]

You can test for the duplicateindex to exclude duplicate products.
<actinic:variable name="DuplicateIndex" /> > 0 will be true if the product is a duplicate for example.

[NormanRouxel]

As I read this, you want to take a bit of code that does one specific thing and have 3 bespoke customisations.

1) Work anywhere listing sub-sections of the current Section only.

2) Limit to 15 products.

3) Automatically exclude products if they've already been listed.

Unfortunately, that's a good few hours work and way beyond what I'm willing to provide, even on Christmas day. You'll need some PHP skill to do (2) and (3).

I'll show you how to do (1) to get you started.

<actinic:block type="ChildSectionList">
	<actinic:block type="ProductList">
		<br/><actinic:variable name="ProductName" />
	</actinic:block>
	<actinic:block type="ChildSectionList">
		<actinic:block type="ProductList">
			<br/><actinic:variable name="ProductName" />
		</actinic:block>
			<actinic:block type="ChildSectionList">
				<actinic:block type="ProductList">
					<br/><actinic:variable name="ProductName" />
				</actinic:block>
			</actinic:block>		
	</actinic:block>		
</actinic:block>

Which is conveniently the middle of the lump of code from post #3.