CodeIgniter或Simple PHP，如何将表与另一个表的两列连接？

Question

I have a form with two select inputs. In each input there are products as options. Each visitor must select two products as their favorites. Their choice goes into a table named: visitors_fav

The structure of table is:

visitors_fav:
vis_ID    -   productID_1   -    productID_2

As you see i fetch the product IDs not their names and store them into the table.

I have another table named: Products, the structure is like:

Product_ID   -    Product_Name

How can I get the Product_Name for both productID_1 & productID_2 in a query?

(PHP Codeigniter active record is preferred, and also my database is mySQL)

Answer 1

you must join visitors_fav with Products two times to get the results you want. try the following query:

select vis_ID, prod1.Product_Name as Product_Name1, prod2.Product_Name as Product_Name2
from visitors_fav vis join Products as prod1 on vis.productID_1 = prod1.Product_ID 
                      join Products as prod2 on vis.productID_1 = prod2.Product_ID