MYSQL fetch records from table 1 that do not exist in table 2

Question

I created a php function to fetch records from a sql table subscriptions, and I want to add a condition to mysql_query to ignore the records in table subscriptions that exists in table removed_items, here is my code;

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 ;
}

My solution is to create another function to fetch the records from table removed_items and set a php condition where I call subscriptions_func() to skip/unset the records that resemble the records in subscriptions_func(), as the following

$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’];
    }
}

This solution succeeded to skip the items in the table removed_items however this solution makes gaps in the array stored in the variable $sub which makes plank spots in the echoed items.

Is there a condition I can add to the function subscriptions_func() to cut all the additional conditions and checks?

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

Assuming id is the primary key of subscriptions and subs_id is the foreign key in removed_items, then you just have to add a condition to the WHERE clause. Something like this should work :

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

Not related to your problem :

Your code seems vulnerable to SQL injection : use prepared statement to prevent this.
The original Mysql API is deprecated, it is highly recommended to switch to Mysqli instead.

edited May 23 '17 at 12:05

Community

1
1

answered Oct 11 '14 at 18:16

ForguesR

3,558
1
17
39

This works like charm, thank you, i will consider your additional recommendations. – benjamin Oct 11 '14 at 18:24

MYSQL fetch records from table 1 that do not exist in table 2

1 Answers1