Help With mySQL Query From Multiple Tables (osCommerce)?

Question

I have the following tables in osCommerce (among a total of 49), I include only relevant fields:

products
------------
  products_id *
  products_quantity
  products_price

products_description
------------------------------
  products_id *
  products_name

categories
---------------
  categories_id *

categories_description
--------------------------------
  categories_id *
  categories_name

products_to_categories
----------------------------------
  products_id *
  categories_id

I want to produce results with the following columns:

categories_name
products_id
products_name
products_qty
products_price

Grouped by category and ordered within that category alphabetically by name.

If it makes it easier to understand, I have a store that sells Magic cards. Magic has several different sets (categories) and between those sets there are often repeated card names (products_name). For example, every third set has four different cards called "Plains"--they are unique, so they each have a different products_id.  osCommerce has a painful method of updating inventory that would result in months of work--I'd never get the online store open.  There is a patch to make it easier, but the patch doesn't take into consideration that a product name might be repeated.

I tried to edit the patch, but instead of coming up with 17000 results (I did not use a limit statement), I consistently come up with fewer than 100.  This occurs even if I write the SQL in phpMyAdmin, skipping osCommerce entirely.

I'm a total sql newbie, I can write simple queries on individual tables and write queries to modify the tables, but this is waaaay over my head.

Any help would be appreciated! :)

osCommerce University · Accepted Answer

If you are just wanting this query to work, you need to include the categories ID in your select, and relate it to the products via the products to categories table.  A JOIN may be helpful here.  

You might find related products or products cross sell contributions to be more helpful here.  Certainly there are several alternatives which seek to address product relationships that could help you with example queries.

If you need more help with this, post on the forums at the osCommerce university.

Seems an interesting issue.. :)

jnbill1204 · Answer

If I follow you correctly you want to take certain columns from these table but group them together as if they were one table. I hope I got that right!

I go kind of confused but I think that it should look something like this:

SELECT categories_name ,products_id, products_name ,products_qty, products_price, FROM products p, products_description pd, categories c, categories_description cd, products_to_categories ptc, WHERE p.products_id =pd.products_id and p.products_id = ptc.products_id.....etc

I don't feel like walking through all the logic.  I'll leave that to you but that is the general idea of it and that should work...it will just a take a bit to get the logic right

Trending News

Help With mySQL Query From Multiple Tables (osCommerce)?

2 Answers