Creating a Section of Products with Matching Attribute and Choice

[Duncan Rounding]

A bit of development I thought I'd share...

The following modification will populate a section in Actinic with all products in the catalog that have matching Attribute and Choice values. This is useful when wanting to have sections with perhaps clothing of specific colours or sizes from across all sections in the catalog.
---------------------------------------

Notes:
This will only work with Attributes and Choices, not with any Components and Permutations.
Duplicates are excluded.
On a large catalog the Section may take a short while to generate on preview and on upload, it may also result in a large page depending on the number of results.
The code is written for v10 but can easily be changed for different versions.
The method can easily be duplicated for other Attribute and Choices in other sections.
The code I'm sure can be neated and optimised but that's another day.
---------------------------------------

Continue at your own risk and Backup first!
---------------------------------------

Backup!

Create new variables, Attribute1 and Choice1, set their Place of Setting as Site.

Make a backup copy of actinic_main.php in the C:\Program Files\Actinic v10 directory.

Edit the actinic_main.php file and add the following line at the end of the file just before ?>

include_once("custom.php");

Create a new text file.
Place the following inside the file and save it as custom.php to the C:\Program Files\Actinic v10 directory:

<?php
function GetChoiceTrue($prodid, $att, $choice)
    {
       
        global $answer1;
	global $answer2;
       
        $connect = odbc_connect("ActinicCatalog10","","");
		
        $query1 = "SELECT [nPropertyID] FROM [ProductProperties] WHERE (([sProductRef]='".$prodid."') AND ([sString1]='".$att."'))";
        $result1 = odbc_exec($connect, $query1);
        while(odbc_fetch_row($result1)){
            $answer1 = odbc_result($result1, 1);
        }

	odbc_close($connect);
	
	$connect = odbc_connect("ActinicCatalog10","","");
		
	$query2 = "SELECT [sProductRef] FROM [ProductProperties] WHERE (([sString1]='".$choice."') AND ([nParentPropertyID]=$answer1))";
        $result2 = odbc_exec($connect, $query2);
        while(odbc_fetch_row($result2)){
            $answer2 = odbc_result($result2, 1);
        }
		
	odbc_close($connect);
		
        return $answer2;
    }
?>

Close and Open Actinic

In Settings - Site Options set Attribute1 to the value of the required Attribute name in Actinic
In Settings - Site Options set Choice1 to the value of the required Choice name in Actinic

Create a new Product List, name it as 'Product List Choice1' and add the following contents:

<actinic:block type="EntireSectionList" >
	<actinic:block type="ProductList" >
		<actinic:block php="true">
			$sChoiceTrue1 = GetChoiceTrue("<actinic:variable name="ProductID" selectable="false" encoding="perl"/>","<actinic:variable name="Attribute1" selectable="false" encoding="perl"/>","<actinic:variable name="Choice1" selectable="false" encoding="perl"/>");
		</actinic:block>
		<actinic:block if="%24sChoiceTrue1%20%3d%3d%20%3cactinic%3avariable%20name%3d%22ProductID%22%20selectable%3d%22false%22%20encoding%3d%22perl%22%2f%3e%20AND%20%3cactinic%3avariable%20name%3d%22ProductType%22%20%2f%3e%20%3d%3d%200" >
			<actinic:variable name="ProductLayout" />
		</actinic:block>
	</actinic:block> 
</actinic:block>

Create a new Secion in Actinic and add a new dummy product
Set the Section Layout - Product List Layout to be the new product list layout 'Product List Choice1'

Note that you may need to compact and close and open Actinic to see the results.
---------------------------------------

Et Voila - you now have a section with all products of say Attribute 'Size' and say Choice '6 x 4' from across the catalog.

[NormanRouxel]

Are you sure this works. There's a typo - selctable="false" - in the second block of code (which won't break things if you're not in the Design tab).

However, I've never been able to use a PHP variable in a BlockIf like your $sChoiceTrue1 in

<actinic:block if="%24sChoiceTrue1%20%3d%3d%20%3cactinic%3avariable%20name%3d%22ProductID%22%20selectable%3d%22false%22%20encoding%3d%22perl%22%2f%3e%20AND%20%3cactinic%3avariable%20name%3d%22ProductType%22%20%2f%3e%20%3d%3d%200" >

And I've tried and tried.

[Duncan Rounding]

I have it working, and the Blockif php variable certainly works for me with the example above.

Perhaps it's because it's already 'contained' in the Product and Section Lists.

I can send you the default store snapshot if you want to delve and see why it works in this case.

Typo fixed - thanks.

[Duncan Rounding]

There is one issue however - the database is caught and won't snapshot.
I'll check the odbc_close

Edit: Fixed - closed the database after first query then reopened again for the second one. Code updated above.

[Duncan Rounding]

I've added a download link in OP above to a zip file containing a working example in a v10 default store of two sections showing Size of 6x 4 and Colour of Yellow. The file also includes the related php files.

[NormanRouxel]

I'm testing your original code in V11 - using odbc_connect("ActinicCatalog11","","").

It doesn't seem to follow changes to the Products and their Attributes / Choices. I have to restart Actinic to get any changes to apply. Not sure if this is related to your fixed odbc_close or not. Have to go out now and will have another try later.

Getting that $sChoiceTrue1 to work in a BlockIf is a real mystery to me. I've never managed that.

[Duncan Rounding]

...I have to restart Actinic to get any changes to apply....

I've also had to close and reopen sometimes to force an update. Obviously expected with a change to custom.php but not with a change to the Product List.

I have however had similar issues of updating before with a simpler db queries used in the same way. (v10)

Note I have now included a download link above to a working v10 store.

[Duncan Rounding]

I've tested the method in v11 and it also works fine.
I've also included a link in the OP for v11 as well.

[NormanRouxel]

Duncan

A few suggestions:-

That might make life easier for those running Windows 7 (where tinkering within Program Files (x86) is difficult).
It also avoids the requirement to patch actinic_main.php.
And the patch can be applied selectively per-site for multi-site situations.
And it's Actinic version independent.

Undo tweaks to actinic_main.php.
Save the following as custom.php into your Site folder.

<?php
function GetChoiceTrue($prodid, $att, $choice, $ecMajorVersion)
    {
	$actinicdatabase = "ActinicCatalog" . $ecMajorVersion;
        global $answer1;
	global $answer2;
       
        $connect = odbc_connect($actinicdatabase,"","");
		
        $query1 = "SELECT [nPropertyID] FROM [ProductProperties] WHERE (([sProductRef]='".$prodid."') AND ([sString1]='".$att."'))";
        $result1 = odbc_exec($connect, $query1);
        while(odbc_fetch_row($result1)){
            $answer1 = odbc_result($result1, 1);
        }
		odbc_close($connect);
		
		$connect = odbc_connect($actinicdatabase,"","");
		
		$query2 = "SELECT [sProductRef] FROM [ProductProperties] WHERE (([sString1]='".$choice."') AND ([nParentPropertyID]=$answer1))";
        $result2 = odbc_exec($connect, $query2);
        while(odbc_fetch_row($result2)){
            $answer2 = odbc_result($result2, 1);
        }
		
		odbc_close($connect);
		
        return $answer2;
    }
?>

Note that the only change above is that the version number of Actinic is now passed in.
Amend the custom Product List layouts to be. E.g.

<actinic:block php="true">include_once("custom.php");</actinic:block>
<actinic:block type="EntireSectionList" >
	<actinic:block type="ProductList" >
		<actinic:block if="%3cactinic%3avariable%20name%3d%22ProductType%22%20%2f%3e%20%3d%3d%200">
			<actinic:block php="true">
				$sChoiceTrue1 = GetChoiceTrue("<actinic:variable name="ProductID" selectable="false" encoding="perl"/>","<actinic:variable name="Attribute1" selectable="false" encoding="perl"/>","<actinic:variable name="Choice1" selectable="false" encoding="perl"/>","<actinic:variable name="ECMajorVersion" selectable="false" />");
			</actinic:block>
			<actinic:block if="%24sChoiceTrue1%20%3d%3d%20%3cactinic%3avariable%20name%3d%22ProductID%22%20selectable%3d%22false%22%20encoding%3d%22perl%22%2f%3e" >
				<actinic:variable name="ProductLayout" />
			</actinic:block>
		</actinic:block>
	</actinic:block> 
</actinic:block>

Changes are that custom.php is loaded here instead of via actinic_main.php.
The version number is passed to custom.php to make it work on V9,10,11, etc.
I've moved the test for ProductType == 0 so the database query is only done on main products. Will save significant time on large sites.

This all works fine for me on my V11.
I still have to restart to get the lists to take note of updated products.

A final tweak is that you don't need Attribute2, Choice2 and layout Product List Choice2. Just allow variables Attribute1 and Choice1 to be settable at the Section Level. Now use the same code for all such Sections with just the variables changing to alter the selection.

[Duncan Rounding]

Thank you for the input. The changes make sense.

I tried setting attribute and choice at section level but it only worked on that particular section in the section list, ie not across the whole catalog.

[NormanRouxel]

Duncan

You're right about needing separate variables for each list. Code that should allow it to use a single list appears to compile but doesn't work properly.

[Duncan Rounding]

Another point to note - the custom.php file now needs to be placed in the Actinic Site folder.

The updated working code with two examples, Size and Colour, in the default v10 store can be downloaded here: http://goo.gl/3ijWn
The same two examples in the default v11 store can be downloaded here: http://goo.gl/3iH3p