0

I have three tables called product, product_category and category with product_category being the linking table.

How can I join these tables using SQL in PHP?

The linking table has only productID linking to the product table and catID linking to the category.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Chris
  • 11
  • 1
  • 1

2 Answers2

4

Something like this?

SELECT
  *
FROM
  product
INNER JOIN
  product_category
    ON product_category.productID = product.productID
INNER JOIN
  category
    ON category.catID = product_category.catID
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Ok but how can I use this by taking the product and category from a PHP variable. – Chris Mar 18 '11 at 17:38
  • First you need to look up how to make Prepared Statements in PHP. These let you supply parameters to your SQL code in a Safe way that can't be abused (lookup SQL Injection attacks). Then you need to add a WHERE clause, such as : * WHERE product.productID = :productID * – MatBailie Mar 18 '11 at 17:41
2

Your query should look something like this: Added the requirement that there should be a productId and categoryId from a variable:

$query = "SELECT * FROM
          product p
            JOIN product_category pc ON p.id = pc.productId
            JOIN category c ON c.id = pc. categoryId
          WHERE p.id = {$productId}
            AND c.id = {$categoryId}";
Nanne
  • 64,065
  • 16
  • 119
  • 163
  • Added the requirement about PHP variables @Dems posted as a comment to other answer – Nanne Mar 18 '11 at 17:41
  • This is an example that is open to SQL Injection Attacks. Please read about them before using this approach. I highly recommend that you Never directly build SQL queries for execution in this way if Any of the parameters are provided by a user. – MatBailie Mar 18 '11 at 17:45
  • That's way to much said for a little blurb like this, please don't add kneejerk reactions like this. If you check your vars for being numeric beforehand (why would you ever want to do that inside a query?) or do some other form of sanitize, you aer NOT open to SQL Injections attacks. – Nanne Mar 18 '11 at 19:03
  • I highly disagree, there are constructors for paramaterised / prepared queries. Not using them is pointless and reckless. Good techniques should be good habits. Half the time it's jus a copy and paste from where you last used it. I have never been given a justification I'd accept for this approach. – MatBailie Mar 18 '11 at 22:05
  • It is a fact that prepared queries do more than just sanitize your input. If you do not want any of the other effects (such as, you don't have PDO available). While they are a good technique, they are not the only technique. Also, it is not ontopic here, as we are talking mainly SQL and how to join tables. If it is not user-input, then way take the long way around. It is become some sort of standard sentence to type, even if the subject is something else. You shoulnd't copy paste half your code anyway.... – Nanne Mar 18 '11 at 22:09
  • @dems I did? Anyway, your answer does not answer the question. Coincidently on the same point you criticize mine... – Nanne Mar 18 '11 at 22:10