Php mysqli select data there is in table and not in different table

Question

This is my "all_workers" table and "absent_workers" table

worker_id  worker_name     |   id       name      
                           |
    1        frank         |    1       neil
    2        tom           |    2       ryan
    3        neil          |    3       david
    4        steve         |    4       steve
    5        ryan          |    .       .
    6        david         |    .       .
    7        .             |

I'm gonna select working workers now from all_workers table, but my codes not work. This is my codes:

<?php
include 'config.php';
$stmt = $conn->prepare('SELECT * FROM absent_workers');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array()){

$absent_name = $row['name'];

}
$stmt->close();
?>

<?php
include 'config.php';
$stmt = $conn->prepare('SELECT * FROM all_workers WHERE worker_name NOT IN ?');
$stmt->bind_param('s', $absent_name); 

$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array()){

$worker_name = $row['worker_name'];

}
$stmt->close();

?>

`my codes not work` is not enough for us to help you. Please describe what doesn't work, query fails, you get an error message, you get the wrong results? You `in` usage will only get you the last result from the query. You should use a subquery. Your db structure also will encounter collisions if you have multiple workers with the same name. You should use ids. — user3783243, Apr 12 '20 at 21:55

score 0 · Answer 1 · answered Apr 12 '20 at 21:55

You seem to want names from all_workers that do not exist in absent_workers. You can do this with a single query rather than looping in application code.

One option is not exists:

select al.*
from all_workers al
where not exists (select 1 from absent_workers ab where ab.name = al.name)

You can also use and anti-left join:

select al.*
from all_workers al
left join absent_workers ab where ab.name = al.name
where ab.name is null

Php mysqli select data there is in table and not in different table

1 Answers1