比较2表之间的mysql id并显示所有匹配id echo yes

I want to compare mysql id between 2 table and display all name with match id echo yes.

I have 2 table one is wine and the other is user. I will use unique id column from table wine. But in table user, I will use wine_id column which are not unique.

This is mysql code that will get a matching id from table wine and user.

$sql="SELECT id FROM `winelist` w WHERE page = 'Chardonnay USA' and
EXISTS(
SELECT wine_id FROM `user_wine_history` u WHERE user_name = 'bon'
AND u.wine_id = w.id);";
$result= mysql_query($sql);

while($data= mysql_fetch_array($result))  {
}

and this is the code for for listing all the name in wine.

$sql2 ="SELECT id, name, year, grape, price, instock FROM winelist WHERE page ='Chardonnay USA';";
$result2 = mysql_query($sql2);

while($data2 = mysql_fetch_array($result2)) {
}

I tried to use this code in listing name while loop but it didn't work.

if($data2['id'] == $data['id']) {
echo "yes";
}
else
{ 
echo "fail";
}

Can anybody give me some advice how to solve this? Thank you in advance.

You can use LEFT JOIN instead and do this within the query using the CASE expression, something like:

SELECT 
  w.id , w.page, w.name, w.year, 
  u.user_name, u.id user_id, u.wine_id uwine_id,
  CASE 
    WHEN u.id IS NOT NULL THEN 'Yes' 
    ELSE 'No' 
  END AS IsMatching
FROM `winelist` w 
LEFT JOIN `user_wine_history` u  ON w.id        = u.wine_id 
                                AND w.page      = 'Chardonnay USA' 
                                AND u.user_name = 'bon';

This will select a new column IsMatching with values yes or no to indicate whether the wine id is found in the other table or not.

• SQL Fiddle Demo

Use Join:

$id= Here the input

SELECT id FROM `winelist` w  
INNER JOIN `user_wine_history` u 
ON u.wine_id = w.id
WHERE w.page = 'Chardonnay USA' AND u.user_name = 'bon'
AND w.id = '".$id."'