我有一张桌子上有我所拥有的汽车类型:
Table cars idCar | name -------+------- 1 | car1 2 | car2 3 | car3 4 | car4另一张桌子上有购买每辆车的顾客:
Table sales idCar | idCustomer -------+------------ 2 | cust1 2 | cust5 3 | cust5 3 | cust2并且我将所有销售表的值存储在使用PHP的数组中。 它看起来像这样:
array(4) { [0]=> array(2) { ["idCar"]=> string(1) "2" ["idCustomer"]=> string(5) "cust1" } [1]=> array(2) { ["idCar"]=> string(1) "2" ["idCustomer"]=> string(5) "cust5" } .... //Here the rest of values }我想要做的是获得特定客户购买的汽车(或汽车)的所有价值,例如cust5 。
因此,如果我在销售数组中搜索已购买客户cust5的汽车,它应该返回给我, idCar的值为2和3 。 之后我将获得idCar的值和MySQL name 。
我试过使用以下sql语句:
"SELECT * FROM cars WHERE FIND_IN_SET(idCar, $salesTable)";其中$salesTable是包含完整销售表的数组,但当然它不起作用,因为我试图直接解析数组,而不是idCar的值。
我只想将与特定客户匹配的idCar的值传递给sql语句,在本例中为cust5 。
是否有一些方法只获得PHP的某些函数所需的idCar值,只给出cust5作为参数,所以我可以应用上面提到的sql语句?
提前致谢!
I have a table with the types of cars that I have on stock:
Table cars idCar | name -------+------- 1 | car1 2 | car2 3 | car3 4 | car4And another table with the customers that have bought each car:
Table sales idCar | idCustomer -------+------------ 2 | cust1 2 | cust5 3 | cust5 3 | cust2and I have all the values of sales table stored in an array with PHP. It looks like this:
array(4) { [0]=> array(2) { ["idCar"]=> string(1) "2" ["idCustomer"]=> string(5) "cust1" } [1]=> array(2) { ["idCar"]=> string(1) "2" ["idCustomer"]=> string(5) "cust5" } .... //Here the rest of values }What I am trying to do is to get all the values of a car (or cars) that have been bought by a particular customer, for example, cust5.
So, if I search on the sales array for the cars that have bought the customer cust5 it should return to me, idCar with the values 2 and 3. After I will get the values of idCar and name with MySQL with that values.
I have tried using the following sql statement:
"SELECT * FROM cars WHERE FIND_IN_SET(idCar, $salesTable)";where $salesTable is the array that contains the full sales table but of course it does not work because I am trying to parse there an array directly, instead of the values of idCar.
I would like only to pass to the sql statement the values of idCar that match with a particular customer, in this case, with cust5.
Is there some way to get only the values of idCar required with some function of PHP giving only cust5 as parameter so I would be able to apply the sql statement that I put above?
Thanks in advance!
最满意答案
使用INNER JOIN
SELECT c.* FROM `cars` c INNER JOIN sales s ON s.idCar = c.id AND s.idCustomer = 'cust5'Use INNER JOIN
SELECT c.* FROM `cars` c INNER JOIN sales s ON s.idCar = c.id AND s.idCustomer = 'cust5'更多推荐
发布评论