mysql选择查询以预填充下拉字段

Question

As part of my ecommerce application, I have this interesting problem, I've been trying to resolve.

I have two things.

• Category
• Product

In relational database sense, a category can have more than one products, and a product can only belong to category.

Then, I have this ecommerce admin coupon page where I want to modify the coupon details that's associated with a particular product and category.

On the modify page, I have the following fields

Coupon description - TextField type Coupon Price - TextField type Coupon Percentage - TextField type Category Name - Dropdownfield type Product Name - Dropdownfield type

I have the following sql query.

$sql = "SELECT cp_description, cp_discountprice, cp_discountpercent, pd_name, cat_name
        FROM tbl_coupon inner join (tbl_product, tbl_category) on 
        (tbl_product.pd_id = tbl_coupon.pd_id AND     tbl_category.cat_id=tbl_product.cat_id)
        WHERE cp_id = $cpId";

What's really interesting about this problem is that I could not include pd_id and cat_id fields into the sql query so I can manipulate them in another sql code that will look up these fields to retrieve correct selected element in a dropdown field when prepopulated.

I cannot use pd_name or cat_name in select statement because these fields are not 'unique' so can cause problems.

Does anybody have any idea what's the best way to approach this problem? I thought the distinct keyword may do the trick... But it doesn't!

Answer 1

Can't you use a query like this?

SELECT 
    cp.cp_description, cp.cp_discountprice, cp.cp_discountpercent, 
    p.pd_id, p.pd_name, 
    cat.cat_id, cat.cat_name
FROM tbl_coupon cp 
INNER JOIN tbl_product p ON p.pd_id = cp.pd_id
INNER JOIN tbl_category cat ON p.cat_id = cat.cat_id 
WHERE cp_id = $cpId