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

Question

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.

Answer 1

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