如何将查询结果插入到php中的数组并在另一个数组中检查它们

Question

I have lawyer , lawyerBadges, badges tables in mysql database.I want to load all the badge ids in badge to an array.(allBadges[]).And I want to load the all the badge ids of lawyer to another array(lawyerBadges[]).Then I want to check whether the allBadges elements are in lawyerBades.how to do it.

Here is my code up to now.

    $username = $_SESSION['username'];

    getPoints();


    function getPoints(){
        global  $connection;
        global $username;
        $pointCount_query =mysqli_query( $connection,"SELECT * FROM lawyer WHERE username='".$username."'");
        $pointCount=mysqli_fetch_array($pointCount_query);
        $points= (int)$pointCount['points'];

        addBadges($points);

    }

    function addBadges($user_points){
        global  $connection;
        global $username;
        $badge_array = array();
        $badgeList=mysqli_query($connection,"SELECT bId FROM badge ");
        $badges=mysqli_fetch_array($badgeList);

        $lawyers_badges=mysqli_query($connection,"SELECT bID FROM lawyerbadge WHERE username='".$username."'");
        while($row=mysqli_fetch_assoc($lawyers_badges)){
            $badge_array[]=$row;
        }

        //this is the problem area
       foreach( $badge_array  as $value){
            echo $value.'<br />';

        }


    }

Answer 1

You'll need 2 for loops. The outer loop will iterate over all the badges. The inner loop will iterate over all the lawyers badges. If outer loops badge is not found, it will echo the badge

foreach( $badgeList as $badge){
    $badge_found_flag = false;
    foreach( $badge_array  as $lawyerBadge){
        $badge_found_flag = true;

    }
    if($badge_found_flag == false){
        echo $badge.'<br />';
    }
}

Update

A better alternative would be to use LEFT JOIN, which will require no manual looping and get result with only one query.

SELECT badge.bId FROM badge 
LEFT JOIN lawyerbadge  ON badge.bId = lawyerbadge.bID
WHERE username = '$username'"
AND lawyerbadge.bID IS NULL;

This will return only badges which you're interested in.