How to join two tables and show what is missing via database?-CodePudding

How to join two tables and check correctness with the third one and show what is missing via users list? I want output like:

User 105 not found!

I have:

All users list:

while($all= mysqli_fetch_array($idall))
{
   echo '<pre>';
   print_r($all);
   //print_r($all['id']);
   echo '</pre>';
}

Output:

Array
(
    [0] => 101
    [id] => 101
    [1] => test101
    [name] => test101
)
Array
(
    [0] => 102
    [id] => 102
    [1] => test102
    [name] => test102
)
Array
(
    [0] => 103
    [id] => 103
    [1] => test103
    [name] => test103
)
Array
(
    [0] => 104
    [id] => 104
    [1] => test104
    [name] => test104
)
Array
(
    [0] => 105
    [id] => 105
    [1] => test105
    [name] => test105
)

Male:

foreach($males as $male)
{
   echo '<pre>';
   print_r($male);
   //print_r($male['ids']);
   echo '</pre>';

}

Output:

Array
(
    [ids] => 103
    [name] => somemanname
)
Array
(
    [ids] => 104
    [name] => somemanname
)

Female:

foreach($females as $female)
{
   echo '<pre>';
   print_r($female);
   //print_r($male['ids']);
   echo '</pre>';

}

Output:

Array
(
    [ids] => 101
    [name] => somewomanname
)
Array
(
    [ids] => 102
    [name] => somewomanname

I tried to figure out something with array_merge and array_diff but I can't get it all together.

Thanks for help!

--- EDIT ---

while($all = mysqli_fetch_array($idall))
{
         //echo "<pre>";
         //print_r($all);
         //echo "</pre>";
}
         $people = array_merge($males,$females);
         //echo "<pre>";
         //print_r($people);
         //echo "</pre>";
         $resulty = array_diff($all,$people);
         echo "<pre>";
         print_r($resulty);
         echo "</pre>";
         foreach($resulty as $res)
         {
            echo "User ".$res["id"]." not found!"."</br>";
         }

print_r($all);

output:

Array
(
    [0] => 101
    [id] => 101
)
Array
(
    [0] => 102
    [id] => 102
)
Array
(
    [0] => 203
    [id] => 203
)
Array
(
    [0] => 204
    [id] => 204
)
Array
(
    [0] => 205
    [id] => 205
)

print_r($people);

output:

Array
(
    [0] => Array
        (
            [ids] => 103
            [name] => test103
        )

    [1] => Array
        (
            [ids] => 104
            [name] => test104
        )

    [2] => Array
        (
            [ids] => 101
            [name] => test101
        )

    [3] => Array
        (
            [ids] => 102
            [name] => test102
         )

)

Next variables not work and i have empty output.

CodePudding user response：

With array_merge you can merge some arrays for example:

$people = array_merge($males,$females);

And with array_diff you can find diffrence between arrays:

$result=array_diff(array_column($all, 'id'),array_column($people, 'id'));
print_r($result);

And result is:

Array
(
   [0] => 105
   [id] => 105
   [1] => test105
   [name] => test105
)

And for your target output you can use:

foreach($result as $res)
{
   echo "User ".$res["id"]." not found!"."</br>";
}

CodePudding user response：

This can be done using array_diff, and since your indices differ ('id' !== 'ids') use array_column to build your parameters.

Assuming $all is an array of arrays, something like:

$all = [];
while($line = mysqli_fetch_array($idall))
{
  $all[] = $line;
}

Then you just need to do the following:

foreach (array_diff(array_column($all, 'id'), array_column($males, 'ids'), array_column($females, 'ids')) as $nf_id) {
  // this will execute for every member of $all who's id is not found in $males or $females
  echo "User $nf_id not found!\n";
}

Here is my working PHP sandbox

Refer to the docs: