CODEWITHSUNDEEP
phpmysqlsql
Lei Lionel
Lei Lionel

Reputation: 1273

Display the article in the database base on categories

I am having a database with a table to stock the products of my site and the table has the following column for all the categories a specific product belongs to: Column of the table for products

After making a query to get the categories the first row belongs to, The information is stored in an object which looks like this $productDetails->ProductCategoryID and the content is the following: dinner,casual,kids

Now,my question is that how can I use the SQL command SELECT to get all the products having at least one category in common by using PHP.

Kindly help me solve this problem. Sorry I am not a native english speaker

Upvotes: 0

Views: 39

Answers (1)

Ma3x
Ma3x

Reputation: 96

If I've understood, you have a column which contains a string representing product's categories separated by comma. In this case you have to execute a substring function on the column ProductCategoryID, which is always discouraged. I suggest you, instead of using the column ProductTable.ProductCategoryID, to make a link table ProductsCategories with the columns ProductID, CategoryID.

----------------------
| ProductsCategories |
----------------------
| ProductID          |
| CategoryID         |
----------------------

In this way you can use a more efficient QUERY like this one:

SELECT DISTINCT ProductTable.*
FROM ProductTable
    INNER JOIN ProductsCategories ON (ProductsCategories.ProductID = ProductTable.ProductID)
WHERE CategoryID IN
(
    SELECT CategoryID
    FROM ProductsCategories
    WHERE ProductID != ProductTable.ProductID
)
;

All this, of course, has sense if I've understood in the right way the structure of ProductCategoryID column :)

Upvotes: 1

Related Questions