dongyilu3143 2013-12-29 11:26
浏览 56
已采纳

比较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.

  • 写回答

2条回答 默认 最新

  • dsadasd12132 2013-12-29 11:49
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题