Eric Nagel

I’m so happy to hear from affiliates who have followed in the datafeed series and created websites of their own! The other day, Jen Goode showed me a site she was working on, and brought up a common problem with datafeeds: how to categorize the products.

I was able to do this using simple like queries:

select * from products where MerchantSubcategory like '%green tea%' or MerchantCategory like '%green tea%' order by Name;

But sometimes this isn’t the case. Sometimes you have to (gasp!) manually categorize the products. To do this, start with some changes to the products table:

ALTER TABLE  `products` ADD  `MyCategory` VARCHAR( 10 ) NOT NULL ;
ALTER TABLE  `products` ADD INDEX (  `MyCategory` ) ;

Then create an admin page for you to categorize the products. Use the admin-sas-datafeed.php as your template, but change the inside code to:

<?php

if (!empty($_POST['ProductID']) && !empty($_POST['MyCategory'])) {
	mysql_query("update products set MyCategory='" . myres($_POST['MyCategory']) . "' where ProductID=" . (int)$_POST['ProductID'] . " limit 1");
	echo("<p>The product has been updated.</p>");
} // ends

$cQuery = "select * from products where MyCategory='' limit 1";
$oResult = mysql_query($cQuery);
if ($rsData = mysql_fetch_array($oResult)) {
	?>
	<form name="productForm" method="post" action="<?= $_SERVER['PHP_SELF'] ?>">
		<input type="hidden" name="ProductID" value="<?= (int)$rsData['ProductID'] ?>" />
		<table width="500">
			<tr>
				<td><a href="/item.php?ProductID=<?= (int)$rsData['ProductID'] ?>" target="_blank"><?= $rsData['Name'] ?></a>: </td>
				<td><select name="MyCategory" onChange="document.forms['productForm'].submit();">
					<option value="">Choose</option>
					<option value="green-tea">Green Tea</option>
					<option value="white-tea">White Tea</option>
					<option value="black-tea">Black Tea</option>
					<option value="oolong-tea">Oolong Tea</option>
					<option value="tea-pot">Tea Pot</option>
					<option value="tea-cup">Tea Cup</option>
					<option value="junk">Do Not Use</option>
				</select></td>
			</tr>
			<tr>
				<td></td>
				<td><input type="submit" name="cAction" value="Submit" /></td>
			</tr>
		</table>
	</form>
	<?php
} // ends if ($rsData = mysql_fetch_array($oResult))
else {
	echo("<p><strong>Congratulations</strong>! All products have been categorized!</p>");
} // ends else from if ($rsData = mysql_fetch_array($oResult))
?>

What this does is update the “MyCategory” field for each product. Then, your SQL query on the browse page is simply:

select * from products where MyCategory='green-tea' order by Name;

Don’t worry – when you update the datafeed (by downloading a new zip file and uploading it in the admin tool you built before), the MyCategory field isn’t touched!

Now you can categorize products however you’d like – enjoy!