SQL query for selecting a category?

[Guest]

Once again, I'm in need of SQL query's help.

I'm studying PHP, but haven't got the SQL part yet... :'(

 

So what I need is this: I have the product ID number, and now need to determine which category it is in, so I can put a link alongside the product name for displaying all products that are in the same category as that particular product.

 

What would be the query for that?

 

Many thanks in advance.

[jdvb]

products can be in multiple categories, so you will get an array as a result:

$querycategories = tep_db_query("SELECT categories_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE products_id = '" . (int)$products_id . "'");
$while ($querycategoriesfetch = tep_db_fetch_array($querycategories) {
$thisproductscategories[] = Array('cat_id' => $querycategoriesfetch['categories_id ']);
}

for($k = 0, $l=sizeof($thisproductscategories); $k<$l; $k++){
echo "<a href=" . tep_href_link(FILENAME_DEFAULT . "?cPath=" . $thisproductscategories[$k]['cat_id']) . ">YOUR_TEXT_HERE</a><br>";
}

did not check if this works.

do mind the trailing <br> if that is not pressent or next row in a table or something alike, and a product apears in multiple categories, you will get them next to each other, instead of below one another.

 

do note that this is still sloppy coding since I did not get the parent categories_id. So your categries_box will not show you in what categorie you are when the link refers to a subcategorie.

this is just the most basic coding to get this working, hope that you can understand what I wrote here.

[Guest]

OK, thanks!! I will give that a try and let you know how it worked.

 

I've moded an existing random products infobox contribution to a layout according to my store's general layout, and I would like to display at the side of the product name (in that infobox), a link "similar products".

So, upon clicking this link, the visitor would be redirected to the category listing which that product is in.

All I need for that, it seems, is a way of fetching the category ID, so I can feed it into the link creation function. :)

[Guest]

OK, after correcting some typos in the code, it worked! :)

 

Here's how it's looking now

 

$querycategories = tep_db_query("SELECT categories_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE products_id = '" . (int)$new_products['products_id'] . "'");
while ($querycategoriesfetch = tep_db_fetch_array($querycategories)) 
{
	$thisproductscategories[] = Array('cat_id' => $querycategoriesfetch['categories_id']);
}

for($k = 0, $l=sizeof($thisproductscategories); $k<$l; $k++)
{
	$similar_string = "<a href=" . tep_href_link(FILENAME_DEFAULT . "?cPath=" . $thisproductscategories[$k]['cat_id']) . ">TEST SIMILAR LINK</a><br>";
}

 

The only problem now is that it displays the link directly to the category ID, even if it's a subcategory. It works OK, the person is redirected to the category fine. But it somewhat breaks the nav-bar trail...

A link looks like this "...cPath=64", when it should read "....cPath=21_64"... :blush:

 

I think there's a way around this (a function somewhere in osC code). I'll try to look for it, but if anyone has a suggestion in the meantime, feel free to post!! :P

[boxtel]

OK, after correcting some typos in the code, it worked! :)

 

Here's how it's looking now

 

$querycategories = tep_db_query("SELECT categories_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE products_id = '" . (int)$new_products['products_id'] . "'");
while ($querycategoriesfetch = tep_db_fetch_array($querycategories)) 
{
	$thisproductscategories[] = Array('cat_id' => $querycategoriesfetch['categories_id']);
}

for($k = 0, $l=sizeof($thisproductscategories); $k<$l; $k++)
{
	$similar_string = "<a href=" . tep_href_link(FILENAME_DEFAULT . "?cPath=" . $thisproductscategories[$k]['cat_id']) . ">TEST SIMILAR LINK</a><br>";
}

 

The only problem now is that it displays the link directly to the category ID, even if it's a subcategory. It works OK, the person is redirected to the category fine. But it somewhat breaks the nav-bar trail...

A link looks like this "...cPath=64", when it should read "....cPath=21_64"... :blush:

 

I think there's a way around this (a function somewhere in osC code). I'll try to look for it, but if anyone has a suggestion in the meantime, feel free to post!! :P

 

 

you can simply use :

 

$category_path = tep_get_product_path($new_products['products_id']);

 

instead of all this stuff.

[Guest]

you can simply use :

 

$category_path = tep_get_product_path($new_products['products_id']);

 

instead of all this stuff.

 

Ahh, the keep things simple strategy!! :D :thumbsup:

 

I tried your suggestion and it worked great! :lol:

 

Thank you very much for your suggestion. Once again you've been of great help!

 

 

By the way, here's the code for it now, much smaller!

 

$category_path = tep_get_product_path($new_products['products_id']);
$similar_string = "<a href=" . tep_href_link(FILENAME_DEFAULT . "?cPath=" . $category_path) . ">TEST SIMILAR LINK</a><br>";

 

The $similar_string variable is then echoed in the appropriate place inside the infoBox text.

 

With time I think I'll add this to the contributions section. I think it's a very useful thing.

[jdvb]

much more simple indeed, yet far less to learn from SQL query's.

do wonder how the function handles products in more that one categorie, I will look that up some time.

[boxtel]

much more simple indeed, yet far less to learn from SQL query's.

do wonder how the function handles products in more that one categorie, I will look that up some time.

 

it is the very same function which is used to determine $cPath in application top, re-use.

 

Anybody who duplicates products via links to multiple categories is asking for trouble and should review their category organization policy.

[Guest]

much more simple indeed, yet far less to learn from SQL query's.

do wonder how the function handles products in more that one categorie, I will look that up some time.

 

Yep, I learned a little more about SQL using your suggestion. So even though it wasn't the best solution for this specific problem, it contributed to increase a little bit my knowledge of SQL. :D

I've already bought a book about it, just didn't have the time to get there yet (I'm still studying the PHP part). Sure enough, once I get to the SQL part, it will be easier to understand because there will already be some prior knowledge! :thumbsup:

 

it is the very same function which is used to determine $cPath in application top, re-use.

 

Anybody who duplicates products via links to multiple categories is asking for trouble and should review their category organization policy.

 

Yep, I agree with you, it's highly recommendable to keep things robust, to the most possible. :)

[jdvb]

I see it will then pick the categorie it is entered in first.

I do use duplicating products by linking them, think it works just fine.

[boxtel]

I see it will then pick the categorie it is entered in first.

I do use duplicating products by linking them, think it works just fine.

 

think again.

[NinjaNoodles]

I've been working on this for a while, and this thread REALLY helps.

 

I know it's old, but I'll bump it just in case anyone else needs to know how to do this.

 

Additionally, if you're using the Random New Products modification, change the code to the following:

 

$category_path = tep_get_product_path($random_product['products_id']);

[NinjaNoodles]

I've created a simple contribution based on this code.