Joining db tables

[project3]

Ok i was wondering if this is possible.

 

I want to use maybe join to get the following to work.

 

I have a table that has product info being displayed. One of the fields in that table is categories.

Now I have another table that holds all the categories and sort order. I want to sort all the products

in product table by the sort order set in the categories table.

 

Is this possible?

[Psycho]

Hmmm, when you say "One of the fields in that table is categories" does that mean there are multiple categories listed for a product? If so, you will want an associative table. if there is only one category per product it is very simple:

 

SELECT *

FROM products

JOIN categories ON products.category_id = categories.id

ORDER BY category.sort_order

[project3]

Hmmm, when you say "One of the fields in that table is categories" does that mean there are multiple categories listed for a product? If so, you will want an associative table. if there is only one category per product it is very simple:

 

SELECT *

FROM products

JOIN categories ON products.category_id = categories.id

ORDER BY category.sort_order

 

There is only one category for each product so I will give that a try

[project3]

Hmmm, when you say "One of the fields in that table is categories" does that mean there are multiple categories listed for a product? If so, you will want an associative table. if there is only one category per product it is very simple:

 

SELECT *

FROM products

JOIN categories ON products.category_id = categories.id

ORDER BY category.sort_order

 

that didn't work so Ill explain in more detail.

 

db table: ds_products

the field name for category in this table is foodcat

 

the food category db is:

db table name: ds_foodcats

 

its setup like this

id          name    active        sort_order

1          Salads    yes          0

2          Soups    yes          1

 

 

now what I want is the products to be sorted by the sort_order in ds_foodcats

the ds_products table has the foodcat field that would hold the value Salad so its the name field.

 

heres the current sql statement.

I don't know if its the other joins that are keeping it from working

 

$sql = ' select pPrice as regularPrice ' .

              '        ,p.pID as productId ' .

              '        ,p.pID as id ' .

              '        ,venID as vendorId ' .

              '        ,pNum as productNumber ' .

              '        ,taxable ' .

              '        ,pWeight as weight ' .

 

              '        ,pBoxWidth as boxWidth ' .

              '        ,pBoxHeight as boxHeight ' .

              '        ,pBoxLength as boxLength ' .

 

              '        ,pSalesPrice as salesPrice ' .

              '        ,pSale as sale ' .

              '        ,lp.pName as name ' .

              '        ,lp.pDesc as description ' .

              '        ,lp.pTeaser as teaser ' .

              '        ,lp.pCallEmailDisplay as callEmailDisplay ' .

              '        ,invLevel as inventoryLevel ' .

              '        ,invTrack as trackInventory ' .

 

              '        ,pUDFName1 as userDefinedName1 ' .

              '        ,pUDFName2 as userDefinedName2 ' .

              '        ,pUDFName3 as userDefinedName3 ' .

              '        ,pUDFName4 as userDefinedName4 ' .

              '        ,pUDFName5 as userDefinedName5 ' .

 

              '        ,pUDFValue1 as userDefinedValue1 ' .

              '        ,pUDFValue2 as userDefinedValue2 ' .

              '        ,pUDFValue3 as userDefinedValue3 ' .

              '        ,pUDFValue4 as userDefinedValue4 ' .

              '        ,pUDFValue5 as userDefinedValue5 ' .

              '        ,pAvail as available ' .

              '        ,p.inventory_notified as currentNotificationStatus ' .

              '        ,notifyLevel ' .

              '        ,pFreeShip as freeShip ' .

              '        ,if(pFlag = ' . PGIFT_CERT_FLAG . ',"' . TRUE_VALUE . '","' . FALSE_VALUE . '") giftCertificate ' .

 

              '        ,extraField ' .

              '        ,extraFieldName ' .

              '        ,extraFieldRequired ' .

              '        ,if(extraFieldPrice="0.00", null, extraFieldPrice) extraFieldPrice ' .

 

              '        ,domMethod1 as domestic1 ' .

              '        ,domMethod2 as domestic2 ' .

              '        ,domMethod3 as domestic3 ' .

              '        ,domMethod4 as domestic4 ' .

              '        ,intMethod1 as international1 ' .

              '        ,intMethod2 as international2 ' .

 

              '        ,pRelate1 as relatedProduct' .

              '        ,pRelate2 as relatedProduct2' .

 

              '        ,meta_title as metaTitle' .

              '        ,meta_description as metaDescription ' .

              '        ,meta_keywords as metaKeywords ' .

 

              '        ,pSize as size' .

              '        ,pCost as cost' .

              '        ,pFlag as flag ' .

              '        ,pProductType as productType ' .

              '        ,pFirstClassMailType as firstClassMailType ' .

              '        ,pContainerType as containerType ' .

              '        ,pBoxGirth as boxGirth ' .

              '        ,pRewardsEligible as rewardsEligible ' .

              '        ,pShipExempt as shipExempt ' .

              '        ,pGiftWrapAvail as giftWrapAvailable ' .

              '        ,pFileUploadEligible as fileUploadEligible ' .

              '        ,pCheckoutId as checkoutId ' .

 

              '  from ds_products p ' .

              '  left join ds_language_products lp on ' .

              '    lp.pID = p.pID ' .

              $ex .

              '  where ' .

              ' 1 and ' .

              $where .

              '    lp.langID = ? ' .

              '  group by p.pID ' .

              '';

 

 

[Psycho]

now what I want is the products to be sorted by the sort_order in ds_foodcats

the ds_products table has the foodcat field that would hold the value Salad so its the name field.

 

Then you are not using the database how it should be. you already have the name of the category in the category table, so the product table should just hold the ID of the category - not the value. That is the whole point of using a relational database, you don't have to replicate data you can relate it to another table. If you decided you needed to rename a category you would just need to update a single record instead of possibly many, many records in the product table.

 

I would highly suggest changing the category column in the product table to the category ID.

 

However, assuming you don't, this shoudl work (although I supply this against my better judgement):

 

SELECT *
FROM ds_products
JOIN ds_foodcats ON ds_products.foodcat = ds_foodcats.name
ORDER BY ds_foodcats.sort_order

 

Also, why are you renaming all of those fields? That's a lot of unnecessary code (unnecessary code = error prone code). Just use 'pWeight' in the result instead of 'weight'. Or if you really want to use 'weight' then change the field name to that in the table.