Error404 Error404 - 6 months ago 10
SQL Question

How to get all the values of a key searching with the value of another key in an array in PHP?

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 | car4


And another table with the customers that have bought each car:

Table sales

idCar | idCustomer
-------+------------
2 | cust1
2 | cust5
3 | cust5
3 | cust2


and 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!

Answer

Use INNER JOIN

SELECT c.* 
FROM `cars`  c
INNER JOIN sales s
ON s.idCar = c.id
AND s.idCustomer = 'cust5'