Eric Nagel

Eric Nagel

CTO, PHP Programmer, Affiliate Marketer & IT Consultant

Custom Categories For Your Datafeed Site

I have been, or can be if you click on a link and make a purchase, compensated via a cash payment, gift, or something else of value for writing this post. Regardless, I only recommend products or services I use personally and believe will be good for my readers.

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:


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">
				<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>
				<td><input type="submit" name="cAction" value="Submit" /></td>
} // 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!

  • Mark John
    Posted March 15, 2010 4:35 pm 0Likes

    It’s pretty amazing to see someone giving tips like this to help other people become successful. Keep it up, Eric!

  • rickvalentine
    Posted March 26, 2010 9:27 am 0Likes

    Amazing post Eric! much appriciated for sharing

  • Trackback: Adding CJ Products To Your Datafeed Website | Eric Nagel
  • ben deal
    Posted June 15, 2010 12:58 pm 0Likes

    thanks very much, this info is well organised, easy to follow and extremely helpful

  • Jim
    Posted July 13, 2010 5:50 am 0Likes

    Or, if you have WordPress, put all your category name on the sidebar, and link each one to a search query of your blog. That’s what I did for the site Pretty basic, I know, but hey, it worked for that situation.

  • Justin
    Posted July 20, 2010 8:22 pm 0Likes

    Hmm very nice series you have written here. Do you know the legality of using text from merchants pages if they do not offer a datafeed?

  • Eric Nagel
    Posted July 21, 2010 11:52 am 0Likes

    @Justin – Ask the affiliate manager. They may not like a scraper running through their site, and would be happy to give you a partial database dump.

    That being said, I have a bot that scrapes title tags & meta data, but it caches it for a week & I only take the homepage. I don’t think a hit a week is going to hurt them. I use it for finding similar merchants (if 2 merchants have x or more identical meta keywords, maybe they’re related)

  • Rainmaker Bob
    Posted October 15, 2010 9:42 am 0Likes

    Hi Eric,
    Thanks for these tutorials. I’m about to give them a try but I need one more thing… A way to add – and keep – custom descriptions. I’d like to have the option to add my own description of the product so I have unique content – and keep it through updates.

    I would display default descriptions from merchant unless a custom written one from me exists – then it would display that one instead. (can be an automatic if/then ~OR~ a check box somewhere in the admin saying “use custom description for this product” – Whichever would load/process faster)

    I have a feeling the solution would be similar to above, with some minor tweaks.

    Can ya point me in the right direction?

    • Eric Nagel
      Posted October 22, 2010 3:16 pm 0Likes

      Hey Bob,

      I’ll write this up shortly, but yes – it’s very similar to this script for custom categories.

Leave A Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.