MYSQL从表1中获取表2中不存在的记录

Question

我创建了一个php函数来从sql表中提取记录subscriptions，我想向mysql_query添加一个条件以忽略表中存在的表subscriptions中的记录removed_items，这是我的代码;

function subscriptions_func($user_id, $limit){ 

    $subs = array(); 

    $sub_query = mysql_query(" 
     SELECT `subscriptions`.`fo_id`, `subscriptions`.`for_id`, `picture`.`since`, `picture`.`user_id`, `picture`.`pic_id` 
     FROM `subscriptions` 
     LEFT JOIN `picture` 
     ON `subscriptions`.`fo_id` = `picture`.`user_id` 
     WHERE `subscriptions`.`for_id` = $user_id 
     AND `picture`.`since` > `subscriptions`.`timmp` 
     GROUP BY `subscriptions`.`fo_id` 
     ORDER BY MAX(`picture`.`since_id`) DESC 
     $limit 
     "); 
    while ($sub_row = mysql_fetch_assoc($sub_query)) { 
     $subs [] = array(
      'fo_id'    => $sub_row['fo_id'], 
      'for_id'   => $sub_row['for_id'], 
      'user_id'   => $sub_row['user_id'], 
      'pic_id'   => $sub_row['pic_id'], 
      'since'    => $sub_row['since'] 
     ); 
    } 
    return $subs ; 
} 

我的解决办法是创建另一个函数从表removed_items获取记录，并设置一个PHP条件其中I调用subscriptions_func（）跳过/复位类似于在subscriptions_func（记录中的记录），如下面的

$sub = subscriptions_func($user_id); 
foreach($sub as $sub){ 
    $rmv_sub = rmv_items_func($sub[‘pic_id’]); 

    If($rmv_sub[‘pic_id’] != $sub[‘pic_id’]){ 
     echo $sub[‘pic_id’]; 
    } 
} 

将该溶液成功跳过的项目表中的removed_items然而这种解决方案使得存储在变量$子，这使得在回荡项木板点阵列中的间隙。

是否有一个条件，我可以添加到函数subscriptions_func（）来削减所有额外的条件和检查？

Answer 1

假设id是subscriptions和subs_id主键是removed_items外键，那么你只需要一个条件添加到WHERE条款。像这样的东西应该工作：

... 
AND `subscriptions`.id NOT IN (SELECT `removed_items`.subs_id FROM `removed_items`) 
... 

不相关的问题：

• 你的代码似乎易受SQL injection：使用prepared statement防止这种情况。我们强烈建议您改用Mysqli。