Page 1 of 1

Creating categories in table

Posted: Mon Nov 27, 2006 6:29 am
by cbrknight
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have alot of products I have to import into my shopping cart. Problem is I need to create my category structure on the fly. I have learned php on my own and this is just a little beyond my knowledge. Hopefully someone can help me. 

Basically the I have a .csv file of products I am importing into a table. One field contains the category structure in this format:

parent_category|child1|child2|child3  etc...

I am using the following code to pull that data into an array....

Code: Select all

while($data = mysql_fetch_array($result)){
	
	$categories = $data['CATEGORIES'];
	$category_array = explode("|", $categories);
result:

Code: Select all

Array
(
    [0] => Consumer Electronics
    [1] => TV and Video
    [2] => TVs
    [3] => 30 & Over
)

Array
(
    [0] => Home and Garden
    [1] => Furniture
    [2] => Other
)

Array
(
    [0] => Home and Garden
    [1] => Furniture
    [2] => Other
)
Here is the table structure for the categories in the database:

Code: Select all

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=MyISAM DEFAULT CHARSET=latin1;
anyone can tell me where to go from here. I need to get the $category_array parsed into the table.

Basically I need to get all the values in the array into the category_name field wth its own unique category_id...

Then I need to get the first level of the array as the parent_category_id and the rest of the array dimensions as the child categories. which is represented in the table in category_path like so

Code: Select all

parent_category = 0
child1 = 1
child2 = 2
child3 = 3

0,1
0,2
0,1,3

etc....
I hope this makes sense to somebody that can help me.... I am really in a bind trying to build this import application...


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Dec 08, 2006 1:05 pm
by cbrknight
Has anyone got any ideas about this... I have managed to get this far and I dont know if I am going in the wrong direction...

Code: Select all

$sql_show_category= "SELECT CATEGORIES, PRODUCT_ID FROM viart_import";
$database->Query($sql_show_category);
$result = $database->result;
$rows = $database->rows;
/*$data = $database->dbArray;*/
/*
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);
 	/*echo('<pre>');
	print_r($category_array);
	echo('</pre>');*/
	
	foreach ($category_array as $key => $value){
	$offset = addslashes($category_array[$key]);
	//echo "<br />Key: " . $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
$sql_ids = "SELECT category_id, category_name, offset_key FROM temp_categories";
	$database->Query($sql_ids);
	$id_result = $database->result;	
	/*echo('<pre>');
	print_r($category_array);
	echo('</pre>');	*/
}
basically what this has done is parsed the datafeed field and entered each individual category into a table with an individual category id by creating an index on category_name that was unique.

Now I need to figure out how to get the first element of the array as the parent_category_id and then get the category path to reflect the rest of the elements according to their ID numbers...

PLEASE HELP I am getting desparate....