I am still working on a datafeed importer... to keep the post short I am trying to build categories dynamically in my shopping cart.
here is an example of the datafeed field
Consumer Electronics|Cables|AV Cables|Audio/Video Cables
here is the sql for the shopping cart category table
CREATE TABLE `va_categories` (
`category_id` int(11) NOT NULL auto_increment,
`parent_category_id` int(11) NOT NULL default '0',
`category_path` varchar(255) NOT NULL default '',
`category_name` varchar(255) NOT NULL default '',
`category_order` int(11) NOT NULL default '1',
`is_showing` int(11) default '0',
`short_description` text,
`full_description` text,
`image` varchar(255) default NULL,
PRIMARY KEY (`category_id`,`category_name`),
KEY `category_path` (`category_path`),
KEY `parent_category_id` (`parent_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the categories are listed in the table like so....
category_id Parent_id category_path category_name
1 0 0, Consumer Electronics
2 1 0,1 Cables
3 2 0,1,2 AV Cables
4 3 0,1,2,3 Audio/Video Cables
I used the following code to put data into a temp table
$sql_show_category= "SELECT CATEGORIES, PRODUCT_ID FROM viart_import";
$database->Query($sql_show_category);
$result = $database->result;
$rows = $database->rows;
/*
ALTER TABLE mytable DROP myid;
ALTER TABLE mytable ADD myid INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (myid), AUTO_INCREMENT=1;
*/
//reset auto_increment value
$sql_clear = "TRUNCATE TABLE temp_categories";
$database->Query($sql_clear);
$sql_drop = "ALTER TABLE temp_categories DROP category_id";
$database->Query($sql_drop);
$sql_reset = "ALTER TABLE temp_categories ADD category_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (category_id), AUTO_INCREMENT=1";
$database->Query($sql_reset);
$num=0;
while($data = mysql_fetch_array($result)){
$categories = $data['CATEGORIES'];
$product_id = $data['PRODUCT_ID'];
$category_array = explode("|", $categories);
foreach ($category_array as $key => $value){
$offset = addslashes($category_array[$key]);
/* Insert all parsed categories into a temporary table and give them a unique ID number */
$sql_values = 'INSERT INTO temp_categories (`category_name`, `offset_key`, `parent_category_id`)VALUES(\''.$offset.'\', \''.$key.'\', \''.$key.'\')ON DUPLICATE KEY UPDATE category_name = category_name';
$database->Query($sql_values);
$results = $database->result;
$row_values = $database->rows;
}//end foreach loop
}//end while loop
here is the code for the temp table
[sql]
#----------------------------
# Table structure for temp_categories
#----------------------------
CREATE TABLE `temp_categories` (
`category_id` int(11) NOT NULL auto_increment,
`parent_category_id` int(11) NOT NULL default '0',
`product_ID` int(11) NOT NULL default '0',
`offset_key` int(2) NOT NULL default '0',
`category_path` varchar(255) default NULL,
`category_name` varchar(255) NOT NULL default '',
`category_order` int(11) default '1',
`is_showing` int(11) default '0',
`short_description` text,
`full_description` text,
`image` varchar(255) default NULL,
PRIMARY KEY (`category_id`),
UNIQUE KEY `category_name` (`category_name`),
KEY `parent_category_id` (`parent_category_id`),
KEY `category_path` (`category_path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[/sql]
this put all the categories into the table with a unique category_id
now I just need to set the parent_category_id and category path....
Someone please help me with this... I have been working on it for months and am losing my mind....
Thanks
