i have in database tables like this
products
similar_products_product
similar_products_view
in Products
keep record of main product
in similar_products_product
keep record of custom products that you can assignment into main product later one
in similar_products_view
keep record of assignment main product and their similar_products_product
Query to insert into similar_products_view
foreach ($data['similar_products'] as $cat_id => $key) {
foreach ($key as $similar_product_id) {
$this->db->query("INSERT INTO " . DB_PREFIX . "similar_products_view SET product_id = '" . (int)$product_id . "', similar_product_id = '" . (int)$similar_product_id . "', category_id = '" . (int)$cat_id . "'");
}
}
than i end up with records like this
id product_id similar_product_id category_id
1 10 2 7
2 10 4 7
3 10 5 7
4 10 10 7
5 10 12 7
6 10 15 6
7 10 24 6
8 10 30 6
what will be the better way to record this results ? is it something like to be record whole thing for same product_id at one line ?
id product_id similar_product_id category_id
1 10 2,4,5,10,12,15 7,6
but should know with similar_product_id match category_id the record also should be able for DELETE,UPDATE,SELECT by similar_product_id and category_id