SQL Query to get full category path?

[simoncarr]

Hi I am writing a script outside of prestashop that will be run by cron job every couple of hours and needs to iterate through all the products on the site (23,000 of them). I am connecting directly to the MySql database rather than through prestashop.

 

one piece of information I need is the full Category Path a particular product. I.e.

 

Food->Dogs->Dry Adult Food

 

The id_category_default field in the product table will contain a reference to the category "Dry Adult Food". Each record in the category table has a reference to it's parent category. With my current knowledge of SQL, I would have to run 3 queries for each product to get the full path, which is too much for 27,000 products.

 

 

what is the most efficient single line SQL code to get the entire category path?

 

Thanks

Simon

[simoncarr]

I worked it out myself.

 

select name from category_lang join category on category_lang.id_category = category.id_category where nleft <= 252 and nright >= 379

 

It's called modified pre-order tree traversal

 

Thanks

Simon

[simoncarr]

Sorry forget to mention that each category has a field called nleft and nright. Anything = or less than nleft and = or greater than nright will be a child of that item. Just have a look in the category table and you will see what I mean.

[adhirath]

hi i am new in preshtashop i am trying to add product from other site into preshtashop database throught mysql query when i add product in other site product is sucessfully added and show into preshtashop but now i want to add new category with this new product

 

please help me how to intsert category with product throught mysql query