Using PDO/MySQL to only show items with an ID that is not is a different table with the same ID

Question

I am trying to get a list to only display user missions that are not already done, I have a table that is setup to collect completed user missions. However I can only get it to not display only either the first one or the last one in the results for selecting the Missions ID from the User Missions Completed table.

I have tried using sub queries to no avail, have tried using different reductions and loops to collect the ID's into an array

$db->query("SELECT mID FROM user_missions_complete WHERE userid = ?");
$db->execute([$user_class->id]);
$get = $db->fetch();
foreach($get as $g) {
    $mID = $g['mID'];
}
$db->query("SELECT * FROM `daily_missions` WHERE ID != ? ORDER BY `id` ASC");
$db->execute([$mID]);
$m = $db->fetch();

foreach($m as $mis) {
    echo "
        <tr>
            "; if($mis['task_one'] != 'N/A') { echo "
            "; } if($mis['task_two'] != 'N/A') { echo "
            "; } if($mis['task_three'] != 'N/A') { echo "
            "; } if($mis['task_four'] != 'N/A') { echo "
            "; } if($mis['task_five'] != 'N/A') { echo "
            "; } if($mis['task_six'] != 'N/A') { echo "
            "; } if($mis['task_seven'] != 'N/A') { echo "
            "; } if($mis['task_eight'] != 'N/A') { echo "
            "; } echo "
        </tr>";
}

What I am trying to accomplish is for the list of missions from daily_missions to only show on the page results/missions that are not already completed and inside the user_missions_complete table. However, it is only either not including the first result or the last result in the query results.

by the way, shouldn't you use join statements instead, i think you meant to use `->prepare()` there instead — Kevin, May 06 '19 at 02:51
Can you please add sample data and what should be returned? This seems more like a `mysql` question. — user3783243, May 06 '19 at 03:53
Have a look through https://stackoverflow.com/questions/14017369/where-value-is-not-in-subquery as you can do this in 1 SQL statement and that will make it easier to work with. — Nigel Ren, May 06 '19 at 05:55

score 0 · Answer 1 · answered May 15 '19 at 12:05

With this :

foreach($get as $g) {
    $mID = $g['mID'];
}

you aren't doing the execute every time so you are only saving the last id which means that :

WHERE ID != ?

is only using the last value (whichever way round you order your results).

Also you are using $db->fetch(); which only returns a single row. You want to use $db->fetchAll();

I think you should be able to do this in a single query as has been suggested. You should make use of INNER JOIN - without knowing your table structure I would hazard a guess at :

SELECT * FROM daily_missions dm
INNER JOIN user_missions_complete umc ON (dm.id = umc.mID)
WHERE dm.userid = ?

So you final code would look something similar to:

$db->query("SELECT * FROM daily_missions dm
            INNER JOIN user_missions_complete umc ON (dm.id = umc.mID)
            WHERE dm.userid = ?");
$db->execute([$user_class->id]);
$m = $db->fetchAll();

then you can loop through the whole list of results.

Naturally you'll need to check your table relationships and column names but this should but you on a better track.

Also if you do need to filter out mysql results based on multiple values you should look into using the WHERE IN( and/or HAVING clauses

Using PDO/MySQL to only show items with an ID that is not is a different table with the same ID

1 Answers1