Right i think i've cracked this. For others wanting to do this, it's work in progress and you'd be better off playing with it on a test server. Still some bugs in it, but i'm looking for feedback/suggestions to improve it.
Firstly set up your database for categories like so:
CREATE TABLE IF NOT EXISTS `categories2` (
`id` int(11) NOT NULL,
`name` varchar(37) NOT NULL,
`parentid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;The following shows you the structure, Basically all top level categories will have the parentid of NULL. Then underneath with the other categories, you specify the id number the category it belongs too in the parentid. An example is below:
id | name | parentid
-------------------------------
1 | console | NULL
2 | pc | NULL
3 | wii | 1
4 | ps3 | 1
5 | accessories | 3
6 | games | 3
Also i made a category_mappings table instead of categories, my plan is to rename categories2 to categories and have the category_mappings and so on.
CREATE TABLE IF NOT EXISTS `mapped_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`alternates` text,
`pid` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;Now the second part is the mappings. I've set it up to basically split the categories which have the character > in it. It checks the alternatives first and if not present it'll add a > to the end which comes in handy when we actually map the product further down the line.
So in admin.php:
search for $alternatives and change:
$alternate = trim($alternate);to:
$alternate = strtolower(trim($alternate));Then search:
$importRecord["category"] = tapestry_normalise($importRecord["category"]);and replace with :
$importRecord["category"] = tapestry_normalise($importRecord["category"],">");Then replace:
/* apply category mappings */
if (isset($admin_importCategoryMappings["=".$importRecord["category"]]))
{
$importRecord["category"] = $admin_importCategoryMappings["=".$importRecord["category"]];
}
else
{
foreach($admin_importCategoryMappings as $k => $v)
{
if (substr($k,0,1) !== "=")
{
$found = 0;
$words = explode(" ",$k);
foreach($words as $word)
{
if ($word)
{
if (strpos($importRecord["category"],$word) !== FALSE) $found++;
}
}
if ($found == count($words))
{
$importRecord["category"] = $v;
break;
}
}
}
}with:
/* apply category mappings */
$category = $importRecord["category"];
$parts = explode(">",$category);
$maincat = $parts[0];
count($parts) > 1 ? $subcat = $parts[1] : $subcat = $parts[0];
if (isset($admin_importCategoryMappings["=".$importRecord["category"]]))
{
$importRecord["category"] = $admin_importCategoryMappings["=".$importRecord["category"]];
}
else
{
foreach($admin_importCategoryMappings as $k => $v)
{
$found = 0;
$words = explode(" ",$k);
foreach($words as $word)
{
if ($word)
{
if (strpos(strtolower($subcat),$word) !== FALSE) $found++;
}
}
if ($found == count($words))
{
$importRecord["category"] = $v;
break;
}
}
if($found == 0)
{
$importRecord["category"] = $category." >";
}
}Ok so that's the mapping part done.
Now i've created categoryadd.php for the time being but i will be integrating it into admin/categories.php eventually: Here it is:
Categoryadd.php
require("../includes/common.php");
$submit = (isset($_POST["submit"])?$_POST["submit"]:"");
$sub_cat = (isset($_POST["sub_category"])?$_POST["sub_category"]:"");
$nomap_cat = strtolower((isset($_POST["nomap_category"])?$_POST["nomap_category"]:""));
$main_cat = (isset($_POST["main_category"])?$_POST["main_category"]:"");
$msg = (isset($_GET["msg"])?$_GET["msg"]:"");
if ($msg == "1") {
echo "<div class='success'>Sucessfully added to database</div>";
}
function widget_posted($text)
{
if (get_magic_quotes_runtime())
{
return stripslashes($text);
}
else
{
return $text;
}
}
if ($submit == "Save")
{
$sql = "SELECT category FROM `".$config_databaseTablePrefix."products`
WHERE id='".database_safe($nomap_cat)."'";
database_querySelect($sql,$rows);
//get unmapped name and remove white fluff and >
$nomap_cat = str_replace(" >", "", ltrim($rows[0]["category"]));
//if no sub category assigned then use main category as cat
if($sub_cat != "") {
$category_id = $sub_cat;
} else {
$category_id = $main_cat;
}
$sql = "SELECT name FROM `".$config_databaseTablePrefix."categories2`
WHERE id='".database_safe($category_id)."'";
database_querySelect($sql,$rows);
//get cat name
$catname = $rows[0]["name"];
$sql = "SELECT pid FROM `".$config_databaseTablePrefix."mapped_categories`
WHERE pid='".database_safe($category_id)."'";
database_querySelect($sql,$rows);
//get pid name
$pid = $rows[0]["pid"];
//if found in database then update else insert new
if ($pid)
{
$sql = "UPDATE `".$config_databaseTablePrefix."mapped_categories` SET
alternates = CONCAT(alternates,'\n','"
.database_safe($nomap_cat)."')
WHERE pid='".database_safe($category_id)."'";
$found = 1;
database_queryModify($sql,$insertId);
} else {
$sql = "INSERT INTO `".$config_databaseTablePrefix."mapped_categories` SET
name = '".database_safe($catname)."',
alternates = '".$nomap_cat."',
pid = '".database_safe($category_id)."'";
$found = 1;
database_queryModify($sql,$insertId);
}
if ($found = 1) {
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET
category = '".$catname."', category_id= '".$category_id."' WHERE category LIKE '%".$nomap_cat."%'";
database_queryModify($sql,$insertId);
}
header("Location: Categoryadd.php?msg=1");
exit();
}
//populate unmapped categories which have the character >
$sql = "SELECT category, id FROM `".$config_databaseTablePrefix."products`
WHERE category LIKE '%>%' AND category != '' GROUP BY category LIMIT 15";
$options="";
if (database_querySelect($sql,$rows))
{
foreach($rows as $categories)
{
if ($categories["category"])
{
$id = $categories["id"];
$category = str_replace(" >", "", strtolower($categories[category]));
$options.="<option value=\"$id\">$category</option>\n";
}
}
}
//populate main category list
$sql = "SELECT name, id FROM `".$config_databaseTablePrefix."categories2` WHERE parentid IS NULL";
$options2="";
if (database_querySelect($sql,$rows))
{
foreach($rows as $categories)
{
if ($categories["name"])
{
$id = $categories["id"];
$category = $categories["name"];
$options2.="<option value=\"$id\">$category</option>\n";
}
}
}
?>
<html>
<head>
<script type="text/javascript" src="includes/javascript/js.js"></script>
</head>
<body>
<form name="category" method='post'>
<table frame="void" rules="none">
<tr>
<th><label for="unmapped category">Choose an unmapped category:</label></th>
<td>
<select name="nomap_category" id="nomap_category" onchange="extract();populate_category(main_category.value);">
<option value="0">-- select --</option>
<?=$options?>
</select>
</td>
</tr>
<tr>
<th><label for="Main categories">Choose a main category:</label></th>
<td>
<select name="main_category" id="main_category" onchange="populate_category(this.value);">
<option value="0">-- select --</option>
<?=$options2?>
</select>
</td>
</tr>
<tr id="subcat">
</tr>
<tr>
<td>
<input type='submit' name='submit' value='Save' /> <input type='button' name='unsure' value='unsure' onClick="check_product(nomap_category.value);"/>
</td>
</tr>
<div id="product">
</div>
</form>
</body>
</html>Create a folder called javascript in includes and a file called js.js (includes/javascript/js.js) and enter this:
js.js
function populate_category(str)
{
var xmlhttp;
if (str=="")
{
document.getElementById("subcat").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("subcat").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","Categorytake.php?cat="+str,true);
xmlhttp.send();
}
function check_product(str)
{
var xmlhttp;
if (str=="")
{
document.getElementById("product").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("product").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","Categoryproduct.php?product="+str,true);
xmlhttp.send();
}
function extract() {
var answer = document.getElementById("nomap_category").options[document.getElementById("nomap_category").selectedIndex].text;
<!-- This will allow you to automatically map certain categories using the unmapped word, it'll only work for certain keywords but comes in handy -->
if (answer.match("dvd"))
{
answer=1;
}
else if (answer.match("game")) {
answer=2;
}
else if (answer.match("wii")) {
answer=3;
}
else {
answer=0;
}
document.getElementById("main_category").selectedIndex = answer;
}Next we attach the ajax includes php files.
This file could do with a tidying up:
Categoryproduct.php
<?php
require("../includes/common.php");
$product = (isset($_GET["product"])?$_GET["product"]:"");
if ($product != "" || $product != "0") {
$sql = "SELECT name,description,image_url FROM `".$config_databaseTablePrefix."products`
WHERE id='".database_safe($product)."'";
database_querySelect($sql,$rows);
$product_name = $rows[0]["name"];
$product_desc = $rows[0]["description"];
$product_image = $rows[0]["image_url"];
$product="<td><strong>$product_name</strong></td>";
$product.="<td><img border='0' height='80' src='$product_image' /></td>";
$product.="<td>$product_desc</td>";
?>
<table width='90%' cellpadding='3'>
<tr bgcolor='#eeeeee'>
<th align='left'>Product Name</th>
<th align='left'>Image</th>
<th align='left'>Description</th>
</tr>
<tr bgcolor='#ffffcc'>
<?=$product?>
</tr>
</table>
<?php } ?>and then finally:
Categorytake.php
<?php
require("../includes/common.php");
$cat = (isset($_GET["cat"])?$_GET["cat"]:"");
if ($cat != ""){
//populate unmapped categories
$sql = "SELECT name, id FROM `".$config_databaseTablePrefix."categories2` WHERE parentid = '".database_safe($cat)."'";
$options="";
if (database_querySelect($sql,$rows))
{
foreach($rows as $categories)
{
if ($categories["name"])
{
$id = $categories["id"];
$category = $categories["name"];
$options.="<option value=\"$id\">$category</option>\n";
}
}
}
}
?>
<th><label for="Sub categories">Choose a sub category:</label></th>
<td>
<select name="sub_category" id="sub_category" name="sub_category">
<option value="0">-- select --</option>
<?=$options?>
</select>
</td>This is how far i've actually got at the moment, however it should be a simple case of adding a where statement to the search.php to recall the products based on parentid.
The reason i've posted this is i'd like some feedback and suggesstions from other members/david to see if i can re use some of price tapestries inner workings and reuse code where it's being duplicated.
I'll probably be posting updates when i complete it.
Thanks
Steve