Jump to content

Every category of my products by DB


ElSHake
 Share

Recommended Posts

Goodday,

I'm trying to extract the data about every  categories associate with a product but the only result that i have is extract the last category.
Es I need to estract "product-category1-category2-category3"

The best result that i have is "product- category3-category3-category3", 

HELP ME PLEASE!

Share this post


Link to post
Share on other sites

SELECT a.`id_product`, b.`name` AS `name`, `reference`, a.`price` AS `price`, sa.`active` AS `active` , shop.`name` AS `shopname`, a.`id_shop_default`, image_shop.`id_image` AS `id_image`, cl.`name` AS `name_category`, sa.`price`, 0 AS `price_final`, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` AS `sav_quantity`, sa.`active`, IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` FROM `ps_product` a LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 4 AND b.`id_shop` = 1) LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0 ) JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default) LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default) LEFT JOIN `ps_shop` shop ON (shop.id_shop = a.id_shop_default) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default) LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = a.`id_product` AND pd.`active` = 1) WHERE 1 ORDER BY a.`id_product` ASC

Share this post


Link to post
Share on other sites

Hi.

$id_product = 14;

$getCategories = Product::getProductCategories($id_product);

/* return id_categories */
$cats = array();
foreach ($getCategories as $category)
{
	$cats[] = $category['id_category'];
}

echo implode(' | ', $cats);

 

Or:

$id_product = 14;
$id_lang = $this->context->language->id;

$getCategories = Product::getProductCategoriesFull($id_product, $id_lang);

foreach ($getCategories as $category)
{
	$category_id = $category['id_category'];
	$category_name = $category['name'];
	$category_link_rewrite = $category['link_rewrite'];

	echo $category_id . ' - ' . $category_name . ' - ' . $category_link_rewrite .'<br />';

}

 

Share this post


Link to post
Share on other sites

 

Hi.

It is always better to use Prestashop functions, the output data can be formatted better.

GROUP_CONCAT and SEPARATOR can be used in SQL.

SELECT 
a.`id_product`, 
b.`name` AS `name`, 
a.`reference`, 
a.`price` AS `price`, 
sa.`active` AS `active`, 
shop.`name` AS `shopname`, 
a.`id_shop_default`, 
image_shop.`id_image` AS `id_image`, 
cl.`name` AS `name_category`, 
(SELECT GROUP_CONCAT(cp.`id_category` SEPARATOR ' - ') 
FROM `ps_category_product` cp
WHERE cp.id_product = a.`id_product`) AS `categories`,
sa.`price`, 
0 AS `price_final`, 
a.`is_virtual`, 
pd.`nb_downloadable`, 
sav.`quantity` AS `sav_quantity`, 
sa.`active`, 
IF(sav.`quantity`<=0, 1, 0) AS `badge_danger`
FROM `ps_product` a
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 4 AND b.`id_shop` = 1) 
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0 ) 
JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default) 
LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default) 
LEFT JOIN `ps_shop` shop ON (shop.id_shop = a.id_shop_default) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default) 
LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = a.`id_product` AND pd.`active` = 1) 
WHERE 1 
ORDER BY a.`id_product` ASC;

obrazek.thumb.png.2cf79a1e4ba356efe7dcf53ecb943d45.png

Edited by 4you.software (see edit history)

Share this post


Link to post
Share on other sites

8 minutes ago, musicmaster said:

You need to query the ps_category_product table to get the other categories besides the default.

And do you read the posts before you reply?
Got a simple sql query that returns category id !!!

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More