Mar.12

Custom Categories For Your Datafeed Site

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!

How To & Tips
Share this Story:
  • facebook
  • twitter
  • gplus

Comments(9)

  1. Mark John
    2456 days ago

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

  2. rickvalentine
    2445 days ago

    Amazing post Eric! much appriciated for sharing

  3. Trackback: Adding CJ Products To Your Datafeed Website | Eric Nagel

  4. ben deal
    2364 days ago

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

  5. Jim
    2337 days ago

    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 onlineshoesoutlet.biz. Pretty basic, I know, but hey, it worked for that situation.

  6. Justin
    2329 days ago

    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?

  7. Eric Nagel
    2328 days ago

    @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)

  8. Rainmaker Bob
    2242 days ago

    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?
    Thanks!!

    • Eric Nagel
      2235 days ago

      Hey Bob,

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

Leave a comment

Comment