help on Database operations (SQL)

[mondobongo3]

I want to COPY all structure of one Top-Level(X) category in another Top-Level(Y) category. This includes all inner levels of categories with all products of X.

 

How do I do it using sql ?

 

Thank you.

[mondobongo3]

I want to COPY all structure of one Top-Level(X) category in another Top-Level(Y) category. This includes all inner levels of categories with all products of X.

 

How do I do it using sql ?

 

Thank you.

 

Is it so difficult to do and nobody knows? :(

[MrPhil]

I doubt you'll find a single SQL command to do such a thing (unless I badly misunderstand what you're trying to do). It's not clear whether you want the same products appearing in multiple categories, or that these will be new (separate) product entries. I would EXPORT your database to a text .sql file, isolate the records in question, update them with new product IDs, etc. as needed, and then IMPORT the .sql file (to INSERT the new records). Practice on a copy of your live store's database, as it will probably take a few iterations to get it right. For auto-incremented fields such as *_IDs, you may have to either manually update the "autoincrement" value (the next ID to be inserted), or let the import process create new IDs and then manually patch references in other tables' .sql records before importing them. You may still end up with some circular references, where A includes a foreign key of B, and B includes a foreign key of A, which will have to be patched after the records are inserted. Needless to say, all of this requires a good understanding of osC's database schema and how to do SQL queries.

 

Have you looked at osC's move product or copy product functions? I think they exist in some versions (add-ons?), and may do what you want, but only one product at a time. It would certainly be easier than directly mucking about with the database!

[mondobongo3]

I doubt you'll find a single SQL command to do such a thing (unless I badly misunderstand what you're trying to do). It's not clear whether you want the same products appearing in multiple categories, or that these will be new (separate) product entries. I would EXPORT your database to a text .sql file, isolate the records in question, update them with new product IDs, etc. as needed, and then IMPORT the .sql file (to INSERT the new records). Practice on a copy of your live store's database, as it will probably take a few iterations to get it right. For auto-incremented fields such as *_IDs, you may have to either manually update the "autoincrement" value (the next ID to be inserted), or let the import process create new IDs and then manually patch references in other tables' .sql records before importing them. You may still end up with some circular references, where A includes a foreign key of B, and B includes a foreign key of A, which will have to be patched after the records are inserted. Needless to say, all of this requires a good understanding of osC's database schema and how to do SQL queries.

 

Have you looked at osC's move product or copy product functions? I think they exist in some versions (add-ons?), and may do what you want, but only one product at a time. It would certainly be easier than directly mucking about with the database!

 

Well, I am aware about "copy product" and "move product". And I don't expect a single command for this operation. But I don't know sql at all. So, what I want is to go in phpMyAdmin and run some sql statements which will do the operations for me.

Yes, I need same products to appear in more categories. The structure is like this:

 

TopLevel_1
       -lots of sub-categories and products

TopLevel_2
       -nothing here yet
TopLevel_3
       -nothing here yet
...
TopLevel_8
       -nothing here yet

 

So, From TopLEvel_1 I need to copy EVERYTHING (subcategories structure, products) in other TopLevel categories where is nothing yet...

I agree with you that for such kind of opperations is needed a strong knowledge of osC db schema and SQL :D , but I have neither, this is why I'm asking here.

[jazzyrosez]

Well, I am aware about "copy product" and "move product". And I don't expect a single command for this operation. But I don't know sql at all. So, what I want is to go in phpMyAdmin and run some sql statements which will do the operations for me.

Yes, I need same products to appear in more categories. The structure is like this:

 

TopLevel_1
       -lots of sub-categories and products

TopLevel_2
       -nothing here yet
TopLevel_3
       -nothing here yet
...
TopLevel_8
       -nothing here yet

 

So, From TopLEvel_1 I need to copy EVERYTHING (subcategories structure, products) in other TopLevel categories where is nothing yet...

I agree with you that for such kind of opperations is needed a strong knowledge of osC db schema and SQL :D , but I have neither, this is why I'm asking here.

 

Hm.. Why are you copying mufti-items into many subcategories? Normally, if you do have a product that can be categories in different areas. The copy button is your best friend. The way you are going to do it will cause your database to lock up trying to process the information for a product. If someone search for a product it will bring back X many of the same product. Why do they want X many of the same product?

 

I would suggest to learn SQL.

[mondobongo3]

Hm.. Why are you copying mufti-items into many subcategories? Normally, if you do have a product that can be categories in different areas. The copy button is your best friend. The way you are going to do it will cause your database to lock up trying to process the information for a product. If someone search for a product it will bring back X many of the same product. Why do they want X many of the same product?

 

I would suggest to learn SQL.

Well, this is a very particular situation, and yes, you are right... the search will return identical products from many categories.

Thanks for your suggestion :D, but I need this operation just one time, to save time. I have about 500 products and 11 Top-Level categories. This means more than 5000 entries. This is a really hard work to do it by hand.

 

I hoped that is a general solution for my problem, because I think that all oscommerce dbs have same schema (if there is no particular customization).

[jazzyrosez]

Well, this is a very particular situation, and yes, you are right... the search will return identical products from many categories.

Thanks for your suggestion :D, but I need this operation just one time, to save time. I have about 500 products and 11 Top-Level categories. This means more than 5000 entries. This is a really hard work to do it by hand.

 

I hoped that is a general solution for my problem, because I think that all oscommerce dbs have same schema (if there is no particular customization).

 

Hmm.. I'm use to just SQL not mySQL but my mother swears it similar. I'll look this up if I get some time. I would tell you should back up your shop before "tweaking" your database. I'm not expert at this.

[jazzyrosez]

Hmm.. I'm use to just SQL not mySQL but my mother swears it similar. I'll look this up if I get some time. I would tell you should back up your shop before "tweaking" your database. I'm not expert at this.

Okie here is some of the code that does this in oscommerce. Look through this and it should give you some ideas.

$heading = array();

$contents = array();

switch ($action) {

case 'new_category':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_NEW_CATEGORY . '</b>');

 

$contents = array('form' => tep_draw_form('newcategory', FILENAME_CATEGORIES, 'action=insert_category&cPath=' . $cPath, 'post', 'enctype="multipart/form-data"'));

$contents[] = array('text' => TEXT_NEW_CATEGORY_INTRO);

 

$category_inputs_string = '';

$languages = tep_get_languages();

for ($i = 0, $n = sizeof($languages); $i < $n; $i++) {

$category_inputs_string .= '<br>' . tep_image(DIR_WS_CATALOG_LANGUAGES . $languages[$i]['directory'] . '/images/' . $languages[$i]['image'], $languages[$i]['name']) . ' ' . tep_draw_input_field('categories_name[' . $languages[$i]['id'] . ']');

}

 

$contents[] = array('text' => '<br>' . TEXT_CATEGORIES_NAME . $category_inputs_string);

$contents[] = array('text' => '<br>' . TEXT_CATEGORIES_IMAGE . '<br>' . tep_draw_file_field('categories_image'));

$contents[] = array('text' => '<br>' . TEXT_SORT_ORDER . '<br>' . tep_draw_input_field('sort_order', '', 'size="2"'));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_save.gif', IMAGE_SAVE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'edit_category':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_EDIT_CATEGORY . '</b>');

 

$contents = array('form' => tep_draw_form('categories', FILENAME_CATEGORIES, 'action=update_category&cPath=' . $cPath, 'post', 'enctype="multipart/form-data"') . tep_draw_hidden_field('categories_id', $cInfo->categories_id));

$contents[] = array('text' => TEXT_EDIT_INTRO);

 

$category_inputs_string = '';

$languages = tep_get_languages();

for ($i = 0, $n = sizeof($languages); $i < $n; $i++) {

$category_inputs_string .= '<br>' . tep_image(DIR_WS_CATALOG_LANGUAGES . $languages[$i]['directory'] . '/images/' . $languages[$i]['image'], $languages[$i]['name']) . ' ' . tep_draw_input_field('categories_name[' . $languages[$i]['id'] . ']', tep_get_category_name($cInfo->categories_id, $languages[$i]['id']));

}

 

$contents[] = array('text' => '<br>' . TEXT_EDIT_CATEGORIES_NAME . $category_inputs_string);

$contents[] = array('text' => '<br>' . tep_image(DIR_WS_CATALOG_IMAGES . $cInfo->categories_image, $cInfo->categories_name) . '<br>' . DIR_WS_CATALOG_IMAGES . '<br><b>' . $cInfo->categories_image . '</b>');

$contents[] = array('text' => '<br>' . TEXT_EDIT_CATEGORIES_IMAGE . '<br>' . tep_draw_file_field('categories_image'));

$contents[] = array('text' => '<br>' . TEXT_EDIT_SORT_ORDER . '<br>' . tep_draw_input_field('sort_order', $cInfo->sort_order, 'size="2"'));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_save.gif', IMAGE_SAVE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&cID=' . $cInfo->categories_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'delete_category':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_DELETE_CATEGORY . '</b>');

 

$contents = array('form' => tep_draw_form('categories', FILENAME_CATEGORIES, 'action=delete_category_confirm&cPath=' . $cPath) . tep_draw_hidden_field('categories_id', $cInfo->categories_id));

$contents[] = array('text' => TEXT_DELETE_CATEGORY_INTRO);

$contents[] = array('text' => '<br><b>' . $cInfo->categories_name . '</b>');

if ($cInfo->childs_count > 0) $contents[] = array('text' => '<br>' . sprintf(TEXT_DELETE_WARNING_CHILDS, $cInfo->childs_count));

if ($cInfo->products_count > 0) $contents[] = array('text' => '<br>' . sprintf(TEXT_DELETE_WARNING_PRODUCTS, $cInfo->products_count));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_delete.gif', IMAGE_DELETE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&cID=' . $cInfo->categories_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'move_category':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_MOVE_CATEGORY . '</b>');

 

$contents = array('form' => tep_draw_form('categories', FILENAME_CATEGORIES, 'action=move_category_confirm&cPath=' . $cPath) . tep_draw_hidden_field('categories_id', $cInfo->categories_id));

$contents[] = array('text' => sprintf(TEXT_MOVE_CATEGORIES_INTRO, $cInfo->categories_name));

$contents[] = array('text' => '<br>' . sprintf(TEXT_MOVE, $cInfo->categories_name) . '<br>' . tep_draw_pull_down_menu('move_to_category_id', tep_get_category_tree(), $current_category_id));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_move.gif', IMAGE_MOVE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&cID=' . $cInfo->categories_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'delete_product':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_DELETE_PRODUCT . '</b>');

 

$contents = array('form' => tep_draw_form('products', FILENAME_CATEGORIES, 'action=delete_product_confirm&cPath=' . $cPath) . tep_draw_hidden_field('products_id', $pInfo->products_id));

$contents[] = array('text' => TEXT_DELETE_PRODUCT_INTRO);

$contents[] = array('text' => '<br><b>' . $pInfo->products_name . '</b>');

 

$product_categories_string = '';

$product_categories = tep_generate_category_path($pInfo->products_id, 'product');

for ($i = 0, $n = sizeof($product_categories); $i < $n; $i++) {

$category_path = '';

for ($j = 0, $k = sizeof($product_categories[$i]); $j < $k; $j++) {

$category_path .= $product_categories[$i][$j]['text'] . ' > ';

}

$category_path = substr($category_path, 0, -16);

$product_categories_string .= tep_draw_checkbox_field('product_categories[]', $product_categories[$i][sizeof($product_categories[$i])-1]['id'], true) . ' ' . $category_path . '<br>';

}

$product_categories_string = substr($product_categories_string, 0, -4);

 

$contents[] = array('text' => '<br>' . $product_categories_string);

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_delete.gif', IMAGE_DELETE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'move_product':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_MOVE_PRODUCT . '</b>');

 

$contents = array('form' => tep_draw_form('products', FILENAME_CATEGORIES, 'action=move_product_confirm&cPath=' . $cPath) . tep_draw_hidden_field('products_id', $pInfo->products_id));

$contents[] = array('text' => sprintf(TEXT_MOVE_PRODUCTS_INTRO, $pInfo->products_name));

$contents[] = array('text' => '<br>' . TEXT_INFO_CURRENT_CATEGORIES . '<br><b>' . tep_output_generated_category_path($pInfo->products_id, 'product') . '</b>');

$contents[] = array('text' => '<br>' . sprintf(TEXT_MOVE, $pInfo->products_name) . '<br>' . tep_draw_pull_down_menu('move_to_category_id', tep_get_category_tree(), $current_category_id));

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_move.gif', IMAGE_MOVE) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

case 'copy_to':

$heading[] = array('text' => '<b>' . TEXT_INFO_HEADING_COPY_TO . '</b>');

 

$contents = array('form' => tep_draw_form('copy_to', FILENAME_CATEGORIES, 'action=copy_to_confirm&cPath=' . $cPath) . tep_draw_hidden_field('products_id', $pInfo->products_id));

$contents[] = array('text' => TEXT_INFO_COPY_TO_INTRO);

$contents[] = array('text' => '<br>' . TEXT_INFO_CURRENT_CATEGORIES . '<br><b>' . tep_output_generated_category_path($pInfo->products_id, 'product') . '</b>');

$contents[] = array('text' => '<br>' . TEXT_CATEGORIES . '<br>' . tep_draw_pull_down_menu('categories_id', tep_get_category_tree(), $current_category_id));

$contents[] = array('text' => '<br>' . TEXT_HOW_TO_COPY . '<br>' . tep_draw_radio_field('copy_as', 'link', true) . ' ' . TEXT_COPY_AS_LINK . '<br>' . tep_draw_radio_field('copy_as', 'duplicate') . ' ' . TEXT_COPY_AS_DUPLICATE);

$contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_copy.gif', IMAGE_COPY) . ' <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');

break;

default:

if ($rows > 0) {

if (isset($cInfo) && is_object($cInfo)) { // category info box contents

$category_path_string = '';

$category_path = tep_generate_category_path($cInfo->categories_id);

for ($i=(sizeof($category_path[0])-1); $i>0; $i--) {

$category_path_string .= $category_path[0][$i]['id'] . '_';

}

$category_path_string = substr($category_path_string, 0, -1);

 

$heading[] = array('text' => '<b>' . $cInfo->categories_name . '</b>');

 

$contents[] = array('align' => 'center', 'text' => '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $category_path_string . '&cID=' . $cInfo->categories_id . '&action=edit_category') . '">' . tep_image_button('button_edit.gif', IMAGE_EDIT) . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $category_path_string . '&cID=' . $cInfo->categories_id . '&action=delete_category') . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $category_path_string . '&cID=' . $cInfo->categories_id . '&action=move_category') . '">' . tep_image_button('button_move.gif', IMAGE_MOVE) . '</a>');

$contents[] = array('text' => '<br>' . TEXT_DATE_ADDED . ' ' . tep_date_short($cInfo->date_added));

if (tep_not_null($cInfo->last_modified)) $contents[] = array('text' => TEXT_LAST_MODIFIED . ' ' . tep_date_short($cInfo->last_modified));

$contents[] = array('text' => '<br>' . tep_info_image($cInfo->categories_image, $cInfo->categories_name, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT) . '<br>' . $cInfo->categories_image);

$contents[] = array('text' => '<br>' . TEXT_SUBCATEGORIES . ' ' . $cInfo->childs_count . '<br>' . TEXT_PRODUCTS . ' ' . $cInfo->products_count);

} elseif (isset($pInfo) && is_object($pInfo)) { // product info box contents

$heading[] = array('text' => '<b>' . tep_get_products_name($pInfo->products_id, $languages_id) . '</b>');

 

$contents[] = array('align' => 'center', 'text' => '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id . '&action=new_product') . '">' . tep_image_button('button_edit.gif', IMAGE_EDIT) . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id . '&action=delete_product') . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id . '&action=move_product') . '">' . tep_image_button('button_move.gif', IMAGE_MOVE) . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $pInfo->products_id . '&action=copy_to') . '">' . tep_image_button('button_copy_to.gif', IMAGE_COPY_TO) . '</a>');

$contents[] = array('text' => '<br>' . TEXT_DATE_ADDED . ' ' . tep_date_short($pInfo->products_date_added));

if (tep_not_null($pInfo->products_last_modified)) $contents[] = array('text' => TEXT_LAST_MODIFIED . ' ' . tep_date_short($pInfo->products_last_modified));

if (date('Y-m-d') < $pInfo->products_date_available) $contents[] = array('text' => TEXT_DATE_AVAILABLE . ' ' . tep_date_short($pInfo->products_date_available));

$contents[] = array('text' => '<br>' . tep_info_image($pInfo->products_image, $pInfo->products_name, SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '<br>' . $pInfo->products_image);

$contents[] = array('text' => '<br>' . TEXT_PRODUCTS_PRICE_INFO . ' ' . $currencies->format($pInfo->products_price) . '<br>' . TEXT_PRODUCTS_QUANTITY_INFO . ' ' . $pInfo->products_quantity);

$contents[] = array('text' => '<br>' . TEXT_PRODUCTS_AVERAGE_RATING . ' ' . number_format($pInfo->average_rating, 2) . '%');

}

} else { // create category/product info

$heading[] = array('text' => '<b>' . EMPTY_CATEGORY . '</b>');

 

$contents[] = array('text' => TEXT_NO_CHILD_CATEGORIES_OR_PRODUCTS);

}

break;

}

 

if ( (tep_not_null($heading)) && (tep_not_null($contents)) ) {

echo ' <td width="25%" valign="top">' . "\n";

 

$box = new box;

echo $box->infoBox($heading, $contents);

 

echo ' </td>' . "\n";

}

?>