消除foreach中的重复ID

Question

I'm having a hard time elimination the duplicates out of my php foreach. I cant get it to work for some reason.

What I'm trying to do is as following: People are able to recommend other people to follow a specific user. If two people has recommended the same person, there are two db entries with the username of that specific user.

I've got a page full of recommendations by the logged in user. I show the users via foreach. Since there are two db entries of that specific person, that user got shown twice. Thats the reason why I want to eliminate duplicates and show every user just once, no matter how many people recommended that person.

I've tried everything I could think of. Even array_unique wouldnt do the trick for me, but thats because I'm comparing an array against a variable. But I seriously have no clue how to do it otherwise..

Here's a part of my code

//Get info about users recommendations and put them into an array
$prepare_user_info = array(); 
$get_user_info = mysqli_query($mysqli,"SELECT * FROM recommend WHERE rec_by_id = '$user_id' ORDER BY timestamp DESC") OR die (mysqli_error($mysqli));

while($prepared_user = mysqli_fetch_array($get_user_info)){
$prepared_user = array( 
    "rec_id" => $prepared_user['rec_id']);
    $prepare_user_info[] = $prepared_user;
}


//Foreach every recommendation by the logged in user
foreach ($prepare_user_info as $pu):

    //Get the recommendations user_data_array
    $stmt = $mysqli->prepare("SELECT user_lookup_array FROM recommend WHERE rec_id = ?") or die (mysqli_error($mysqli));
    $stmt->bind_param('s', $pu['rec_id']); 
    $stmt->execute();  
    $stmt->bind_result($db_user_data_array); 
    $stmt->store_result();
    $stmt->fetch();
    $fetch_array = $stmt->num_rows;
    $stmt->close();

    //Decode the user_data_array
    $user = json_decode($db_user_data_array);

    //Echo the name of the user
    echo $user->name;

endforeach;

Is there any way to eliminate the duplicates, so every user only get shown once, no matter how many times the logged in user recommended that person?

Thanks in advance!

EDIT

I've changed my first piece of code into (thank you @Mike Brant):

$prepare_user_info = array(); 
$get_user_info = mysqli_query($mysqli,"SELECT DISTINCT rec_id FROM recommend WHERE rec_by_id = '$user_id' ORDER BY timestamp DESC") OR die (mysqli_error($mysqli));

while($prepared_user = mysqli_fetch_array($get_user_info)){
$prepared_user = array( 
    "rec_id" => $prepared_user['rec_id']);
    $prepare_user_info[] = $prepared_user;
}

The duplicates are gone and the people are shown only once. However, one question remains. Why should I not prepare inside the foreach? I need the user_lookup_array of every user. Therefor I placed it inside the foreach.

Thanks!

Answer 1

You should simply ask for users recommended by $user_id - no need for indirect query here since all data you want is already associated with rec_by_id row.

SELECT user_lookup_array
FROM recommend
WHERE rec_by_id = ? ($user_id bind)

However user_lookup_array sounds like bad field for database and it seems that it duplicates a lot. You should build associative table (see also: many-to-many relationship) where recommended-by user ids couples with recommended user ids and all user's details would be JOINed by these ids from separate table:

users table

user_id | user_name | ...other user details (one per field)
1       | Mark      | ...
2       | Helen     | ...
...     | ...       | ...
142     | John      | ...

recommend table

rec_by_id | rec_id
1         | 2
1         | 142
2         | 142

Now your query would look like this:

SELECT users.*
FROM recommend
INNER JOIN users ON users.user_id = recommend.rec_id
WHERE recommend.rec_by_id = $user_id

Looks complicated but you should really learn how to JOIN tables in one result set. It makes a lots of operations easier and faster (and no duplication of course)

Queries in loops consume a lot of server resources (it's like buying one item, paying for it and getting back for further shopping over and over again).