Greens44 Posted March 4, 2009 Share Posted March 4, 2009 I tried to ask this question in the contributions main thread however after three posts all I get is the cold shoulder. No support at all for me. Here is the quest.. lets see how many people can fix this simple code. Here is my question: Well after some coding I've came up with the following code to the manufacturer to product url that will add the category name to the url. So far this is what I have: the SQL: $sql = "SELECT p.products_id as id, pd.products_name as name, c.categories_id as id, cd.categories_name as cname FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd, ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS_TO_CATEGORIES." ptc WHERE p.products_id=ptc.products_id and c.categories_id=ptc.categories_id and c.categories_id = cd.categories_id and p.products_status='1'"; and the below portion: while ($product = $this->DB->FetchArray($product_query)) { $define = 'define(\'PRODUCT_NAME_' . $product['id'] . '\', \'' . $this->strip($product['cname']) . '/' . $this->strip($product['name']) . '\');'; $prod_cache .= $define . "\n"; eval("$define"); The only problem with the code above is it pulls the category as the product id. So in otherwords.. if product 3 is in category 1 it will still display category 3 for product 3. Im not an SQL programmer but I know thats where we need to look at. Maybe do something with the PRODUCTS_TO_CATEGORIES table. If anyone can fix my code above to display the proper category I will release alot of my mods to oscommerce including a completely converted version from tables to a div/css version. Thanks! Quote Link to comment Share on other sites More sharing options...
Greens44 Posted March 6, 2009 Author Share Posted March 6, 2009 I tried to ask this question in the contributions main thread however after three posts all I get is the cold shoulder. No support at all for me. Here is the quest.. lets see how many people can fix this simple code. Here is my question: Well after some coding I've came up with the following code to the manufacturer to product url that will add the category name to the url. So far this is what I have: the SQL: $sql = "SELECT p.products_id as id, pd.products_name as name, c.categories_id as id, cd.categories_name as cname FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd, ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS_TO_CATEGORIES." ptc WHERE p.products_id=ptc.products_id and c.categories_id=ptc.categories_id and c.categories_id = cd.categories_id and p.products_status='1'"; and the below portion: while ($product = $this->DB->FetchArray($product_query)) { $define = 'define(\'PRODUCT_NAME_' . $product['id'] . '\', \'' . $this->strip($product['cname']) . '/' . $this->strip($product['name']) . '\');'; $prod_cache .= $define . "\n"; eval("$define"); The only problem with the code above is it pulls the category as the product id. So in otherwords.. if product 3 is in category 1 it will still display category 3 for product 3. Im not an SQL programmer but I know thats where we need to look at. Maybe do something with the PRODUCTS_TO_CATEGORIES table. If anyone can fix my code above to display the proper category I will release alot of my mods to oscommerce including a completely converted version from tables to a div/css version. Thanks! Well after some elaborate testing I've got the following sql to pull the data as intended.. however the only question now is how to make it pull each product as the following sql you need to set this product_id: $proid = 2; $sql = tep_db_query("SELECT p.products_id as id, pd.products_name as name, cd.categories_name as cname FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd, ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS_TO_CATEGORIES." ptc WHERE p.products_id='" . $proid . "' and pd.products_id='" . $proid . "' and ptc.products_id = '" . $proid . "' and c.categories_id=ptc.categories_id and c.categories_id = cd.categories_id and p.products_status='1'"); If anyone can fix this code please help me. Once all the bugs are worked out we can make a full version of this and add it to new releases of SEO URL's. Thanks again! Quote Link to comment Share on other sites More sharing options...
Greens44 Posted March 8, 2009 Author Share Posted March 8, 2009 bump!! Quote Link to comment Share on other sites More sharing options...
chooch Posted March 9, 2009 Share Posted March 9, 2009 SEO URL support thread Quote Upon receiving fixes and advice, too many people don't bother to post updates informing the forum of how it went. Until of course they need help again on other issues and they come running back! Why receive the information you require in good faith for free, only to then have the attitude to ignore the people who gave it to you? There's no harm in saying, 'Thanks, it worked'. On the contrary, it creates a better atmosphere. CHOOCH Link to comment Share on other sites More sharing options...
Greens44 Posted March 10, 2009 Author Share Posted March 10, 2009 SEO URL support thread Already posted in that thread three times with no reply or help whatsoever so I felt the need to make this a separate thread. Besides there is a separate thread for the manufacturers contribution but I can't find it at the moment. All I need is some simple sql fix. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.